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.

The appropriate use of indexes can also help you to avoid deadlocks, since indexes make table scans less likely and reduce the number of locks obtained. For more information, see "CREATE INDEX statement" in the Derby Reference Manual and the topics under "Avoiding table scans of large tables" in Tuning Derby.

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 shared locks on a table. For more information, see "LOCK TABLE statement" in the Derby Reference Manual.

Related concepts
Deadlock detection
Lock wait timeouts
Configuring deadlock detection and lock wait timeouts
Debugging deadlocks
Programming applications to handle deadlocks