Concurrency Gone Wrong: Fixing Lost Updates with Database Locking

Hello, today i am going to deep dive into optimistic and pessimistic locking from a database perspective. First, let's look at the following example:

DO $$
DECLARE
    current_stock INT;
BEGIN
    SELECT stock INTO current_stock
    FROM products
    WHERE id = 1;

    UPDATE products
    SET stock = stock - 1
    WHERE id = 1;
END $$;

the above example is very simple and easy to understand. Inside the transaction (BEGIN…COMMIT) we are reducing the stock quantity for product with id = 1. Do you think there is any problem here?

Unfortunately, yes. There is a problem: we didn't handle concurrency. Consider the following scenario to understand the problem here:

Let's say two people are buying the same product (id = 1) at the same time, so what will happen is as follows:

For Person 1, Transaction starts, reads the stock value (assume 100), and updates it to 99. For Person 2, Transaction starts, reads the stock value (it is still 100), and updates it to 99.

Now, here two people bought it, so stock would be 98 now but it is showing 99, this problem is known as Lost Updates Under Concurrency. The solution for this problem is Locking.

Types of Locking:

1. Pessimistic Locking:

• In pessimistic locking, we lock the row beforehand within a transaction so no other transaction can write or acquire lock on it simultaneously.

DO $$
DECLARE
    current_stock INT;
BEGIN
    SELECT stock INTO current_stock
    FROM products
    WHERE id = 1
    FOR UPDATE;
 
    UPDATE products
    SET stock = stock - 1
    WHERE id = 1;
END $$;

• we use FOR UPDATE keyword and lock the row using that, this way database knows that product table row with id = 1 should be locked.

• This lock is kind of lock in which no other transaction would be allowed to access this row for update or for locking but reading may be allowed depending on isolation levels.

• The other transaction trying to update the value or trying to acquire lock will simply have to wait till the time lock gets unlocked and it might be possible that timeout will occur and other transaction will simply fail.

• From the perspective of distributed system, this approach prioritizes consistency, but can increase latency and reduce throughput under high contention.

• Pessimistic locking works well when contention is high and strict consistency is required, especially in single-node systems.

• In distributed systems coordination over different nodes needs to be managed and If a deadlock occurs between transactions, it can significantly impact latency and require one transaction to be aborted.

2. Optimistic Locking:

• In optimistic locking we allow concurrent transactions and use versioning to resolve conflicts at the end.

DO $$
DECLARE
    current_stock INT;
    current_version INT;
BEGIN
    SELECT stock, version
    INTO current_stock, current_version
    FROM products
    WHERE id = 1;

    UPDATE products
    SET stock = stock - 1, version = version + 1
    WHERE id = 1
    AND version = current_version;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Conflict detected: version mismatch';
    END IF;

END $$;

• here, transaction 1 and transaction 2 will be happening concurrently but in one of the transactions, the version will no longer match, so the update will affect 0 rows and as we have written IF NOT FOUND clause it will raise an exception and will fail.

• here, there are no explicit locks involved as such it is like everyone can access that row (unless database internally does some locking) but when the final changes are to be done we check if there is any conflict or not and if there are any conflicts based on version number mismatch we make the transaction fail.

• From the perspective of distributed system, we get strong availability, low latency and consistency is enforced at write time, but transactions may temporarily operate on stale data before conflict detection.

• We shall be cautious when using this type of locking as we need to handle additional things like exponential backoff, retries, conflict resolution etc.

• Many NoSQL systems rely on similar optimistic concurrency approaches because they are easier to scale across multiple nodes.

Note: The behavior of these scenarios also depends on the database isolation level (e.g., READ COMMITTED, REPEATABLE READ, SERIALIZABLE), which defines how transactions interact.

Use Pessimistic Locking When:

• Contention is high (many users updating the same row).

• Conflicts are very likely.

• You cannot afford failures or retries.

• Strong consistency is required immediately.

Use Optimistic Locking When:

• Contention is low.

• Conflicts are rare.

• You can retry safely.

• System needs high throughput and scalability.