Nodejs

JavaScript

Postgresql

Database

Deadlocks: A Real world Example

6 min readOct 11, 2024

Deadlocks are a critical concern in real-world applications, especially those that handle high concurrency and multiple transactions accessing shared resources. Let’s explore practical, real-world scenarios where deadlocks can occur due to inconsistent resource locking and how to prevent them by implementing consistent locking strategies.

We’ll cover below comprehensive example:

Banking Application: Transferring Funds Between Accounts

Scenario Overview

In a banking application, users can transfer funds between their accounts. Each transfer involves two critical operations:

Debiting the sender’s account.
Crediting the receiver’s account.

Problem Case: Inconsistent Lock Ordering Leading to Deadlocks

When two transactions attempt to transfer funds between the same pair of accounts but in opposite directions, deadlocks can occur if they acquire locks in different orders.

Deadlock Scenario

Transaction A: Transfers funds from Account 1 to Account 2
Transaction B: Transfers funds from Account 2 to Account 1

If Transaction A locks Account 1 first and then Account 2, while Transaction B locks Account 2 first and then Account 1, a deadlock can occur.

Code Example

We’ll simulate the deadlock scenario using Sequelize and Node.js. To effectively demonstrate this, we’ll use two separate Node.js scripts running concurrently.

Transaction A: Transfer from Account 1 to Account 2

// transactionA.js
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('postgres://user:password@localhost:5432/bank_db');

// Define the BankAccount model
const BankAccount = sequelize.define('BankAccount', {
  userId: DataTypes.INTEGER,
  balance: DataTypes.DECIMAL,
}, {
  tableName: 'bank_accounts',
  timestamps: false,
});

