Concurrent transactions in PostgreSQL and MySQL from MVCC to locking

Two database engines look interchangeable on a feature checklist: both speak SQL, both run under Linux, both promise ACID compliance. The difference surfaces at two in the morning, when two sessions grab the same rows and the application starts reporting numbers nobody can explain. This article compares PostgreSQL and MySQL the way a production workload compares them, through concurrent transactions: where each engine keeps old row versions, what the default isolation levels deliver on a live connection, how locks behave when writes collide, and what every connection costs the server. The commands below were written against PostgreSQL 17 and MySQL 8.4 LTS, and every claim is checkable in a psql or mysql session.

Two MVCC engines with opposite storage choices

Both engines implement multiversion concurrency control, and the abbreviation hides the question that actually matters: where do old row versions live? PostgreSQL keeps them inside the table itself. An UPDATE writes a new version of the row into the same heap file, stamps it with the identifier of the creating transaction in the hidden xmin column, and marks the old version as superseded by writing xmax into it. Any transaction that started earlier can still read the old version, which is how a reader never blocks a writer and a writer never blocks a reader in PostgreSQL.

The price appears later. Dead versions accumulate inside the heap and inside the index entries that point to them, and VACUUM is the process that reclaims the space. The autovacuum background worker runs this cleanup by default, but its trigger thresholds scale with the table, and a busy table with the default scale factor of 0.2 can wait until twenty percent of the rows are dead before it is vacuumed. The tuning knobs sit in postgresql.conf:

autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 2000

The first line makes vacuuming start when five percent of the rows are dead, the second raises the cleanup budget per cycle. Seeing the damage is one query against the statistics view:

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 5;

One more mechanism matters for write heavy tables: HOT updates. When an UPDATE touches no indexed column and the new version fits on the same page, PostgreSQL keeps the new version heap-only and skips index maintenance entirely, and the fillfactor setting reserves free space on each page so those updates keep fitting over the life of the table.

MySQL takes the opposite path. InnoDB keeps exactly one version of each row in the clustered primary key index, and reconstructs older versions on demand from undo logs written at update time. The cleanup is done by the purge thread, which removes undo records once no active transaction needs them. When a long running transaction holds a snapshot for hours, the purge stalls, the history list grows, and SHOW ENGINE INNODB STATUS prints the number:

------------
TRANSACTIONS
------------
Trx id counter 1234567
History list length 421

The transactions view names the culprits directly:

SELECT trx_id, trx_started, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started;

The operational consequences differ in shape but rhyme in effect. PostgreSQL bloats tables and indexes that autovacuum cannot keep up with, and the query plans degrade gradually. InnoDB accumulates undo that slows every read which has to walk back through versions, and the buffer pool fills with pages the working set no longer needs. Both engines penalize the same mistake, the long lived transaction, and both reward the same discipline, short transactions and monitored cleanup.

The default isolation levels and what they deliver

The defaults differ, and identical application code behaves differently on each engine. PostgreSQL starts every transaction at READ COMMITTED: each statement takes a fresh snapshot and sees everything committed when that statement began. InnoDB starts at REPEATABLE READ: the first read establishes a snapshot and every later read inside the transaction sees the same one, which is the default value of the transaction_isolation server variable on every standard MySQL installation.

Checking and changing the level is one command on each side:

SHOW TRANSACTION ISOLATION LEVEL;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SHOW VARIABLES LIKE 'transaction_isolation';
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

The reporting consequence is concrete. A summary job that reads five tables inside one transaction gets five consistent tables on MySQL by default, because the snapshot was fixed at the first read. The same job on PostgreSQL under READ COMMITTED can see a commit land between table two and table three, and a report that disagrees with itself is the result. The fix costs one line, an explicit SET TRANSACTION ISOLATION LEVEL REPEATABLE READ at the start of the reporting transaction, but nobody writes that line until a finance report sums two tables that were captured half a second apart.

Gap locks next-key locking and the range read story

The SQL standard permits newly matching rows to appear between queries at REPEATABLE READ, and InnoDB quietly refuses. Its REPEATABLE READ is enforced with next-key locks, a record lock on the indexed row combined with a gap lock on the space before it. Locking a range locks the inserts into that range:

SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE;

A concurrent INSERT with id 150 now waits until the locking transaction ends, so a second count over the same range returns the same rows. No new rows slip in, at the cost of blocking inserts that PostgreSQL would have allowed through. A write heavy table with frequent range locks and frequent inserts is the workload where this difference shows up as insert latency, and the SHOW ENGINE INNODB STATUS sections for lock waits are where it becomes visible.

PostgreSQL under READ COMMITTED does allow the range read: each statement evaluates against a new snapshot, and a row committed by another session between two counts appears in the second one. PostgreSQL REPEATABLE READ closes the range read the other way, with a pure snapshot and no range locks, so a transaction that only reads never blocks anyone and is never blocked.

PostgreSQL SERIALIZABLE, since version 9.1, is built on Serializable Snapshot Isolation with predicate locks that track which rows and ranges a transaction touched. When two concurrent transactions threaten an order that no serial execution could produce, one of them is aborted with an error the server prints verbatim:

ERROR:  could not serialize access due to read/write
        dependencies among transactions

SQLSTATE 40001. The application is expected to catch it and retry, and that retry loop is the price of genuine serializability without global blocking. The predicate locks themselves are visible, which turns contention from a mystery into a measurement:

