Dealing with Database Contention

High-scale systems face contention when multiple concurrent requests attempt to read and write to the same database records.

1. Concurrency Patterns

• Pessimistic Locking:

Locks database rows explicitly (SELECT ... FOR UPDATE in SQL) during the transaction. Blocks other clients until the transaction commits. Prevents updates but risks deadlocks.

• Optimistic Concurrency Control (OCC):

Rows include a version or timestamp column. When writing, SQL checks:

UPDATE table SET val = x, version = version + 1 WHERE id = 1 AND version = current_version

If another write completed in the meantime, the statement fails, and the application retries. Best for read-heavy systems.

• Distributed Locking (Redlock):

Coordinates lock acquisitions across multiple independent Redis masters using lease timeouts (TTL) to avoid lock starvation.

2. Queue-Based Writes

To eliminate database locks entirely, writes stream to partition queues (Kafka/RabbitMQ) and a single-threaded background worker processes updates sequentially.

3. References & Tech Blogs

• PostgreSQL Documentation: Concurrency Control and Locking
• Redis Documentation: Distributed Locks using Redlock