Transactions and Locks in Sequelize

A transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure that either all operations succeed (commit) or none do (rollback), maintaining the consistency of your database.

In Sequelize, transactions can be managed using the sequelize.transaction() method, which supports both callback and promise-based (including async/await) patterns. We'll focus on the async/await approach for clarity and modern syntax.

Basic Transaction Structure with async/await

const { Sequelize, DataTypes } = require('sequelize');

// Initialize Sequelize (replace with your actual config)
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres', // or 'mysql', 'sqlite', etc.
});

// Define models (example)
const User = sequelize.define('User', {
  name: DataTypes.STRING,
  balance: DataTypes.DECIMAL,
});

const Account = sequelize.define('Account', {
  userId: DataTypes.INTEGER,
  amount: DataTypes.DECIMAL,
});

// Function to perform a transaction
async function performTransaction() {
  const transaction = await sequelize.transaction();

  try {
    // Perform transactional operations here
    const user = await User.create(
      { name: 'Alice', balance: 1000 }, { transaction });
    await Account.create(
      { userId: user.id, amount: 100 }, { transaction });

    // Commit the transaction
    await transaction.commit();
    console.log('Transaction committed successfully.');
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Transaction rolled back due to error:', error);
  }
}

// Execute the transaction
performTransaction();

Key Points:

• Initiating a Transaction: const transaction = await sequelize.transaction();
• Passing the Transaction Object: Each query within the transaction receives the transaction option.
• Committing or Rolling Back: Use await transaction.commit(); or await transaction.rollback(); based on success or failure.

Overview of Transaction Locks

Transaction locks control how multiple transactions interact with the same set of data concurrently. They help prevent issues like dirty reads, non-repeatable reads, and phantom reads by controlling access levels to data rows.

Sequelize supports various lock types, typically aligning with the underlying database’s locking mechanisms. The primary lock types we’ll discuss are:

1. UPDATE Lock
2. SHARE Lock
3. KEY SHARE Lock
4. NO KEY UPDATE Lock

Each lock type serves a specific purpose in managing concurrent data access.

3. Detailed Explanation of Each Lock Type with Examples

3.1 UPDATE Lock

Overview

The UPDATE lock is used when a transaction intends to modify (update or delete) specific rows. It prevents other transactions from acquiring conflicting locks that would allow them to modify the same rows simultaneously.

Behavior

• Exclusive Nature: Prevents other transactions from acquiring UPDATE or EXCLUSIVE locks on the same rows.
• Allows: Other transactions can read the locked rows.
• Prevents: Other transactions from updating or deleting the locked rows until the transaction commits or rolls back.

Use Cases

• Write Operations: Ensuring that no two transactions can modify the same row concurrently.
• Consistent Read-Modify-Write: Preventing race conditions during update operations.

Example: Using UPDATE Lock

async function updateUserBalance(userId, amount) {
  const transaction = await sequelize.transaction();

  try {
    // Acquire an UPDATE lock on the specific user row
    const user = await User.findOne({
      where: { id: userId },
      lock: transaction.LOCK.UPDATE, // Applying the UPDATE lock
      transaction,
    });

    if (!user) {
      throw new Error('User not found');
    }

    // Update the user's balance
    user.balance = parseFloat(user.balance) + amount;
    await user.save({ transaction });

    // Commit the transaction
    await transaction.commit();
    console.log(`User ${userId} balance updated successfully.`);
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Failed to update user balance:', error);
  }
}

// Usage
updateUserBalance(1, 500);

Explanation:

1. Start Transaction: Initiate a transaction using await sequelize.transaction().
2. Acquire UPDATE Lock: Use lock: transaction.LOCK.UPDATE in the findOne query to lock the selected row for updates.
3. Modify Data: Update the user’s balance.
4. Commit or Rollback: Commit if successful; otherwise, rollback on error.

SQL Equivalent:

BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- Perform update
UPDATE users SET balance = balance + 500 WHERE id = 1;
COMMIT;

3.2 SHARE Lock

Overview

The SHARE lock is used when a transaction needs to read specific rows without modifying them. It allows multiple transactions to hold SHARE locks on the same rows concurrently, preventing any transaction from modifying those rows during the read.

Behavior

• Shared Nature: Multiple transactions can hold SHARE locks on the same rows simultaneously.
• Allows: Other transactions to also acquire SHARE locks and read the rows.
• Prevents: Transactions from acquiring UPDATE or EXCLUSIVE locks to modify the rows until all SHARE locks are released.

Use Cases