SELECT locktype, relation::regclass, mode, pid
FROM pg_locks
WHERE NOT granted;

Lost updates and the read modify write trap

Neither default isolation level saves an application from the classic lost update, because the trap lives above the engine. Session one reads a quantity, computes a new value in application code, and writes the result back. Session two does the same with the value it read. One change silently wins.

Concretely, a stock table holds quantity 10 for sku A100. Two sessions each read 10, each decide to sell one unit, each write 9. Two sales happened, the stock says 9, and the books are wrong by one unit with no error anywhere:

BEGIN;
SELECT quantity FROM stock WHERE sku = 'A100';
UPDATE stock SET quantity = 9 WHERE sku = 'A100';
COMMIT;

Both sessions run this and both succeed, because under PostgreSQL READ COMMITTED the UPDATE re-checks its WHERE clause against the newest committed row, and the literal quantity = 9 is simply written twice. Two repairs exist, and both are one line. The pessimistic one locks the row so the second session waits its turn:

SELECT quantity FROM stock WHERE sku = 'A100' FOR UPDATE;

The optimistic one turns the write into a guarded arithmetic operation:

UPDATE stock SET quantity = quantity - 1
WHERE sku = 'A100' AND quantity >= 1;

and the application checks the affected row count, because a zero means somebody else moved first. Under InnoDB REPEATABLE READ the plain SELECT is a consistent nonlocking read, so the identical trap exists on both engines, and FOR UPDATE is the same escape on both. There is one engine level surprise worth knowing: on PostgreSQL at REPEATABLE READ, a session that tries to UPDATE a row another transaction already changed and committed receives a hard error instead of a silent overwrite:

ERROR:  could not serialize access due to concurrent update

The engine refuses to guess, the transaction rolls back, and the application retries. That strictness is a feature, not an inconvenience, because the alternative is the silent loss above.

Deadlocks lock waits and the error text to watch for

Both engines detect deadlocks rather than wait forever, but with different timing. PostgreSQL waits deadlock_timeout, one second by default, before running the cycle detector, and then aborts the transaction that ran the check:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 567;
         blocked by process 567.

Plain lock waits are unbounded on PostgreSQL because lock_timeout defaults to zero, so a query queued behind a long transaction simply sits. Setting log_lock_waits = on makes the server log any wait longer than deadlock_timeout, which is how the queuing becomes visible before users complain. InnoDB runs its deadlock detector when the wait begins and rolls back the transaction with less undo to process:

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Row lock waits on MySQL end in a different error after innodb_lock_wait_timeout, 50 seconds by default:

ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction

The SHOW ENGINE INNODB STATUS output keeps a LATEST DETECTED DEADLOCK section with the statements of both transactions, which is the first place to look during an incident. On PostgreSQL the same visibility comes from pg_locks and the server log. The operational conclusion is identical for both engines: retry logic for these specific errors is not optional in production code, because deadlocks are normal traffic, not exceptional failures. The standard pattern is a bounded retry loop with jitter, three attempts for most workloads, and a metric that counts the retries, because a retry count that trends upward is the earliest warning that lock contention is growing.

Connections processes threads and the pooling arithmetic

A PostgreSQL connection is a separate backend process, created when the client authenticates. Each process costs several megabytes of resident memory before it has read a single row, and max_connections defaults to 100. Three thousand direct connections at five megabytes each burn fifteen gigabytes just to exist, more than many servers allocate to the shared buffer cache, which is why a pooler such as PgBouncer in transaction pooling mode is standard equipment on busy PostgreSQL fleets. Ten thousand application connections multiplex over a few dozen real backends, and the fork cost disappears. Transaction pooling has one caveat that bites teams during migration: session level state, from prepared statements to advisory locks to temporary tables, does not survive a client switch between backends, so the application must hold no session state across transactions.

MySQL maps a connection to a thread inside the single server process, and threads are cheaper than forked processes, with max_connections defaulting to 151. The arithmetic still bites, because several classic settings are per connection, sort_buffer_size and join_buffer_size among them, and a few hundred concurrent sorts each holding their buffer can exhaust memory. The answer is the same on both platforms: pool at the application tier, keep direct connections modest, and treat the connection count as a capacity number that gets measured, not assumed.

Choosing an engine when concurrent writes dominate

The honest comparison does not crown a winner, it maps the decision. PostgreSQL fits the workload where writes collide constantly, where the application wants true serializability with predicate locks, partial indexes and the richer planner, and where the team will invest in autovacuum tuning and a pooler from day one. InnoDB fits the workload that is read mostly, navigates by primary key, and benefits from defaults that hold a whole transaction snapshot without any configuration. Both engines reward the same habits, and the checklist for a write heavy deployment is short:

1. Fix the isolation level explicitly in application code and never rely on the server default;
2. Wrap every read modify write in FOR UPDATE or an optimistic guard;
3. Add a retry loop for SQLSTATE 40001 before the first production deploy;
4. Put a pooler in front of the database before the connection count reaches three digits;
5. Load the exact transaction pattern in staging and watch the lock views, not the response times alone.

The engine decides where dead row versions live and which defaults guard consistency. The application decides whether those guards are armed. A benchmark between the two engines that does not model the real transaction pattern measures the wrong thing, and the cheapest way to run that benchmark is to read the code that will issue the writes, reproduce it in staging, and count the locks on a quiet Friday afternoon.