Database deadlock example

Posted on by dev

Problem of deadlocks exists in all programming languages with lock-based synchronization. And even more, if the language has message passing synchronization program can still be stuck in deadlock at database level. Down here I’ll write a Java JDBC example of database deadlock:

Database deadlock if pretty much the same as usual deadlock when different threads of execution are holding locks for some resources and at the same time they want to acquire another lock which is currently held by contender.

T1 holds lock for resource R1 & tries to acquire lock for R2
T2 holds lock for resource R2 & tries to acquire lock for R1

Finally here is example of code in Java using JDBC driver com.h2database:h2:1.4.200 with embedded database H2.

public void functionWithSeveralLocks(int id1, int id2) {
    try {
        Connection connection = getConnection();
        connection.setAutoCommit(false);

        final PreparedStatement lockSt = connection.prepareStatement(
                "SELECT id FROM example where id = ? FOR UPDATE"
        );
        lockSt.setInt(1, id1);
        lockSt.execute();
        // split locks up a bit to reproduce deadlocks easily
        Thread.sleep(1000);

        lockSt.setInt(1, id2);
        lockSt.execute();

        // some complex logic involving updates of both entities
        // ...
        // ...
    } catch (SQLException| InterruptedException e) {
        e.printStackTrace();
        throw new IllegalStateException(e);
    }
}

And here is the code which reproduces the deadlock

public void reproduceDeadLock() {
        Thread t = new Thread(() -> dao.functionWithSeveralLocks(1, 2));
        t.start();

        dao.functionWithSeveralLocks(2, 1);
    }

And the exception it generates:

org.h2.jdbc.JdbcSQLTransactionRollbackException: Deadlock detected. The current transaction was rolled back. Details: "EXAMPLE"; SQL statement:
SELECT id FROM example where id = ? FOR UPDATE [40001-200]
Caused by: java.lang.IllegalStateException: Transaction 2 has been chosen as a deadlock victim. Details:
 Transaction 1 attempts to update map  entry with key <2> modified by transaction 2(18) OPEN 2
 Transaction 2 attempts to update map  entry with key <1> modified by transaction 1(17) OPEN 2
  [1.4.200/105]


How to avoid a database deadlock?

An easy to follow answer exists only for cases when locks are done within one table — order all your locks.

If transaction should operate with two locked entities in the same table always lock the entity with smallest id first. Or biggest first doesn’t matter just follow the same rule in every function.

Leave a Reply