0

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

Improve this question

1 Answer 1

Reset to default
0

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

Improve this answer
1
  • That makes sense to me if we assume that the two processes are processing the same items in the same order. If for some reason the two processes may be updating the items in a different order (i.e., each may have built the "list" using it's own separate logic), then you could also have another deadlock situation where each process has already updated a row (but not yet committed the transaction) that the other process will later need to update.
    – Geoff Patterson
    Commented May 21, 2015 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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