Concurrency Challenges¶

Race Condition¶

1. Optimistic Concurrency Control (OCC)¶

This approach assumes that multiple transactions can frequently complete without interfering with each other. It doesn't lock the row; instead, it checks for changes before committing.

• How it works: You add a version or timestamp column to your table.
• The Process: 1. Read the row and note the current version (e.g., version = 1).
• Perform your logic.
• Update the row only if the version is still 1: UPDATE table SET balance = 50, version = 2 WHERE id = 123 AND version = 1.
• Best for: Systems with high read volume but low write contention (fewer collisions).

2. Pessimistic Locking¶

This approach assumes the worst—that a collision will happen—so it locks the data the moment it is read.

• How it works: You use a specific SQL command to "claim" the row until your transaction is finished.
• The Syntax (SQL): SELECT * FROM accounts WHERE id = 123 FOR UPDATE;
• The Result: Any other process trying to SELECT ... FOR UPDATE or UPDATE that specific row will be forced to wait until your transaction COMMITs or ROLLBACKs.
• Best for: High-contention environments where data integrity is critical (like banking).

3. Atomic Database Operations¶

Instead of pulling data into your application code, calculating a new value, and saving it back, you let the database handle the math in a single step.

• The Race Condition Way:
• App reads: balance = 100
• App calculates: 100 - 10 = 90

• App saves: UPDATE accounts SET balance = 90 (If another process saved 80 in between, it gets overwritten).

• The Atomic Way:

• UPDATE accounts SET balance = balance - 10 WHERE id = 123 AND balance >= 10;

• Why it works: The database engine executes this as a single, indivisible unit of work.

4. Distributed Locks (Redis/Zookeeper)¶

If you are working across multiple microservices or servers where a single database lock isn't enough, you use a distributed lock manager.

• Redlock (Redis): Before a service starts a sensitive task, it tries to set a "key" in Redis with a specific timeout. If it succeeds, it has the "lock." Other services see that the key exists and wait.
• Best for: Tasks that aren't strictly database-related, like ensuring only one server sends a specific email or processes a third-party API payment.

5. Database Isolation Levels¶

You can also adjust how the database itself handles visibility between transactions. Most modern databases default to READ COMMITTED, but you can move up the chain:

Dirty Reads¶

1. Set Database Isolation Level to "Read Committed"¶

This is the most common solution. Most modern databases (like PostgreSQL and SQL Server) use this as their default setting.

• How it works: The database ensures that any SELECT query only sees data that was committed before the query started.
• The SQL: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
• Result: If Transaction A is mid-update, Transaction B will read the old (pre-update) version of the data until Transaction A hits "Commit."

2. Use Snapshots (MVCC)¶

Many databases use Multi-Version Concurrency Control (MVCC). Instead of locking rows, the database keeps multiple versions of a row.

• How it works: When a transaction starts, the database gives it a "snapshot" of the data at that exact moment.
• Result: Even if other transactions are changing data in the background, your transaction stays in its own consistent "time bubble."

3. Pessimistic Locking (Read Locks)¶

If your database doesn't support MVCC or you are on a very low isolation level, you can manually prevent others from reading "work in progress."

• Shared Locks: You can apply a lock that allows others to read but prevents them from updating until you're done.
• The SQL: SELECT * FROM table WHERE id = 1 LOCK IN SHARE MODE; (syntax varies by DB).

4. Application-Level Validation¶

While not recommended as a primary fix, some developers handle this in the app logic by using a State Machine.

• How it works: You add a status column (e.g., PENDING, COMPLETED).
• The Logic: Your application queries ignore any rows marked as PENDING, effectively creating a manual "Read Committed" filter.