Sql-server – Is it possible to have a deadlock in this situation

deadlocksql server

I am working with SqlServer and Java.

I have the following (pseudo) code :

begin transaction
  For each item of a list
    select the row in db 
    ...
    update the row
  End for
end transaction

Is it possible to have a deadlock in this situation if two transactions are running with the same items ?

My understanding is the following :
For each item the select take a Shared lock on the row, and next the lock is upgraded to a Exclusive lock for the update. The lock are released at the end of the transaction. Is it true ? If yes I think that could explain a deadlock, if the first transaction tries to read a row locked by the second transaction that is trying to read a row already locked too, isn't it ?

Thks

Best Answer

Assuming you're working with read committed isolation level (=the default), then the deadlock will happen when a reads the row, then b reads the row, then a tries to update it and then b tries to update it.

In addition to deadlocks you might have another problem if you're dealing with the same rows and the process isn't that fast, since the second process will be stuck until the first one completes the whole transaction (if deadlock doesn't happen).

If the blocking doesn't matter, you can take an exclusive lock on the row when you're selecting the data by specifying lock hints, most likely the correct ones in this case would be UPDLOCK and HOLDLOCK, see https://msdn.microsoft.com/en-us/library/ms187373.aspx

Related Question