Syed Jafer K

Today, I learnt about concurrency control on database from application level. Two widely used strategies to manage concurrency are optimistic locking and pessimistic locking. In this blog, i jot down notes on these two locking mechanism for my future reference.

Pessimistic Locking

Pessimistic locking assumes that conflicts will occur and proactively prevents them by locking data as soon as a transaction accesses it. While the lock is in place, other transactions are blocked from accessing or modifying the same data until the lock is released.

Characteristics of Pessimistic Locking

1. Proactive Approach: Prevents conflicts before they happen.
2. Concurrency Impact: Can reduce concurrency as other transactions are blocked from accessing locked resources.
3. Use Cases: Ideal for scenarios with high contention, such as banking systems or inventory management.

-- Transaction A: Acquires a lock to update a row
BEGIN;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- Transaction B: Attempts to update the same row
BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
-- Transaction B is blocked until Transaction A completes and releases the lock.

In this example, FOR UPDATE acquires a row-level lock, preventing other transactions from modifying the same row until the lock is released.

Optimistic Locking

Optimistic locking assumes that conflicts are rare and allows transactions to proceed without locking resources. Instead, it uses a mechanism like version numbers or timestamps to detect conflicts during the commit phase.

Characteristics of Optimistic Locking

1. Reactive Approach: Detects and resolves conflicts after they occur.
2. Concurrency-Friendly: Allows multiple transactions to proceed simultaneously, improving concurrency.
3. Use Cases: Best for scenarios with low contention, such as read-heavy applications or systems with distributed transactions.

How Optimistic Locking Works

1. Read the Data: A transaction reads the data along with its version number or timestamp.
2. Modify the Data: The transaction modifies the data locally.
3. Check for Conflicts: During the update, the system checks if the version number or timestamp has changed.
4. Commit or Rollback: If the version matches, the transaction commits. Otherwise, it rolls back due to a conflict.

Assume we have a products table with a version column to track changes,

-- Transaction A: Reads data
BEGIN;
SELECT product_id, stock, version FROM products WHERE product_id = 1;
-- Assume version = 3

-- Transaction B: Updates the same row
BEGIN;
UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = 1 AND version = 3;
-- Transaction A: Tries to update the same row
UPDATE products SET stock = stock - 2, version = version + 1 WHERE product_id = 1 AND version = 3;
-- Transaction A fails because the version number has changed.

In this example, the version column helps detect conflicts. Transaction A fails because Transaction B updated the row and incremented the version.

When to use which ?

• Use pessimistic locking in scenarios with high contention or critical operations where preventing conflicts is crucial.
• Use optimistic locking in environments with low contention or distributed systems to maximize concurrency.