When analyzes
such a situation for deadlocks it tries to determine how many transactions
are involved in the deadlock (two or more). Usually aborting one transaction
breaks the deadlock. must
pick one transaction as the victim and abort that transaction; it picks the
transaction that holds the fewest number of locks as the victim, on the assumption
that transaction has performed the least amount of work. (This may not be
the case, however; the transaction might have recently been escalated from
row-level locking to table locking and thus hold a small number of locks even
though it has done the most work.)
When aborts the
victim transaction, it receives a deadlock error (an SQLException with
an SQLState of 40001). The error message gives you
the transaction IDs, the statements, and the status of locks involved in a
deadlock situation.
ERROR 40001: A lock could not be obtained due to a deadlock,
cycle of locks & waiters is:
Lock : ROW, DEPARTMENT, (1,14)
Waiting XID : {752, X} , APP, update department set location='Boise'
where deptno='E21'
Granted XID : {758, X} Lock : ROW, EMPLOYEE, (2,8)
Waiting XID : {758, U} , APP, update employee set bonus=150 where salary=23840
Granted XID : {752, X} The selected victim is XID : 752
For information on configuring when deadlock checking occurs, see .
Deadlocks are detected only within a single database. Deadlocks across
multiple databases are not detected. Non-database deadlocks caused by Java
synchronization primitives are not detected by .