<< Transaction Isolation Level | Deadlock detection >>

Deadlocks

Derby Developer's Guide

Notes on locking

In addition to the locks already described, foreign key lookups require briefly held shared
locks on the referenced table (row or table, depending on the configuration).

The table and examples in this section do not take performance-based lock escalation
into account. Remember that the system can choose table-level locking for performance
reasons.

Deadlocks

In a database, a deadlock is a situation in which two or more transactions are waiting for
one another to give up locks.

For example, Transaction A might hold a lock on some rows in the Accounts table and
needs to update some rows in the Orders table to finish. Transaction B holds locks on
those very rows in the Orders table but needs to update the rows in the Accounts table
held by Transaction A. Transaction A cannot complete its transaction because of the
lock on Orders. Transaction B cannot complete its transaction because of the lock on
Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS
detects the deadlock and aborts one of the transactions. The following figure shows this
situation.

Figure 6.

A deadlock where two transactions are waiting for one another to give up

locks

Avoiding deadlocks

Using both row-level locking and the TRANSACTION_READ_COMMITTED isolation
level makes it likely that you will avoid deadlocks (both settings are Derby defaults).
However, deadlocks are still possible.

Derby application developers can avoid deadlocks by using consistent application logic;
for example, transactions that access Accounts and Orders should always access the
tables in the same order. That way, in the scenario described above, Transaction B
simply waits for transaction A to release the lock on Orders before it begins. When
transaction A releases the lock on Orders, Transaction B can proceed freely.

Another tool available to you is the LOCK TABLE statement. A transaction can attempt
to lock a table in exclusive mode when it starts to prevent other transactions from getting