Transaction Locking in MySQL and PostgreSQL

When a database transaction reads or changes rows, locks determine whether other transactions must wait before they can make conflicting changes. MySQL’s InnoDB engine and PostgreSQL both use multiversion concurrency control (MVCC), which lets readers see committed versions of data while other transactions change rows. The databases differ in how they protect ranges of rows and handle conflicts.

In MySQL 8.4, InnoDB’s default REPEATABLE READ isolation can lock gaps between index records, which makes inserts into a scanned range wait. PostgreSQL’s default is READ COMMITTED; at SERIALIZABLE, it tracks read/write conflicts and can abort a transaction instead of blocking a conflicting insert. Those differences affect transaction design, retry handling, and how indexes influence contention. The details below compare InnoDB with PostgreSQL 18; other storage engines, versions, and configured defaults can behave differently.

A plain read does not lock rows the same way as a locking read

An ordinary SELECT gives a transaction a view of committed data. A locking read, such as SELECT ... FOR UPDATE, also asks the database to protect selected rows against conflicting changes. The distinction matters because a stable view of data does not necessarily stop another transaction from updating it.

InnoDB uses nonlocking consistent reads by default. At its default REPEATABLE READ level, consistent reads in a transaction share a snapshot, a view of the data, established by the first such read. PostgreSQL defaults to READ COMMITTED, where each statement sees data committed before that statement began. At PostgreSQL’s REPEATABLE READ level, the transaction instead uses a stable snapshot. The two products’ isolation levels therefore do not imply identical behavior; compare the InnoDB isolation rules with PostgreSQL’s isolation rules.

If a transaction must read a row and then change it without another transaction changing it in between, use a locking read and update the row in the same transaction. An ordinary SELECT does not prevent another transaction from changing the row.

InnoDB can lock the gaps between index records

InnoDB applies row-level locks to index records. Under REPEATABLE READ, a range search or index scan can take a next-key lock, which combines a lock on an index record with a lock on the gap before it. That gap lock prevents another transaction from inserting a record into the protected range. InnoDB uses these locks to prevent phantom rows in relevant locking searches and scans, as described in its locking documentation.

For example, suppose a transaction runs a locking range query for IDs above 100 and finds the row with ID 102. If the scan uses the relevant index and takes a next-key lock on the range before 102, another transaction inserting ID 101 must wait. The first transaction has protected part of the range, including a row that did not exist when it ran the query.

InnoDB scans and locks records based on the index and search condition. When a query uses a unique index to find one row by its exact unique value, InnoDB can lock that index record without locking the gap before it. A range scan can lock index records it examines, including records that the full WHERE condition later excludes. Check the query’s access path when a locking statement blocks more inserts or updates than its returned rows suggest. At READ COMMITTED, InnoDB disables gap locking for searches and index scans, with exceptions for foreign-key and duplicate-key checks.

PostgreSQL uses row locks and separate conflict tracking

PostgreSQL row locks block conflicting updates, deletes, and other locking reads on selected rows. PostgreSQL provides four strengths: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE. For example, FOR KEY SHARE allows some changes that a stronger row lock would block. PostgreSQL also takes table-level locks for ordinary operations, and its row locks do not block ordinary reads of the same row. See the PostgreSQL locking rules.

At REPEATABLE READ, PostgreSQL prevents phantom reads, which occur when a repeated query finds new rows. Transactions can still produce serialization anomalies: outcomes that would not occur if the transactions ran one at a time. PostgreSQL’s SERIALIZABLE level adds Serializable Snapshot Isolation, which tracks read/write dependencies. Its predicate locks, visible internally as SIReadLock, record which data a transaction read so PostgreSQL can detect dangerous conflicts. They do not block other transactions. When PostgreSQL finds a conflict pattern that could produce a nonserializable result, it aborts a transaction with a serialization failure. The application must retry the entire transaction, including its reads.

That creates a practical difference: InnoDB’s next-key locks can make a conflicting insert wait, while PostgreSQL’s serializable conflict tracking can let transactions proceed and later abort one. Neither mechanism is inherently faster. Waiting, retry costs, query plans, indexes, and workload contention determine the outcome.

Use nonblocking reads selectively

Both databases support NOWAIT, which reports a lock conflict instead of waiting, and SKIP LOCKED, which omits rows already locked by another transaction. This makes SKIP LOCKED better suited to work queues than queries that need every matching row. MySQL documents these options in its SELECT reference.

A queue worker can select an available job with a locking read, then mark that job as claimed before committing:

BEGIN;

SELECT id
FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Mark the selected job as claimed, then commit.
COMMIT;

Keep the selection and update in the same short transaction. The explicit ordering makes selection predictable, while SKIP LOCKED lets another worker move past a row that is currently locked. It does not guarantee that every eligible job appears in each worker’s result.

Both databases can deadlock

A deadlock occurs when transactions wait on locks held by one another. For instance, one transaction can lock row A and then request row B, while another locks B and then requests A. Both MySQL and PostgreSQL detect deadlocks and abort a transaction. Applications should handle these failures by retrying the full transaction when the operation remains valid.

InnoDB deadlock detection is enabled by default, and a deadlock rolls back the entire transaction. A lock-wait timeout normally rolls back only the statement that timed out, unless the innodb_rollback_on_timeout setting changes that behavior. MySQL describes these outcomes in its InnoDB deadlock documentation. Keep transactions short and acquire rows in a consistent order to reduce the time locks remain held and the chance that transactions form a cycle.

For lock investigations, MySQL exposes InnoDB lock and wait details through Performance Schema, including data_locks and data_lock_waits. The InnoDB lock-information documentation explains those views. PostgreSQL exposes outstanding locks through pg_locks; the two databases’ diagnostic views do not present identical internals.

Choose locking behavior around the invariant

Start by identifying what the transaction must protect. If it must prevent another transaction from inserting into a range, InnoDB’s next-key locking under REPEATABLE READ may provide that protection through a locking range scan. Inspect the index used by the query, because the scan determines the lock footprint. In PostgreSQL, use SERIALIZABLE when the transaction must preserve serializable outcomes, and make the application retry serialization failures.

With either database, keep transactions short and lock rows in a consistent order. Test with concurrent transactions that match production access patterns. The application must handle lock waits, deadlocks, and serialization failures. Matching SQL syntax does not guarantee matching locking behavior.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com