SQL Concurrency Control: Row-Level Locking vs Optimistic Patterns

When you’re building high-traffic systems, the way you handle simultaneous data updates is the difference between a smooth user experience and a pile of 500 errors. I’ve spent more than a few late nights debugging race conditions that only show up when the system is under heavy load. The core problem is always the same: how do we ensure data integrity when multiple processes try to touch the same record at the same time?

In my experience, you’re almost always choosing between two strategies: pessimistic row-level locking and optimistic concurrency control (OCC).

Understanding the Trade-offs

Pessimistic locking assumes conflict is likely. When you read a row, you lock it immediately, forcing other processes to wait until you're finished. This is the "look, don't touch" approach. It’s safe, but it can tank your performance if your transactions stay open too long.

Optimistic concurrency control assumes conflict is rare. You read the record, perform your logic, and only check if the record changed when you go to write back. If it did, you abort and retry. It’s much faster for read-heavy workloads but requires more complex application-side handling.

Pessimistic Locking: The SELECT FOR UPDATE Pattern

In both MySQL and PostgreSQL, the most common way to implement pessimistic locking is the SELECT FOR UPDATE syntax. When I’m working on a critical balance update, I’ll use it to ensure no other process sneaks in a change.

SQL
BEGIN;
-- Lock the row so others wait
SELECT balance FROM accounts WHERE id = 123 FOR UPDATE;
-- Perform calculation in application logic
-- UPDATE accounts SET balance = balance - 50 WHERE id = 123;
COMMIT;

In MySQL, this locks the index record. If you aren't careful, you can easily trigger a database deadlock prevention issue where two transactions end up waiting for each other’s locks. I’ve learned the hard way to always order my row acquisitions consistently across the application. If Transaction A locks rows 1 then 2, and Transaction B locks 2 then 1, you’re asking for a deadlock.

PostgreSQL MVCC and Optimistic Patterns

PostgreSQL handles concurrency differently than MySQL because of its Multiversion Concurrency Control (MVCC) architecture. MySQL vs PostgreSQL: MVCC Architectures for Data Migration is worth a read if you want to understand why Postgres is so efficient at handling concurrent reads without blocking writes.

For optimistic locking, I don't use locks. Instead, I add a version column to my tables.

SQL
-- Application reads the row: id=123, version=5
-- Application calculates new values
UPDATE accounts 
SET balance = 100, version = 6 
WHERE id = 123 AND version = 5;

If the UPDATE affects zero rows, I know someone else updated the record while I was working. I then catch that in my code and trigger a retry or notify the user. This is infinitely more scalable than holding locks, especially when dealing with database performance: mitigating hot row contention in postgres.

Which one should you pick?

If I’m building a queue processor, I almost always reach for SELECT FOR UPDATE SKIP LOCKED. It’s the cleanest way to handle concurrency without forcing every worker to wait for the same row. If I'm building a user-facing form that might stay open for a while, I stick to optimistic locking to avoid holding database connections open indefinitely.

I’ve seen too many systems fail because they used pessimistic locks for long-running processes. If you're building a new feature, start with optimistic locking. Only move to row-level locking when you can prove that your contention rate is high enough to justify the performance cost.

Next time, I’d like to experiment more with advisory locks for application-level coordination, but for now, sticking to standard SQL concurrency control patterns keeps my production environment stable. Don't over-engineer the lock strategy until your telemetry tells you that you actually have a contention problem.

FAQ

Q: Does SELECT FOR UPDATE work the same in MySQL and Postgres? A: Largely yes, but Postgres offers SKIP LOCKED, which is a game-changer for queueing. Check out database queueing with SELECT FOR UPDATE SKIP LOCKED for the implementation details.

Q: How do I handle deadlocks if I use pessimistic locking? A: The best approach is prevention: always acquire locks in the same order, keep transactions as short as possible, and ensure your application code has a retry mechanism for transaction serialization failures.

Q: Is optimistic locking always better for performance? A: Not necessarily. If your system has extremely high contention on a single row (like a global counter), optimistic locking will cause a storm of retries that can be just as damaging as lock contention. In those cases, you might need to rethink your schema.