postgresqljdbcdeadlockrdbmspessimistic-locking

Why Pessimistic triggers a deadlock

I'm trying to undestand Pessimistic Lock with a simple example of Bank Money Transfer.

I believe this statements can lead to a Deadlock

BEGIN TRANSACTION

UPDATE BankAccount SET balance = balance - amount where id = 123;
UPDATE BankAccount SET balance = balance + amount where id = 456;

COMMIT;

And i believe this statements also lead to a Deadlock

BEGIN TRANSACTION

SELECT BankAccount wHERE id = 123 FOR UPDATE; // Statement #1
SELECT BankAccount WHERE id = 456 FOR UPDATE; // Statement #2

// perform some logics

UPDATE BankAccount SET balance = balance - amount where id = 123;
UPDATE BankAccount SET balance = balance + amount where id = 456;

COMMIT;

This is because if having 2 concurrent transactions T1 and T2, T1 can lock the first account with Statement #1 and T2 can lock the second account using Statement #2 which ends with a deadlock (Please correct me if i'm wrong)

Now i've tried the following Transaction, and it also leads to a Deadlock, but i can't see why !

BEGIN TRANSACTION

SELECT BankAccount wHERE id IN (123, 456) FOR UPDATE; // Statement #1

// perform some logics

UPDATE BankAccount SET balance = balance - amount where id = 123;
UPDATE BankAccount SET balance = balance + amount where id = 456;

COMMIT;

Notes:

  1. i've tried all these transactions using JDBC in a Java 11 Environnement.
  2. I'm using multithreading to simulate concurrent access to the database. Each Money transfer happen by 1 single thread which choose randomly 2 different account and make a money transfer.

Here's the StackTrace:

org.postgresql.util.PSQLException: ERROR: deadlock detected
  Détail : Process 6596 waits for ExclusiveLock on tuple (314,24) of relation 321198 of database 321194; blocked by process 6643.
Process 6643 waits for ShareLock on transaction 326566; blocked by process 6637.
Process 6637 waits for ShareLock on transaction 326569; blocked by process 6574.
Process 6574 waits for ExclusiveLock on tuple (314,24) of relation 321198 of database 321194; blocked by process 6596.
  Indice : See server log for query details.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:125)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at com.mssmfactory.service.OptimisticMoneyTransferHandler.transfer(OptimisticMoneyTransferHandler.java:76)
    at ConcurrentMoneyTransferHandlerTest.lambda$test$0(ConcurrentMoneyTransferHandlerTest.java:84)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)

Here's my code that transfer money:

public class PessimisticMoneyTransferHandler implements IMoneyTransferHandler {

    private IDatabaseConnector iDatabaseConnector;

    public void transfer(Long senderId, Long receiverId, Double amount) throws SQLException, NoSuchBankAccountException, InsufficientBalanceException {
        try (Connection connection = this.iDatabaseConnector.getConnection()) {
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM mssmbank.mssmbank.bankaccounts WHERE id IN (?, ?) FOR UPDATE");
            preparedStatement.setLong(1, senderId);
            preparedStatement.setLong(2, receiverId);

            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                Long firstAccountId = resultSet.getLong("id");
                Double firstAccountBalance = resultSet.getDouble("balance");

                if (resultSet.next()) {
                    Double secondAccountBalance = resultSet.getDouble("balance");

                    boolean isFirstSender = firstAccountId.equals(senderId);

                    if (isFirstSender && firstAccountBalance < amount) {
                        connection.rollback();

                        throw new InsufficientBalanceException();
                    }
                    else if (!isFirstSender && secondAccountBalance < amount) {
                        connection.rollback();

                        throw new InsufficientBalanceException();
                    }

                    preparedStatement = connection.prepareStatement("UPDATE mssmbank.mssmbank.bankaccounts SET balance = balance - ? WHERE id = ?");
                    preparedStatement.setDouble(1, amount);
                    preparedStatement.setDouble(2, senderId);
                    preparedStatement.executeUpdate();

                    preparedStatement = connection.prepareStatement("UPDATE mssmbank.mssmbank.bankaccounts SET balance = balance + ? WHERE id = ?");
                    preparedStatement.setDouble(1, amount);
                    preparedStatement.setDouble(2, receiverId);
                    preparedStatement.executeUpdate();

                    connection.commit();
                } else throw new NoSuchBankAccountException(receiverId);
            } else throw new NoSuchBankAccountException(senderId);
        }
    }
}

And here's the main code:

    final int numberOfAccount = 10;
    final int numberOfTransactions = 150;

    List<IBankAccountDetails> bankAccounts = new ArrayList<>(numberOfAccount);
    Runnable transaction = () -> {
        Random random = new Random();

        int emeeterIndex;
        int receiverIndex;

        do {
            emeeterIndex = random.nextInt(numberOfAccount);
            receiverIndex = random.nextInt(numberOfAccount);
        } while (emeeterIndex == receiverIndex);

        IBankAccountDetails emeeterAccount = bankAccounts.get(emeeterIndex);
        IBankAccountDetails receiverAccount = bankAccounts.get(receiverIndex);

        double amount = random.nextInt((int) (1.5 * emeeterAccount.getAccountBalance()));

        try {
            this.iMoneyTransferHandler.transfer(emeeterAccount.getAccountId(), receiverAccount.getAccountId(), amount);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchBankAccountException e) {
            e.printStackTrace();
        } catch (InsufficientBalanceException e) {
            e.printStackTrace();
        }
    };

    // ---------------------------------------------------------------------------------------------------

    ExecutorService executorService = Executors.newCachedThreadPool();

    for (int i = 0; i < numberOfTransactions; i++)
        executorService.execute(transaction);

    executorService.shutdown();
    executorService.awaitTermination(10, TimeUnit.SECONDS);
}
Solution

  • The following transaction if run in parallel should not trigger deadlock:

    BEGIN TRANSACTION
UPDATE BankAccount SET balance = balance - amount where id = 123;
UPDATE BankAccount SET balance = balance + amount where id = 456;
COMMIT;

    But following transactions if run in parallel can trigger deadlock:

        BEGIN TRANSACTION
    UPDATE BankAccount SET balance = balance - amount where id = 123;
    UPDATE BankAccount SET balance = balance + amount where id = 456;
    COMMIT;

    BEGIN TRANSACTION
    UPDATE BankAccount SET balance = balance - amount where id = 456;
    UPDATE BankAccount SET balance = balance + amount where id = 123;
    COMMIT;

    In general deadlock occurs when same locks (same objects, same mode) are taken in a different order. In this case it's the exclusive locks on following objects:

    In your Java code you generate a lot of transactions for a very small set of accounts by generating the bank account numbers randomly. This increases the risk of locking conflicts and the risk of deadlocks because if several transactions run on same accounts the locks may not be taken in the same order: