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.

Figure 6. A deadlock.

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

When a transaction waits more than a specific amount of time to obtain a lock (called the deadlock timeout), Derby can detect whether the transaction is involved in a deadlock. When Derby 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. Derby 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 Derby 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 Configuring Deadlock Detection and Lock Wait Timeouts.

Note:

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 Derby.

Even if a transaction is not involved in a deadlock, it might have to wait a considerable amount of time to obtain a lock because of a long-running transaction or transactions holding locks on the tables it needs. In such a situation, you might not want a transaction to wait indefinitely. Instead, you might want the waiting transaction to abort, or time out, after a reasonable amount of time, called a lock wait timeout. (For information about configuring the lock wait timeout, see Configuring Deadlock Detection and Lock Wait Timeouts.)

You configure the amount of time a transaction waits before Derby does any deadlock checking with the derby.locks.deadlockTimeout property. You configure the amount of time a transaction waits before timing out with the derby.locks.waitTimeout property. When configuring your database or system, you should consider these properties together. For example, in order for any deadlock checking to occur, the derby.locks.deadlockTimeout property must be set to a value lower than the derby.locks.waitTimeout property. If it is set to a value equal to or higher than the derby.locks.waitTimeout, the transaction times out before Derby does any deadlock checking.

By default, derby.locks.waitTimeout is set to 60 seconds. -1 is the equivalent of no wait timeout. This means that transactions never time out, although Derby can choose a transaction as a deadlock victim.

Figure 7. One possible configuration: deadlock checking occurs when a transaction has waited 30 seconds; no lock wait timeouts occur.

Figure 8. Another typical configuration: deadlock checking occurs after a transaction has waited 60 seconds for a lock; after 90 seconds, the transaction times out and is rolled back.

Figure 9. A configuration in which no deadlock checking occurs: transactions time out after they have waited 50 seconds. No deadlock checking occurs.

If deadlocks occur frequently in your multi-user system with a particular application, you might need to do some debugging. Derby provides a class to help you in this situation, org.apache.derby.diag.LockTable. You can also set the property derby.locks.deadlockTrace to dump additional information to the derby.log file about any deadlocks that occur on your system. See the Tuning Guide for more information on this property. For information, see the Derby Server and Administration Guide.

When you configure your system for deadlock and lockwait timeouts and an application could be chosen as a victim when the transaction times out, you should program your application to handle this. To do this, test for SQLExceptions with SQLStates of 40001 (deadlock timeout) or 40XL1 or 40XL2 (lockwait timeout).

In the case of a deadlock you might want to re-try the transaction that was chosen as a victim. In the case of a lock wait timeout, you probably do not want to do this right away.

The following code is one example of how to handle a deadlock timeout.

/// if this code might encounter a deadlock, 
// put the whole thing in a try/catch block
// then try again if the deadlock victim exception 
// was thrown
try {
    s6.executeUpdate(
         "UPDATE employee " + 
         "SET bonus = 625 " + 91 
         "WHERE empno='000150'"); 
    s6.executeUpdate("UPDATE project " + 
         "SET respemp = '000150' " + 
         "WHERE projno='IF1000'"); 
} 
// note: do not catch such exceptions in database-side methods; 
// catch such exceptions only at the outermost level of 
// application code. 
// See Database-Side JDBC Procedures and SQLExceptions.  
catch (SQLException se) { 
    if (se.getSQLState().equals("40001")) { 
        // it was chosen as a victim of a deadlock. 
        // try again at least once at this point. 
        System.out.println( "Will try the transaction again."); 
        s6.executeUpdate("UPDATE employee " + 
        "SET bonus = 625 " + 
        "WHERE empno='000150'"); 
        s6.executeUpdate("UPDATE project " + 
        "SET respemp = 000150 " + 
        "WHERE projno='IF1000'"); 
    } 
    else throw se; 
}