CODEWITHSUNDEEP
javasql-serverdatabase-deadlocks
Omar Kooheji
Omar Kooheji

Reputation: 55790

Why Would these Two Database Commits Deadlock?

<executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x02000000cb01b3329478b4bbe70e115ebcb8f5f4a8610e7e">
(@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000))UPDATE [TABLE_NAME] set [END_TIME] = @1,[ERROR] = @2  WHERE [USER_ID]=@3 AND [SESSION_ID]=@4 AND [END_TIME] IS NULL     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x02000000ef595e01eab1df10c694910f5810afb17d814663">
UPDATE TABLE_NAME SET END_TIME='2012-03-30 12:07:45', ERROR='FALSE' WHERE USER_ID='username' AND SESSION_ID='095d42ad-67d8-444f-8e51-4576f6b940d8' AND END_TIME IS NULL     </frame>
 </executionStack>

The above is a sanitized instance of a deadlock trace that a customer has sent me from site. As I understand it deadlocks happen when two commits attempt to access the same tables out of order.

In this instance however there is only one table and the code that is executing these sql statements is pulling them from a queue and executing them in sequence.

Why would this deadlock?

Upvotes: 0

Views: 103

Answers (1)

Jool
Jool

Reputation: 1785

It depends on the DBMS, but Deadlocks can occur when two different user sessions attempt to lock a resource (row or table) that another session is also trying to get unfettered access to, a commit does not have to occur.

Session 1 :  lock  <row x>   (e.g. read for update)
Session 2 :  lock  <row y>   (e.g. read for update)
Session 1 :  fetch <row y>
Session 2 :  fetch <row x>

if the fetch, or other operation, requires un-locked access or a clean read, then there is a Deadlock.

Upvotes: 1

Related Questions