Optimistic lock & Pessimistic lock

Pessimistic lock

• Lock
• READ
• UPDATE
• Unlock

Example:

START TRANSACTION;

-- Select the product row for update
SELECT availabl_quantity FROM products 
WHERE product_id = 1 FOR UPDATE;

-- Code logic: Check if availabl_quantity > 0;

-- Create a purchase
INSERT INTO purchase (user_id, product_id, purchase_quantity)
VALUES(123, 1, 1);

-- Perform operations on the selected data
UPDATE products 
SET available_quantity = availabl_quantity - 1,
WHERE product_id = 1;


-- Commit the transaction
COMMIT;

PROs:

• Prevents conflicts.
• Ensures data integrity.
• Suitable for high contention.

CONs:

• Decreased throughput.
• Increased deadlock risk.
• Reduced responsiveness.

Use Case:

Pessimistic locking is often employed in scenarios where conflicts are likely or must be avoided, such as:

• Banking systems where transactions involving account balances must be processed serially.
• Reservation systems where concurrent bookings for the same resource must be prevented.

Optimistic lock

• READ
• UPDATE WITH CONDITION (version)
• RETRY IF THERE IS NO RECORDS MATCHED

Example:

-- Check current version of the product
SELECT available_quantity FROM products WHERE product_id = 1;

-- Update product price (assuming version 1)
UPDATE products
SET available_quantity = available_quantity - 1,
    version = version + 1
WHERE product_id = 1 AND version = $1;

-- Retry if update count is zero

-- Create purchase if update product successfully 
INSERT INTO purchase (user_id, product_id, purchase_quantity)
VALUES(123, 1, 1);

PROs:

• Minimal performance impact.
• Allows concurrent access.
• Suitable for infrequent conflicts.

CONs:

• Risk of conflicts.
• Requires conflict resolution. (retry)
• Not ideal for high contention.

Use Case

Optimistic locking is commonly used in scenarios where the likelihood of conflicts is low, such as:

• Content management systems where users rarely edit the same document simultaneously.
• E-commerce platforms where product prices are updated infrequently.