Understanding Lock Types in Databases

Before diving into the specifics of UPDATE locks, it's essential to have a clear understanding of the primary lock types used in relational databases:

1. Shared Locks (S):

• Purpose: Allow multiple transactions to read a row simultaneously.
• Behavior: Multiple shared locks can coexist on the same row, permitting concurrent reads.
• Restrictions: Prevent transactions from acquiring exclusive locks needed for writes.

2. Exclusive Locks (X):

• Purpose: Allow a single transaction to modify a row.
• Behavior: Only one exclusive lock can exist on a row at any given time.
• Restrictions: Prevent other transactions from reading or writing the locked row until the exclusive lock is released.

3. Update Locks (U):

• Purpose: Serve as a precursor to acquiring an exclusive lock.
• Behavior: Temporarily hold an update lock to indicate an intention to modify a row.
• Restrictions: Prevent other transactions from acquiring conflicting update or exclusive locks.

2. The Two-Phase Locking Mechanism with UPDATE Locks

2.1. What is Two-Phase Locking?

Two-Phase Locking (2PL) is a concurrency control method that ensures serializability — the highest level of isolation — in transactions. It operates in two distinct phases:

1. Growing Phase:

• Lock Acquisition: Transactions acquire locks but do not release any.
• Behavior: As the transaction progresses, it can acquire new locks.

2. Shrinking Phase:

• Lock Release: Transactions release locks but do not acquire any new ones.
• Behavior: Once a transaction starts releasing locks, it cannot acquire any additional locks.

2PL helps in preventing deadlocks and ensures that transactions execute in a manner that maintains data consistency.

2.2. How UPDATE Locks Fit into 2PL

When a transaction intends to update a row, it follows a two-phase approach using UPDATE locks:

1. Acquiring an UPDATE Lock:

• Initial Lock: The transaction first acquires an UPDATE lock on the target row.
• Purpose: Signals the transaction’s intent to modify the row, preventing other transactions from acquiring conflicting UPDATE or EXCLUSIVE locks.

2. Escalating to an EXCLUSIVE Lock:

• Condition for Escalation: If the transaction proceeds with the update operation.
• Lock Promotion: The UPDATE lock is escalated to an EXCLUSIVE lock.
• Behavior: Grants the transaction full control over the row, allowing modifications while blocking other conflicting operations.

3. Releasing Locks:

• Post-Update: Once the update is complete, the EXCLUSIVE lock is released.
• Transition: The transaction moves into the shrinking phase, where it cannot acquire new locks.

2.3. Rationale Behind Using UPDATE Locks

The primary reasons for this two-phase approach are:

1. Deadlock Prevention:

• Scenario: Without UPDATE locks, two transactions might simultaneously acquire EXCLUSIVE locks on different rows and wait for each other to release them, leading to a deadlock.
• Solution: By acquiring an UPDATE lock first, the database can manage lock acquisition more gracefully, reducing the likelihood of deadlocks.

2. Optimizing Lock Management:

• Flexibility: UPDATE locks are less restrictive than EXCLUSIVE locks, allowing for better concurrency.
• Efficiency: Only escalate to EXCLUSIVE locks when necessary, conserving database resources.

The Difference Between UPDATE and EXCLUSIVE Locks

Exclusive Lock (EXCLUSIVE Lock)

• Purpose: Prevent all other transactions from reading or writing the row. No other transactions can access the row until the lock is released.
• Usage: Applied when you are certain that you will update (or delete) the row.
• Downside: It is very restrictive since it blocks all other access (including reads). If you hold the lock for a long time, it can lead to significant contention, and performance could degrade under high concurrency.

Update Lock (UPDATE Lock)

• Purpose: Indicates an intention to update a row but does not yet prevent other transactions from reading it. Prevents deadlocks by ensuring that only one transaction can proceed with the update.
• Usage: Used when you are not sure whether you will actually modify the row but want to ensure you will be the only one with the option to do so.
• Escalation: Once the decision to update is made, it escalates to an exclusive lock.
• Benefit: It’s less restrictive than an exclusive lock and is particularly useful in scenarios where you are reading and checking conditions before deciding whether to update.

