Update tbl1 set col1 = 1 where id = 1

The same update ran a few miliseconds apart. It causes deadlock. Id is primary clustered key.

I read about lock partition. is there any other way to resolve this kind of deadlock?

Please see below Deadlock graph. Thanks.

unknown unknown (@P0 int,@P1 int)UPDATE Tbl1 SET Col1=@P0 WHERE Col2 =@P1 unknown unknown (@P0 int,@P1 int)UPDATE Tbl1 SET Col1=@P0 WHERE Col2 =@P1

closed as unclear what you're asking by ypercubeᵀᴹ, Erik Darling, hot2use, Mr.Brownstone, Andriy M Feb 12 '18 at 8:00

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What have you tried up to this point? How much data is in the table? What else is happening while this update runs? Is there an explicit transaction involved? How long does it run for? – Randolph West Feb 9 '18 at 23:59
  • 2
    We'll need more info for the question to be answered. Please provide the CREATE TABLE statement with all the indexes and the complete deadlock graph. Also confirm whether the transactions involved are composed from a single (UPDATE) statement each or more that you omitted, and that the statements are exactly these you show (and no hints omitted, etc). What is the isolation level used? – ypercubeᵀᴹ Feb 10 '18 at 12:12

Why are you running the same update?

Try as this will not take a lock if col1 = 1

Update tbl1 
set col1 = 1 
where id = 1
  and col1 <> 1

It doesn't make sense to me that SQL Server would try to escalate to a shared table-level lock for a trivial update via the primary key. Check the execution plan to ensure the primary key index is being used as expected.

It seems you may have a data type mismatch for the id column value or parameter, resulting in a scan instead of clustered index seek.

  • In execution plan, it is clustered index update. ID is a clustered primary key. – AMMH Feb 20 '18 at 20:41
  • @AMMH, although this question is closed, upload the plan to brentozar.com/pastetheplan and share the link here. I think there is a missing detail. – Dan Guzman Feb 20 '18 at 21:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.