Optimistic vs. Pessimistic Locking

Optimistic and pessimistic locking are concurrency control techniques used in databases and distributed systems to manage access to shared resources and ensure data consistency.

Optimistic Locking

Optimistic locking assumes conflicts are rare and works under the expectation that most transactions can proceed without interference.

Key points

• When reading data, a version number or timestamp is recorded
• No locks are placed on the data
• Before updating, the system checks if the version/timestamp matches the original
• If the version has changed, it means someone else modified the data, and the update fails
• The application must then handle the conflict (usually by asking the user to retry)

Algorithm

1. Read the resource without acquiring a lock.
2. When ready
   • Check if the resource has been modified by another transaction.
   • If not, commit the changes.
   • If modified, abort and retry.

When to use Optimistic Locking

Optimistic locking is well-suited for scenarios where conflicts are infrequent and the cost of acquiring and releasing locks is high.

It is commonly used in scenarios where the probability of conflicts is low, such as read-heavy workloads or scenarios where transactions are short-lived.

Real World Examples

• Version control systems like Git use optimistic locking to manage concurrent changes to source code files.
• Online shopping carts use optimistic locking to prevent conflicts when multiple users update the same cart simultaneously.

Example of Optimistic Locking in SQL

-- Initial record
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    version INT
);

-- Reading the record
SELECT id, name, price, version 
FROM products 
WHERE id = 1;

-- Updating with version check
UPDATE products 
SET price = 29.99, version = version + 1 
WHERE id = 1 AND version = 5;

Pessimistic Locking

Pessimistic locking is a concurrency control technique that assumes conflicts between transactions are common. In pessimistic locking, a transaction acquires a lock on a resource before reading or modifying it. The lock prevents other transactions from accessing the resource until the lock is released. This ensures that only one transaction can access the resource at a time, reducing the likelihood of conflicts.

Use cases

Pessimistic locking is well-suited for scenarios where conflicts are common and the cost of acquiring and releasing locks is low.

It is commonly used in scenarios where the probability of conflicts is high, such as write-heavy workloads or scenarios where transactions are long-lived.

Real World Example

• Better for write-heavy systems, long transactions, and high-conflict scenarios (e.g., banking transactions)
• A bank application that locks an account when a user is making a transfer to prevent other users from accessing the account until the transfer is complete.
• An inventory management system that locks a product when a user is updating its quantity to prevent other users from modifying the product at the same time.
• A reservation system that locks a seat when a user is booking it to prevent other users from reserving the same seat.
• A content management system that locks a document when a user is editing it to prevent other users from modifying the document concurrently.

Example of Pessimistic Locking in SQL

-- Using SELECT FOR UPDATE in a transaction
BEGIN TRANSACTION;

SELECT * FROM products 
WHERE id = 1 
FOR UPDATE;  -- This locks the row

-- Make modifications
UPDATE products 
SET price = 29.99 
WHERE id = 1;

COMMIT;  -- Releases the lock

Summary

• Optimistic locking assumes conflicts are rare and works under the expectation that most transactions can proceed without interference.
• Pessimistic locking assumes conflicts are common and acquires locks on resources to prevent conflicts between transactions.
• Optimistic locking is well-suited for scenarios where conflicts are infrequent and the cost of acquiring and releasing locks is high.
• Pessimistic locking is well-suited for scenarios where conflicts are common and the cost of acquiring and releasing locks is low.
• The choice between optimistic and pessimistic locking depends on the characteristics of the workload and the probability of conflicts between transactions.