• Read-Only Operations: Ensuring consistent reads without allowing modifications during the read.
• Generating Reports: Reading data for reports without interfering with other transactions.

Example: Using SHARE Lock

async function getUserWithShareLock(userId) {
  const transaction = await sequelize.transaction();

  try {
    // Acquire a SHARE lock on the user row
    const user = await User.findOne({
      where: { id: userId },
      lock: transaction.LOCK.SHARE, // Applying the SHARE lock
      transaction,
    });

    if (!user) {
      throw new Error('User not found');
    }

    // Perform read-only operations
    console.log(`User Name: ${user.name}, Balance: ${user.balance}`);

    // Commit the transaction
    await transaction.commit();
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Failed to acquire SHARE lock:', error);
  }
}

// Usage
getUserWithShareLock(1);

Explanation:

1. Start Transaction: Initiate a transaction.
2. Acquire SHARE Lock: Use lock: transaction.LOCK.SHARE to lock the row for shared reading.
3. Read Data: Access user data without intending to modify it.
4. Commit or Rollback: Commit if successful; otherwise, rollback on error.

SQL Equivalent:

BEGIN;
SELECT * FROM users WHERE id = 1 FOR SHARE;
-- Perform read operations
COMMIT;

Behavior:

• Multiple transactions can execute getUserWithShareLock concurrently on the same userId.
• No transaction can modify the user row until all SHARE locks are released.

3.3 KEY SHARE Lock

Overview

The KEY SHARE lock is designed to prevent other transactions from modifying key columns (such as primary keys or unique indexes) of the locked rows. Unlike the SHARE lock, which prevents any modifications to the entire row, the KEY SHARE lock specifically maintains the integrity of key columns while allowing non-key column modifications.

Behavior:

Partial Exclusivity:

Allows:

• Reading the locked rows.
• Acquiring other KEY SHARE locks.
• Modifying non-key columns.

Prevents:

• Modifying key columns.
• Acquiring UPDATE or EXCLUSIVE locks.
• Lock Compatibility: Compatible with other KEY SHARE locks but incompatible with locks that intend to modify key columns.

Use Cases

• Referential Integrity: Ensuring that key columns remain unchanged during operations that depend on them.
• Stable Keys in Operations: Operations that require the stability of key columns without preventing non-key updates.

Example: Using KEY SHARE Lock

Assuming we have an Order model where orderId is the primary key, and userId is a foreign key.

async function fetchOrdersWithKeyShareLock(userId) {
  const transaction = await sequelize.transaction();

  try {
    // Acquire a KEY SHARE lock on all orders for the user
    const orders = await Order.findAll({
      where: { userId },
      lock: transaction.LOCK.KEY_SHARE, // Applying the KEY SHARE lock
      transaction,
    });

    if (!orders.length) {
      throw new Error('No orders found for the user.');
    }

    // Perform operations that rely on the stability of key columns
    orders.forEach(order => {
      console.log(`Order ID: ${order.id}, Amount: ${order.amount}`);
    });

    // Commit the transaction
    await transaction.commit();
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Failed to acquire KEY SHARE lock:', error);
  }
}

// Usage
fetchOrdersWithKeyShareLock(1);

Explanation:

1. Start Transaction: Initiate a transaction.
2. Acquire KEY SHARE Lock: Use lock: transaction.LOCK.KEY_SHARE to lock the key columns of the selected orders.
3. Read Data: Access order data, ensuring key columns remain unchanged.
4. Commit or Rollback: Commit if successful; otherwise, rollback on error.

SQL Equivalent:

BEGIN;
SELECT * FROM orders WHERE user_id = 1 FOR KEY SHARE;
-- Perform read operations
COMMIT;

Behavior:

• Multiple transactions can acquire KEY SHARE locks on the same orders.
• No transaction can modify the orderId (primary key) while the KEY SHARE lock is held.
• Transactions can still update non-key columns like amount.

3.4 NO KEY UPDATE Lock

Overview

The NO KEY UPDATE lock allows a transaction to update non-key columns of a row while preventing other transactions from modifying key columns. It offers a balance between the permissiveness of KEY SHARE locks and the restrictiveness needed to maintain key integrity during updates.

Behavior

Selective Exclusivity:

Allows:

• Reading the locked rows.
• Acquiring other NO KEY UPDATE locks.
• Modifying non-key columns.

Prevents:

• Modifying key columns.
• Acquiring UPDATE or EXCLUSIVE locks.
• Lock Compatibility: Compatible with other NO KEY UPDATE and KEY SHARE locks but incompatible with locks that intend to modify key columns.

Use Cases

