08-database-concurrency-and-locking.md

Database Concurrency, Locking, & Idempotency

Interview Definition:

"Concurrency control is how a database makes sure that if two people try to change the exact same piece of data at the exact same millisecond, they don't accidentally overwrite each other's work or cause math errors."

1. Concurrency Control Mechanisms

A. Pessimistic Locking

Assume that conflicts will happen frequently. Therefore, explicitly lock the resource before reading or writing to it, preventing anyone else from touching it until you are done.

  • How it works (SQL): SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  • This statement places an exclusive lock on row 1. If another transaction runs the exact same query, it will hang and wait until the first transaction runs COMMIT or ROLLBACK.
  • Pros: Absolutely safe. Prevents any concurrent modification.
  • Cons: Slow. Causes high contention and potential deadlocks if transactions wait for each other in a circle.

B. Optimistic Locking

Assume conflicts are rare. Do not take any locks when reading data. Instead, when you are ready to write/update, check if the data has been modified by someone else since you read it.

  • How it works (Version Numbers): Add a version integer column to your table.
    1. T1 reads Row A: balance=100, version=1.
    2. T2 reads Row A: balance=100, version=1.
    3. T1 updates: UPDATE accounts SET balance=90, version=2 WHERE id = 1 AND version = 1; (Success! 1 row updated).
    4. T2 updates: UPDATE accounts SET balance=80, version=2 WHERE id = 1 AND version = 1; (Fails! 0 rows updated, because version is now 2). T2 must retry the whole process.
  • Pros: Very fast for read-heavy workloads because there are no locks. No deadlocks.
  • Cons: If conflicts are frequent, the system wastes resources constantly aborting and retrying transactions.

2. Distributed Locks

In a microservices architecture, you might have 10 instances of your Payment Service running on 10 different servers. A standard programming mutex (synchronized in Java, std::mutex in C++) only locks threads within the same server process.

To prevent two different servers from processing the same order simultaneously, you need a Distributed Lock.

Redis as a Distributed Lock (Redlock Algorithm)

  1. Server A wants to process Order 123.
  2. Server A tries to set a key in Redis: SET lock:order_123 "server_A" NX PX 5000 (NX = Only set if it doesn't exist. PX = Expire in 5000ms to prevent infinite lock if Server A crashes).
  3. If successful, Server A processes the order.
  4. Server B tries to process Order 123, tries the same SET command. Redis rejects it because the key exists. Server B knows someone else is handling it.
  5. Server A finishes and deletes the key in Redis to release the lock.

3. Idempotency (CRITICAL for Payment Gateways)

An operation is idempotent if executing it multiple times produces the exact same result as executing it once.

Why is this needed?

A user clicks "Pay ₹500". The request hits the server, the server deducts ₹500, but before the server can reply "Success", the user's internet drops. The user opens the app again and hits "Pay ₹500" again. The server receives the request again. If it's not idempotent, the user is charged ₹1000!

How to implement Idempotency in Payments:

  1. When the client app prepares to initiate a payment, it generates a unique Idempotency Key (a random UUID).
  2. The client sends the payment request along with this key: POST /charge {amount: 500, idempotency_key: "abc-123"}.
  3. The server checks its database (or Redis): "Have I seen the key 'abc-123' before?"
    • No: Process the payment. Save "abc-123 -> SUCCESS" in the DB. Return 200 OK.
    • Yes, and status is SUCCESS: Do NOT process payment again. Just return the cached 200 OK response.
    • Yes, and status is IN_PROGRESS: Another thread is currently processing this. Return 409 Conflict or tell the client to wait.

This guarantees that even if a user mashes the Pay button 10 times, they are only charged once.