How PostgreSQL and MySQL Lock Transactions

When two database transactions try to change some of the same data, the database uses locks to let one proceed while the other waits. PostgreSQL and MySQL’s InnoDB storage engine both use multiversion concurrency control (MVCC), which keeps committed versions of rows so ordinary reads can often continue while a transaction changes a row. Both databases also lock rows when transactions change them or explicitly request a lock.

The main practical difference is how far some locks reach. PostgreSQL row locks apply to particular rows. InnoDB locks entries in indexes and, under some conditions, the gaps between those entries. As a result, a range search can block inserts for values the search did not return. The sections below explain how reads, isolation settings, and indexes affect contention and how to reduce avoidable waits.

Ordinary reads usually do not lock rows

In both databases, a regular SELECT generally reads a committed version without locking the row against later changes. If a transaction reads a value and then makes a decision based on it, that read alone does not stop another transaction from changing the value first.

When a transaction must read a row and then act on it, the transaction can use a locking read such as SELECT ... FOR UPDATE. The database makes competing transactions wait if they request a conflicting lock or try to change the locked row. PostgreSQL provides four row-lock modes, including FOR UPDATE and FOR SHARE; each blocks a different set of competing actions. In InnoDB, FOR UPDATE and FOR SHARE are locking reads. Use these reads when a transaction must make a decision based on a value and then change that same row. The lock lasts until the transaction ends, so it can delay other transactions. See the PostgreSQL row-lock modes and InnoDB locking reads.

A locking read protects the rows it locks, but it does not automatically protect every decision based on earlier reads. For example, if a transaction checks a balance and then updates it, the transaction needs a locking or other concurrency strategy that protects the check and update together.

InnoDB locks depend on indexes and search ranges

InnoDB locks index records, which are entries in an index. Under the default REPEATABLE READ isolation level, a locking search can also use a next-key lock. This lock covers an index record and the gap just before it in index order. The gap lock prevents another transaction from inserting a value into that part of the index. The locks a search takes depend on the index and search the database uses, as well as the transaction’s isolation level. The InnoDB locking documentation describes these record and gap locks.

When a transaction searches a unique index for one specific row, it can lock just the matching index record; the index allows only one row for each indexed value. A range search or a search through a nonunique index, which allows multiple rows to have the same indexed value, can cover more of the index. As a result, a locking query can block an insert even when the new row would not appear in the query results.

Indexes affect both query speed and how transactions interfere with each other. If a locking search touches more index records than the transaction needs, it holds more locks and makes other transactions wait. Check which index the query uses when a range search blocks inserts or unrelated transactions wait on locks. InnoDB disables gap locking for searches and index scans at READ COMMITTED, with exceptions for foreign-key and duplicate-key checks.

PostgreSQL also uses locks beyond individual rows, including table-level locks. Its row locks block conflicting writes and lock requests on the same rows, while ordinary data queries continue to read. The two databases therefore differ in how particular operations cover rows and ranges, not in a simple division where one locks rows and the other locks tables.

Isolation defaults change what transactions see

An isolation level controls which changes made by other transactions a transaction can see. PostgreSQL defaults to READ COMMITTED; InnoDB defaults to REPEATABLE READ. The names do not explain every locking detail, so check the isolation setting and query type when investigating differences between the databases. PostgreSQL lists its default connection settings, and MySQL describes the InnoDB transaction model.

At PostgreSQL’s READ COMMITTED level, each statement sees rows that were committed before the statement began. At REPEATABLE READ, all statements in a transaction use the same snapshot, set by the transaction’s first query or data-modification statement. PostgreSQL’s REPEATABLE READ also prevents phantom reads: a repeated search cannot return additional matching rows because another transaction inserted them.

InnoDB’s default REPEATABLE READ gives ordinary reads a consistent view of the data, while locking searches can use next-key locks to protect index ranges. Two queries in the same InnoDB transaction can therefore lock differently. An ordinary read uses that view without locking rows, while a locking read can block changes to index records or gaps.

Deadlocks require transaction-level recovery

A deadlock occurs when transactions wait for one another in a cycle. For example, one transaction locks row A and waits for row B, while another locks row B and waits for row A. PostgreSQL detects deadlocks and aborts one transaction. By default, InnoDB detects deadlocks and rolls back one transaction to break the cycle. The PostgreSQL locking guidance recommends acquiring locks in a consistent order.

To reduce deadlocks, have transactions lock rows in the same order and keep each transaction short. If a deadlock or serialization failure aborts a transaction, retry the entire unit of work when the application’s logic allows it. Retrying one statement inside an aborted transaction does not finish the original work.

Serializable isolation may abort work instead of waiting

SERIALIZABLE isolation makes concurrent transactions behave as if they had run one at a time. At this level, PostgreSQL tracks dependencies between transactions and aborts a transaction with a serialization failure when it cannot safely order their concurrent actions. Applications using this level must handle these failures by retrying the transaction. PostgreSQL describes the available transaction isolation settings.

Applications still need to handle errors at stricter isolation levels. Before changing isolation settings, determine whether the application needs the same view across multiple statements, protection from a particular concurrent write, or both. Then test how the transaction behaves when other transactions compete for the same data.

Choose locking behavior for the work

For ordinary reads, let the database read committed versions without a locking clause. When a transaction reads and then changes data, lock the rows it will change, keep the transaction short, and update rows in a consistent order. For InnoDB range searches, check the index and isolation level because they determine whether locks cover gaps.

MySQL supports NOWAIT, which fails immediately when a requested lock is unavailable, and SKIP LOCKED, which omits locked rows from the result. SKIP LOCKED suits work queues where a worker should take another available task. It returns an inconsistent view, so it is unsuitable for general-purpose transactional reads. The application must account for tasks another transaction held when the query ran.

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