2. Why Not Use EXCLUSIVE Lock Directly?

You might wonder: if an exclusive lock solves the concurrency problem, why not use it all the time? Here are the reasons:

2.1. Avoiding Unnecessary Locking

When you use an UPDATE lock, you're saying:

• “I may update this row, but I’m not sure yet.”

For example:

• You fetch a row (e.g., a product’s inventory) and first check a condition (e.g., whether the inventory is sufficient to fulfill an order).
• Only if the condition is satisfied, do you actually modify the row.

Using an exclusive lock too early in such cases would prevent other transactions from even reading the data, even though you may not end up modifying it. This leads to unnecessary contention.

2.2. Optimizing Concurrency

UPDATE locks allow multiple transactions to read the data concurrently, provided that none of them is yet making a final decision to update. This is particularly useful in scenarios where a transaction needs to read the data before making a decision on whether to modify it.

Example scenario:

• Multiple customers are viewing the inventory of a product. They all need to see the current inventory level.
• If a customer decides to purchase, an UPDATE lock ensures only their transaction will be able to proceed with modifying the row.
• Other customers can still read the data (under the UPDATE lock) but can't modify it at the same time.

If you had used an exclusive lock upfront, none of the other transactions would be able to even read the data, which could lead to poor user experience in highly concurrent systems (e.g., an eCommerce platform).

2.3. Preventing Deadlocks

One of the primary reasons for the UPDATE lock is to prevent deadlocks.

Deadlock Example:

• Transaction A locks Row 1 and tries to lock Row 2.
• Transaction B locks Row 2 and tries to lock Row 1.
• Both transactions are stuck waiting for each other to release the lock, resulting in a deadlock.

Using UPDATE locks can prevent such situations because the UPDATE lock is an intent lock that allows one transaction to "announce" its intention to update without immediately blocking others from reading. Once the decision to update is made, the lock escalates to an exclusive lock, avoiding the conditions that lead to deadlocks.

3. Practical Example of Why UPDATE Lock is Better

Consider this scenario:

• You have a table of bank accounts.
• A transaction tries to withdraw money from one account, but it first checks whether the account balance is sufficient.
• Multiple withdrawals may happen concurrently on the same account.

Using an exclusive lock upfront would block all other transactions, even if most of them are just reading or checking conditions. This would severely limit the system’s ability to handle concurrency.

Example Code with UPDATE Lock:

async function withdrawFromAccount(accountId, amount) {
  const transaction = await sequelize.transaction({
    isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ,
  });

  try {
    // Step 1: Acquire an UPDATE lock on the account row
    const account = await Account.findOne({
      where: { id: accountId },
      transaction,
      lock: transaction.LOCK.UPDATE, // Acquire UPDATE lock
    });

    // Step 2: Check the balance
    if (account.balance < amount) {
      throw new Error('Insufficient funds');
    }

    // Step 3: Update the balance
    account.balance -= amount;
    await account.save({ transaction });

    // Step 4: Commit the transaction
    await transaction.commit();
  } catch (error) {
    // Rollback in case of error
    await transaction.rollback();
    throw error;
  }
}

Here’s why the UPDATE lock is useful in this case:

• Multiple transactions can read the account balance.
• Only one transaction will be able to proceed with the update (the lock escalates to EXCLUSIVE at the point of modification).
• Other transactions can continue reading while the first transaction is still checking the balance.

If we had used an exclusive lock from the beginning, every transaction trying to access the account would be blocked before they could even check the balance.

4. Summary: When to Use UPDATE vs. EXCLUSIVE Locks

UPDATE Lock:

• Use when you intend to modify the row, but first need to read it or check conditions.
• Prevents deadlocks and improves concurrency by allowing reads before escalating to an exclusive lock.

EXCLUSIVE Lock:

• Use when you know you need to immediately modify a row and don’t want other transactions to even read the data.