• Updating Non-Key Data: When you need to update non-key columns without affecting key columns.
• Minimizing Lock Contention: Allows multiple transactions to update non-key data concurrently.
• Maintaining Data Integrity: Ensures key columns remain stable during partial updates.

Example: Using NO KEY UPDATE Lock

async function updateProductPrice(productId, newPrice) {
  const transaction = await sequelize.transaction();

  try {
    // Acquire a NO KEY UPDATE lock on the product row
    const product = await Product.findOne({
      where: { id: productId },
      lock: transaction.LOCK.NO_KEY_UPDATE, // Applying the NO KEY UPDATE lock
      transaction,
    });

    if (!product) {
      throw new Error('Product not found');
    }

    // Update non-key column
    product.price = newPrice;
    await product.save({ transaction });

    // Commit the transaction
    await transaction.commit();
    console.log(`Product ${productId} price updated successfully.`);
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Failed to update product price:', error);
  }
}

// Usage
updateProductPrice(101, 49.99);

Explanation:

1. Start Transaction: Initiate a transaction.
2. Acquire NO KEY UPDATE Lock: Use lock: transaction.LOCK.NO_KEY_UPDATE to lock the row, preventing key modifications.
3. Modify Non-Key Data: Update the price field without altering key columns like id.
4. Commit or Rollback: Commit if successful; otherwise, rollback on error.

SQL Equivalent:

BEGIN;
SELECT * FROM products WHERE id = 101 FOR NO KEY UPDATE;
-- Perform update on non-key columns
UPDATE products SET price = 49.99 WHERE id = 101;
COMMIT;

Behavior:

• Multiple transactions can acquire NO KEY UPDATE locks on the same product row.
• They can concurrently update non-key columns like price.
• Modifications to key columns (e.g., id) are blocked until all NO KEY UPDATE locks are released.

4. Comprehensive Example Combining Multiple Locks

In real-world scenarios, you often need to apply multiple locks within a single transaction to manage different aspects of data concurrently. Here’s an example that demonstrates the use of all four lock types (UPDATE, SHARE, KEY SHARE, NO KEY UPDATE) within a single transaction using async/await.

Scenario

Suppose you are developing an e-commerce platform where:

• Users have Accounts with balances.
• Users place Orders for Products.
• Products belong to Categories.

You need to:

1. Update a user’s balance.
2. Read all orders placed by the user.
3. Update the prices of products in those orders without altering their IDs.
4. Ensure that key columns (like orderId and productId) remain unchanged during these operations.

Comprehensive Transaction Example

async function processUserOrder(userId, amountToAdd, productPriceUpdates) {
  // `productPriceUpdates` is an array of objects: [{ productId: 201, newPrice: 59.99 }, ...]

  const transaction = await sequelize.transaction();

  try {
    // Step 1: Acquire an UPDATE lock on the user row to update balance
    const user = await User.findOne({
      where: { id: userId },
      lock: transaction.LOCK.UPDATE, // UPDATE lock
      transaction,
    });

    if (!user) {
      throw new Error('User not found');
    }

    // Update user's balance
    user.balance = parseFloat(user.balance) + amountToAdd;
    await user.save({ transaction });

    // Step 2: Acquire a SHARE lock on the user's orders to read them
    const orders = await Order.findAll({
      where: { userId: user.id },
      lock: transaction.LOCK.SHARE, // SHARE lock
      transaction,
    });

    if (!orders.length) {
      throw new Error('No orders found for the user.');
    }

    // Step 3: Acquire a KEY SHARE lock on related products to ensure key stability
    const productIds = orders.map(order => order.productId);
    const products = await Product.findAll({
      where: { id: productIds },
      lock: transaction.LOCK.KEY_SHARE, // KEY SHARE lock
      transaction,
    });

    if (!products.length) {
      throw new Error('No products found for the orders.');
    }

    // Step 4: Acquire NO KEY UPDATE locks to update non-key columns of products
    for (const update of productPriceUpdates) {
      const product = products.find(p => p.id === update.productId);
      if (product) {
        await Product.findOne({
          where: { id: product.id },
          lock: transaction.LOCK.NO_KEY_UPDATE, // NO KEY UPDATE lock
          transaction,
        });

        // Update non-key column
        product.price = update.newPrice;
        await product.save({ transaction });
      } else {
        throw new Error(`Product with ID ${update.productId} not found.`);
      }
    }

    // Commit the transaction
    await transaction.commit();
    console.log('Transaction processed successfully.');
  } catch (error) {
    // Rollback the transaction on error
    await transaction.rollback();
    console.error('Transaction failed:', error);
  }
}

