Do both the sessions need to have UPDLOCK to avoid deadlocks

• summer12in

  SSC Enthusiast

  Points: 161

  More actions

  November 20, 2015 at 9:58 pm

  #313249

  The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time -

  The table has clustered index on ID column

  ----session 1 --------

  begin transaction

  select * from a1

  update a1

  set id = 22

  where id = 2

  ----session 2 --------

  begin transaction

  select * from a1

  update a1

  set id = 22

  where id = 2

  Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.

  Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .

  For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock

  so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation. Please advise.

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  November 21, 2015 at 12:01 am

  #1841499

  this will not result in a deadlock since both the updates are happening to the same row.

  your looking at most blocking and that too should resolve itself as soon as the first update is done.

  Jayanth Kurup[/url]