Optimistic vs Pessimistic Locking: Handling Race Conditions in High-Contention Databases

Optimistic vs Pessimistic Locking is a crucial topic for managing race conditions in high-traffic databases. Picture an e-commerce flash sale for a Nintendo Switch, with only one item in stock. Two customers, Alice and Bob, attempt to buy it at the same moment. Both read the stock level as 1, decide it's available, and try to update the stock to 0.

Unfortunately, the database can only accommodate one update at a time, leading to the Lost Update Problem where both customers receive an order confirmation but only one item exists.

To resolve concurrent race conditions, databases provide two primary concurrency control patterns: Pessimistic Locking and Optimistic Locking. Pessimistic locking is the approach that assumes conflicts will occur. It secures the database row immediately after reading it by locking it. This guarantees absolute consistency but can be problematic when many users want to access the same data simultaneously. In such cases, 499 users are blocked, waiting for a database lock.

Optimistic locking, on the other hand, is a strategy that assumes conflicts are rare. It doesn't lock the database row during read operations. Instead, it adds a version column to the table to track changes. The execution flow involves reading the current stock level and version number, then updating the stock and incrementing the version in a single SQL statement. If the update affects only one row, the purchase is considered successful; otherwise, the application must rollback and retry the transaction.

Python developers can implement optimistic locking with a retry loop, attempting the transaction up to a certain number of times before raising an error. This approach is particularly suitable for scenarios with low to moderate contention, such as user profile updates or CMS edits, where conflicts are infrequent. High-contention situations, like flash sales or auction bidding, benefit from Pessimistic Locking, ensuring that only one user can access the data at a time.