// Usage Example
const productPriceUpdates = [
  { productId: 201, newPrice: 59.99 },
  { productId: 202, newPrice: 89.99 },
];

processUserOrder(1, 500, productPriceUpdates);

Explanation:

1. Start Transaction: Initiate a transaction using await sequelize.transaction().
2. Step 1 (UPDATE Lock): Acquire an UPDATE lock on the user row to safely update the balance.
3. Step 2 (SHARE Lock): Acquire a SHARE lock on the user's orders to read them without allowing modifications during the read.
4. Step 3 (KEY SHARE Lock): Acquire KEY SHARE locks on the related products to ensure that their key columns remain unchanged during the transaction.
5. Step 4 (NO KEY UPDATE Lock): For each product to be updated:

• Acquire a NO KEY UPDATE lock to update non-key columns (e.g., price) while preventing key modifications.
• Update the product’s price.

6. Commit or Rollback: Commit the transaction if all operations succeed; otherwise, rollback on any error.

SQL Equivalent:

BEGIN;

-- Step 1: UPDATE lock on user
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET balance = balance + 500 WHERE id = 1;

-- Step 2: SHARE lock on orders
SELECT * FROM orders WHERE user_id = 1 FOR SHARE;

-- Step 3: KEY SHARE lock on products
SELECT * FROM products WHERE id IN (201, 202) FOR KEY SHARE;

-- Step 4: NO KEY UPDATE locks and updates
SELECT * FROM products WHERE id = 201 FOR NO KEY UPDATE;
UPDATE products SET price = 59.99 WHERE id = 201;

SELECT * FROM products WHERE id = 202 FOR NO KEY UPDATE;
UPDATE products SET price = 89.99 WHERE id = 202;

COMMIT;

Behavior:

• User Row: Locked for updates; no other transaction can modify or delete the user until the transaction commits.
• Orders: Locked for shared reading; multiple transactions can read orders but cannot modify them.
• Products: Locked to prevent key modifications; multiple transactions can update non-key columns concurrently.

5. Best Practices

Managing transactions and locks effectively is crucial for maintaining data integrity and ensuring optimal performance. Here are some best practices to follow when working with Sequelize transactions and locks using async/await.

5.1. Keep Transactions Short

• Minimize Lock Duration: Hold locks only for the shortest time necessary to perform operations.
• Perform Quick Operations: Avoid long-running processes within transactions to reduce the risk of deadlocks and improve concurrency.

5.2. Choose the Appropriate Lock Type

• Use Least Restrictive Lock: Apply the least restrictive lock that satisfies your consistency requirements to maximize concurrency.
• Avoid Unnecessary Locks: Don’t lock rows or tables if not required, as it can lead to performance bottlenecks.

5.3. Consistent Lock Ordering

• Acquire Locks in a Fixed Order: To minimize deadlock risks, always acquire multiple locks in a consistent order across transactions.
• Example

// Always lock User before Order
const user = await User.findOne(
  { /* ... */, lock: transaction.LOCK.UPDATE, transaction });
const order = await Order.findOne(
  { /* ... */, lock: transaction.LOCK.SHARE, transaction });

5.4. Handle Deadlocks Gracefully

• Implement Retry Logic: If a transaction fails due to a deadlock, implement retry mechanisms to attempt the transaction again.
• Example:

async function executeWithRetry(fn, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try {
      return await fn();
    } catch (error) {
      if (error.name === 'SequelizeDeadlockError' && i < retries - 1) {
        console.warn('Deadlock detected, retrying transaction...');
        continue;
      }
      throw error;
    }
  }
}

// Usage
await executeWithRetry(() => processUserOrder(1, 500, productPriceUpdates));

5.5. Use Isolation Levels Appropriately

• Set Isolation Levels: Depending on your application’s requirements, set appropriate isolation levels (e.g., READ COMMITTED, SERIALIZABLE) when initiating transactions.
• Example:

const transaction = await sequelize.transaction({
  isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ,
});

• Understand Trade-offs: Higher isolation levels provide more consistency but may reduce concurrency and performance.

5.6. Monitor and Optimize

• Use Database Monitoring Tools: Keep an eye on lock waits, deadlocks, and transaction durations to identify and resolve performance issues.
• Analyze Query Performance: Optimize queries within transactions to ensure they execute quickly.

Conclusion

Managing transactions and applying the appropriate locks is fundamental to maintaining data integrity and ensuring efficient concurrency in your Sequelize-based applications. By leveraging the async/await pattern with sequelize.transaction(), you can write clean, readable, and maintainable code that effectively handles complex transactional operations.