How Database Transactions Handle Two Users Editing at Once

The short answer

Quick answer: When two transactions touch the same data at the same time, the database uses concurrency control to stop them corrupting each other's work. Most modern databases use multi-version concurrency control (MVCC): instead of overwriting a row, an update creates a new version, and each transaction reads from a consistent snapshot. Readers never block writers and writers never block readers. Two writers to the same row are serialised with a row lock, so the second waits for the first. How strictly transactions are separated is set by the isolation level, and the default level in most databases still allows some race conditions.

The lost update problem

Two customers buy the last concert ticket at the same moment. Each request runs:

SELECT stock FROM tickets WHERE id = 7;        -- both read 1
-- application checks stock > 0, computes 1 - 1 = 0
UPDATE tickets SET stock = 0 WHERE id = 7;     -- both write 0

Both succeed. Two tickets were sold and stock went down by one. One update was lost. This "read, decide, write" pattern is the most common concurrency bug in application code.

The anomalies

Write skew is the subtle one. Two doctors are on call; the rule is at least one must remain. Each checks "is someone else on call?", sees yes, and removes themselves. Both commit. Nobody is on call, yet no row was written twice.

Approach 1: Locks

The traditional solution is locking:

• A shared lock lets many transactions read.
• An exclusive lock lets one transaction write and blocks everyone else.

Holding locks until the transaction ends (two-phase locking) gives full serializability, but readers block writers and writers block readers, so throughput suffers under load.

Approach 2: MVCC

Most databases today (PostgreSQL, MySQL's InnoDB, Oracle, SQL Server in snapshot mode) use MVCC, described in the PostgreSQL concurrency control documentation.

The idea:

• An update does not overwrite the row. It writes a new version tagged with the transaction that created it.
• Each transaction sees a snapshot: only versions committed before a certain point.
• Old versions are kept until no running transaction can need them, then cleaned up (vacuum in PostgreSQL, purge in InnoDB).

The big win is that reads and writes do not block each other. A long-running report sees a stable snapshot while updates continue.

Two transactions that try to update the same row still conflict. The second one waits for the first to commit or roll back, then either proceeds or fails, depending on the isolation level.

Isolation levels in practice

The SQL standard defines four levels, and databases implement them differently. The PostgreSQL isolation documentation is unusually clear about what each actually prevents.

Two practical points:

• The same level name means different things in different databases. Read your database's documentation, or the independent analyses at Jepsen.
• At serializable, and sometimes at repeatable read, the database may abort a transaction with a serialization error. Your code must be ready to retry.

Fixing the lost update

There are four standard fixes for the ticket example.

1. Do it in one atomic statement

UPDATE tickets SET stock = stock - 1
WHERE id = 7 AND stock > 0;

Check how many rows were affected. If zero, the ticket is gone. This is the simplest and best fix when the logic fits in one statement.

2. Pessimistic locking

Lock the row when you read it:

BEGIN;
SELECT stock FROM tickets WHERE id = 7 FOR UPDATE;
-- other transactions asking for this row now wait
UPDATE tickets SET stock = stock - 1 WHERE id = 7;
COMMIT;

Good when conflicts are common. Keep the transaction short, because others are queuing.

3. Optimistic locking

Do not lock. Add a version column and make the write conditional:

UPDATE tickets SET stock = 0, version = version + 1
WHERE id = 7 AND version = 12;

If no row was updated, someone else changed it first; reload and retry. Good when conflicts are rare, and it works across separate requests, such as a user editing a form for several minutes.

4. Raise the isolation level

Run the transaction as serializable and retry on failure. The database detects the conflict for you, including write skew.

Deadlocks

Transaction A locks row 1 and wants row 2. Transaction B locks row 2 and wants row 1. Neither can proceed.

Databases detect this cycle automatically and abort one transaction with a deadlock error. To reduce deadlocks:

• Always lock rows in the same order (for example, by ascending ID).
• Keep transactions short.
• Do not wait on user input or external API calls inside a transaction.
• Retry when a deadlock error occurs.

Beyond one database

All of this works inside a single database. Once several services or databases are involved, you need other tools: idempotency keys so that retries are safe, and sometimes distributed locks, which are much harder to get right. For the guarantees a transaction offers in the first place, see what ACID really means.

Frequently asked questions

What is MVCC?

Multi-version concurrency control. The database keeps several versions of each row so that every transaction can read a consistent snapshot without blocking writers.

What isolation level should I use?

Start with your database's default and protect critical read-then-write logic with atomic updates, SELECT ... FOR UPDATE or version checks. Use serializable for logic that is hard to protect any other way, and add retries.

What is the difference between optimistic and pessimistic locking?

Pessimistic locking takes a lock before changing data so others must wait. Optimistic locking takes no lock and checks at write time whether anyone else changed the data.

Why did my transaction fail with a serialization or deadlock error?

The database detected a conflict it could only resolve by aborting one transaction. This is normal behaviour; retry the transaction.

Conclusion

Databases handle simultaneous edits with versions, snapshots and row locks, but they only protect you as far as your isolation level goes. The default level almost everywhere permits lost updates in "read, then decide, then write" code. Use atomic statements where you can, explicit locks or version checks where you cannot, and always be ready to retry.

Related articles

• What ACID Really Means and Why It Matters
• How Write-Ahead Logs Prevent Data Loss During Crashes
• How Distributed Locks Work (and Why They Often Fail)
• How Payment Systems Avoid Charging You Twice (Idempotency)

Sources and further reading

• Concurrency Control, PostgreSQL documentation
• Transaction Isolation, PostgreSQL documentation
• Consistency models (Jepsen)