Database deadlocks occur when two or more transactions permanently block each other by holding locks on resources that the others need. Reproducing this condition in a controlled Java environment requires managing multiple concurrent connections and deliberately crossing their lock acquisition order.

Prerequisites and Schema Setup

A relational database table with atleast two distinct rows is required to demonstrate cross-resource locking. Execute the following SQL to prepare the environment:

CREATE TABLE account_balance (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO account_balance (account_id, balance) VALUES (101, 500.00);
INSERT INTO account_balance (account_id, balance) VALUES (102, 750.00);

Implementing the Deadlock Scenario

To trigger a deadlock, two separate database connections must operate concurrently. Each connection will disable auto-commit, acquire a lock on one row, pause briefly to ensure the other transaction acquires its lock, and then attempt to update the row held by the opposing transaction.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeadlockSimulator {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "developer";
    private static final String PASS = "securePassword123";

    public static void main(String[] args) {
        Thread workerA = new Thread(() -> executeTransaction(101, 102), "Transaction-Alpha");
        Thread workerB = new Thread(() -> executeTransaction(102, 101), "Transaction-Beta");

        workerA.start();
        workerB.start();
    }

    private static void executeTransaction(int firstTarget, int secondTarget) {
        String sql = "UPDATE account_balance SET balance = balance - 10.00 WHERE account_id = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            conn.setAutoCommit(false);

            // Step 1: Lock the first row
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, firstTarget);
                pstmt.executeUpdate();
                System.out.println(Thread.currentThread().getName() + " locked account " + firstTarget);
            }

            // Brief pause to allow the other thread to acquire its initial lock
            Thread.sleep(500);

            // Step 2: Attempt to lock the second row (causes deadlock)
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, secondTarget);
                pstmt.executeUpdate();
                System.out.println(Thread.currentThread().getName() + " locked account " + secondTarget);
            }

            conn.commit();
        } catch (SQLException e) {
            System.err.println(Thread.currentThread().getName() + " encountered SQL exception: " + e.getMessage());
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

Execution Flow Analysis

1. Transaction-Alpha establishes a connection, disables auto-commit, and executes an update on account_id = 101. The database places an exclusive lock on this row.
2. Simultaneously, Transaction-Beta connects, disables auto-commit, and updates account_id = 102, acquiring an exclusive lock on that row.
3. Both threads pause for 500 milliseconds. This synchronization gap guarantees that both initial locks are held before either proceeds.
4. Transaction-Alpha attempts to update account_id = 102. It blocks because Transaction-Beta holds the lock.
5. Transaction-Beta attempts to update account_id = 101. It blocks because Transaction-Alpha holds the lock.
6. The database deadlock detector identifies the circular dependency. One transaction is forcibly terminated, typically throwing a SQLException with a vendor-specific deadlock error code (e.g., MySQL error 1213 or PostgreSQL error 40P01), while the other proceeds to commmit.

Key Configuration Considerations

• Isolation Level: The default READ_COMMITTED or REPEATABLE_READ isolation levels are sufficient. Lower levels like READ_UNCOMMITTED may bypass locking mechanisms and prevent the deadlock.
• Connection Pooling: When testing with connection pools (HikariCP, Druid), ensure the pool size allows at least two concurrent physical connections. A pool size of one will serialize the requests and mask the deadlock.
• Timeout Settings: Configure lock_wait_timeout (MySQL) or statement_timeout (PostgreSQL) appropriately. If set too low, the database may abort the waiting query before the deadlock cycle fully forms.