async function transferFunds(senderId, receiverId, amount) {
  const transaction = await sequelize.transaction();

  try {
    // Step 1: Lock Sender's Account
    const sender = await BankAccount.findOne({
      where: { id: senderId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log('Transaction A: Locked Sender Account 1');

    // Simulate processing delay
    await new Promise(resolve => setTimeout(resolve, 1000)); // 1 second delay

    // Step 2: Lock Receiver's Account
    const receiver = await BankAccount.findOne({
      where: { id: receiverId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log('Transaction A: Locked Receiver Account 2');

    // Perform the transfer
    if (parseFloat(sender.balance) < parseFloat(amount)) {
      throw new Error('Insufficient funds in Sender Account.');
    }

    sender.balance = parseFloat(sender.balance) - parseFloat(amount);
    receiver.balance = parseFloat(receiver.balance) + parseFloat(amount);

    await sender.save({ transaction });
    await receiver.save({ transaction });

    console.log('Transaction A: Transfer completed');

    await transaction.commit();
    console.log('Transaction A: Committed');
  } catch (error) {
    await transaction.rollback();
    console.error('Transaction A: Rolled back due to error:', error.message);
  }
}

// Initiate the transfer
transferFunds(1, 2, 100);

Transaction B: Transfer from Account 2 to Account 1

// transactionB.js
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('postgres://user:password@localhost:5432/bank_db');

// Define the BankAccount model
const BankAccount = sequelize.define('BankAccount', {
  userId: DataTypes.INTEGER,
  balance: DataTypes.DECIMAL,
}, {
  tableName: 'bank_accounts',
  timestamps: false,
});

async function transferFunds(senderId, receiverId, amount) {
  const transaction = await sequelize.transaction();

  try {
    // Step 1: Lock Receiver's Account (since sender is Account 2)
    const sender = await BankAccount.findOne({
      where: { id: senderId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log('Transaction B: Locked Sender Account 2');

    // Simulate processing delay
    await new Promise(resolve => setTimeout(resolve, 1000)); // 1 second delay

    // Step 2: Lock Sender's Account (Account 1)
    const receiver = await BankAccount.findOne({
      where: { id: receiverId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log('Transaction B: Locked Receiver Account 1');

    // Perform the transfer
    if (parseFloat(sender.balance) < parseFloat(amount)) {
      throw new Error('Insufficient funds in Sender Account.');
    }

    sender.balance = parseFloat(sender.balance) - parseFloat(amount);
    receiver.balance = parseFloat(receiver.balance) + parseFloat(amount);

    await sender.save({ transaction });
    await receiver.save({ transaction });

    console.log('Transaction B: Transfer completed');

    await transaction.commit();
    console.log('Transaction B: Committed');
  } catch (error) {
    await transaction.rollback();
    console.error('Transaction B: Rolled back due to error:', error.message);
  }
}

// Initiate the transfer
transferFunds(2, 1, 50);

Execution Steps

Initialize Accounts: Ensure that the bank_accounts table has at least two records:

INSERT INTO bank_accounts (id, userId, balance) VALUES
(1, 101, 1000),
(2, 102, 500);

Run Transaction A and B:

node transactionA.js

node transactionB.js

What Happens

Transaction A locks Account 1 and waits for 1 second before attempting to lock Account 2.
Transaction B locks Account 2 and waits for 1 second before attempting to lock Account 1.
Both transactions end up waiting for each other to release their respective locks on Account 1 and Account 2, resulting in a deadlock.
PostgreSQL detects the deadlock and aborts one of the transactions to resolve it.

Sample Output

Transaction A Output:

Transaction A: Locked Sender Account 1
Transaction A: Locked Receiver Account 2
Transaction A: Transfer completed
Transaction A: Committed

Transaction B Output:

Transaction B: Locked Sender Account 2
Transaction B: Locked Receiver Account 1
Transaction B: Rolled back due to error: deadlock detected

Note: Depending on the timing and PostgreSQL’s deadlock resolution algorithm, either Transaction A or Transaction B could be the one that gets aborted.

Solution: Consistent Lock Ordering to Prevent Deadlocks

To prevent deadlocks, all transactions must acquire locks in the same order. In this case, always lock the account with the smaller id first, then the account with the larger id.

Revised Code Example

We’ll modify Transaction B to lock Account 1 first and then Account 2, aligning it with Transaction A’s lock order.

Transaction B: Transfer from Account 2 to Account 1 (Consistent Lock Ordering)

// transactionB.js
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('postgres://user:password@localhost:5432/bank_db');

// Define the BankAccount model
const BankAccount = sequelize.define('BankAccount', {
  userId: DataTypes.INTEGER,
  balance: DataTypes.DECIMAL,
}, {
  tableName: 'bank_accounts',
  timestamps: false,
});

async function transferFunds(senderId, receiverId, amount) {
  const transaction = await sequelize.transaction();

  try {
    // Determine lock acquisition order based on account IDs
    const firstAccountId = Math.min(senderId, receiverId);
    const secondAccountId = Math.max(senderId, receiverId);

    // Step 1: Lock the first account
    const firstAccount = await BankAccount.findOne({
      where: { id: firstAccountId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log(`Transaction B: Locked Account ${firstAccountId}`);

    // Simulate processing delay
    await new Promise(resolve => setTimeout(resolve, 1000)); // 1 second delay

    // Step 2: Lock the second account
    const secondAccount = await BankAccount.findOne({
      where: { id: secondAccountId },
      lock: transaction.LOCK.UPDATE,
      transaction,
    });

    console.log(`Transaction B: Locked Account ${secondAccountId}`);

    // Perform the transfer
    let sender, receiver;
    if (senderId === firstAccountId) {
      sender = firstAccount;
      receiver = secondAccount;
    } else {
      sender = secondAccount;
      receiver = firstAccount;
    }

    if (parseFloat(sender.balance) < parseFloat(amount)) {
      throw new Error('Insufficient funds in Sender Account.');
    }

    sender.balance = parseFloat(sender.balance) - parseFloat(amount);
    receiver.balance = parseFloat(receiver.balance) + parseFloat(amount);

    await sender.save({ transaction });
    await receiver.save({ transaction });

    console.log('Transaction B: Transfer completed');

    await transaction.commit();
    console.log('Transaction B: Committed');
  } catch (error) {
    await transaction.rollback();
    console.error('Transaction B: Rolled back due to error:', error.message);
  }
}

// Initiate the transfer
transferFunds(2, 1, 50);

What Happens

Transaction A locks Account 1 first and then Account 2.
Transaction B determines the lock order based on id, locks Account 1 first (consistent with Transaction A), and then Account 2.
Since Transaction A already holds the lock on Account 1, Transaction B waits until Transaction A commits.
Once Transaction A commits, Transaction B proceeds without deadlocking.

Additional Best Practices to Prevent Deadlocks

Consistent Lock Acquisition Order Across All Transactions:

Global Ordering: Decide on a global order for acquiring locks (e.g., ascending order of primary keys) and enforce it across all transactions.
Application-Level Enforcement: Implement utility functions or transaction managers that handle lock ordering to prevent human error.

2. Keep Transactions Short and Efficient:

Minimize Lock Duration: Perform only essential operations within transactions to reduce the time locks are held.
Avoid User Input or External Calls: Do not include operations that can cause delays (like API calls or user input) within transactions.

3. Use Appropriate Isolation Levels:

Balance Consistency and Concurrency: Choose an isolation level that meets your application’s consistency requirements without unnecessarily increasing lock contention.
Example: Use READ COMMITTED for general operations and escalate to REPEATABLE READ or SERIALIZABLE only when necessary.

Implement Retry Logic for Deadlock Handling:

Detect Deadlocks: Catch deadlock errors and retry the transaction.
Exponential Backoff: Implement retry strategies with delays to reduce the chance of immediate contention upon retry.