How SQL Databases Handle Concurrent Writes

That Moment Your Database Refuses Two People at Once

I was debugging a ticket system once where two support agents picked up the same customer issue within seconds of each other. The database let them both click "assign" and recorded two different agents for the same ticket. Chaos ensued. That's when I learned that SQL databases have very specific ways of handling concurrent writes, and they're not always as forgiving as we assume.

The Core Problem: Two People, One Row

When two users try to update the same row at the exact same millisecond, the database can't just let both changes happen simultaneously without rules. Without those rules, you'd end up with something called a "lost update" – where one person's change silently overwrites the other's.

How Databases Actually Lock Things

SQL databases use locks, but they're not all the same kind. Here are the main strategies:

Row-level locking is the most common in modern databases like PostgreSQL and MySQL with InnoDB. When someone starts an UPDATE on row 42, the database puts a lock on that exact row. The second person's query waits until the first transaction finishes. It's like having a single bathroom at a party – everyone queues up but nobody misses their turn entirely.

Table-level locking is simpler but harsher. Early MySQL with MyISAM would lock the entire table during any write operation. Imagine one person editing a document while everyone else has to stare at a spinning beach ball. That's table-level locking in action.

What About "Dirty Reads" and Isolation Levels

Here's something that surprised me early on: you can actually configure how strict your database is about concurrent writes using isolation levels.

The READ COMMITTED level (default in PostgreSQL and SQL Server) means your transaction only sees committed data. But within a transaction, two different reads of the same row might return different results if someone else updated it in between. This is called "non-repeatable read."

The SERIALIZABLE level is the strictest. It forces the database to pretend that all transactions happened one after another, even if they actually overlapped. The tradeoff is performance – Serializable mode can be 10-20% slower under heavy load, depending on your database.

Real-World Examples

At PythonSkillset, we once built an inventory system for a sports equipment shop. Two customers could theoretically order the last pair of running shoes at the exact same second. Without proper locking, both would get "thanks for your order" notifications, but only one pair existed.

We solved it using optimistic locking. We added a version column to the products table. Every update checked that the version number hadn't changed since the user loaded the page. If it had changed (meaning someone else already bought the shoes), the update failed, and the user got a "sorry, those are gone" message instead of a phantom confirmation.

Here's what that looked like in practice:

UPDATE products 
SET stock_count = stock_count - 1, version = version + 1
WHERE product_id = 123 AND version = 7;

If the version was no longer 7 (because another transaction already ran the same update), zero rows would be affected, and the application could handle the conflict gracefully.

Deadlocks: The Nightmare Scenario

Deadlocks happen when two transactions each hold a lock the other needs. Transaction A locks row 1 and waits for row 2. Transaction B locks row 2 and waits for row 1. They're both stuck forever unless the database kills one.

Most databases detect deadlocks automatically and roll back the least expensive transaction. But detection alone isn't prevention. The way to avoid deadlocks is to always access tables in the same order in your code. If every piece of code that touches inventory always updates the products table before the orders table, you'll rarely hit a deadlock.

When Locks Aren't Enough

Sometimes locking creates more problems than it solves. High-traffic systems like payment gateways or social media feeds often use queue-based approaches instead. Instead of letting every user write directly to the database, you put write requests into a queue and process them one at a time.

This is why you sometimes see "please wait, your transaction is being processed" on e-commerce sites. It's not a lie – the system is serializing writes through a queue to avoid conflicts.

What You Should Actually Do

For most applications, standard row-level locking with READ COMMITTED isolation will serve you fine. You only need to think about advanced locking strategies when: - You're doing financial transactions where every millisecond matters - You have multiple users updating the same rows frequently - You're building a system that needs to handle thousands of writes per second

Start simple, measure where your bottlenecks are, and add sophistication only when you're actually hitting problems. The database handles concurrent writes better than most developers give it credit for – just don't forget to handle the edge cases in your application code too.

And if you're ever debugging a situation where two users somehow both got the last item? Check your transaction isolation level first. That's almost always where the problem hides.