Transaction and Locking Differences Between PostgreSQL and MySQL

PostgreSQL and MySQL with InnoDB both support ACID transactions: transactions that preserve data integrity while grouping changes so they commit together or roll back together. They differ in how concurrent transactions see changes, which records or index ranges they lock, and whether a conflict makes a transaction wait or fail. Those differences affect production behavior, including query results, write delays, and retry handling.

Both databases use multiversion concurrency control (MVCC), which lets a query read a version of a row without always waiting for another transaction to finish changing it. They also use locks to coordinate conflicting work. The details below follow the PostgreSQL 18 and MySQL 8.4 documentation; MySQL behavior here refers to its InnoDB storage engine, since other engines can behave differently. PostgreSQL’s MVCC overview and InnoDB’s transaction model describe these foundations.

Default isolation changes what reads see

An isolation level sets which changes made by other active transactions a transaction can see and which results the database allows. PostgreSQL defaults to READ COMMITTED, while InnoDB defaults to REPEATABLE READ. The same sequence of SQL statements can therefore return different results on the two databases. PostgreSQL’s isolation documentation and MySQL’s isolation documentation describe these defaults.

At PostgreSQL’s default, each statement sees data committed before that statement began. Two ordinary SELECT statements in one transaction can return different results if another transaction commits between them. If an UPDATE encounters a row another transaction has changed, PostgreSQL waits for that transaction to finish, then checks whether the row still matches the UPDATE statement’s WHERE condition. This works well for many straightforward updates. But if a business rule depends on several reads staying consistent, the application may need to coordinate transactions explicitly or use a stronger isolation level.

At InnoDB’s default, ordinary reads within a transaction use a consistent snapshot established by the transaction’s first consistent read. A later plain SELECT can therefore keep showing the earlier view after another transaction commits. Locking reads and data-changing statements work with current rows and use locks, so they can see a different view from ordinary snapshot reads in the same transaction. MySQL cautions against mixing these read types without accounting for that difference.

InnoDB locks index ranges; PostgreSQL adds explicit locks to MVCC

PostgreSQL takes table-level locks to coordinate statements, but ordinary read and write lock modes are compatible, so a plain read generally does not block a concurrent row update and the update does not block the read. A separate row-level lock can make another transaction wait when it tries to update or lock the same row. In PostgreSQL, lock names such as ROW EXCLUSIVE describe table-level lock modes, despite the word “ROW.” PostgreSQL’s explicit-locking documentation explains the distinction.

InnoDB also performs ordinary reads without locking the rows they read. But InnoDB can lock index records when a transaction runs UPDATE or DELETE. Locking reads can do the same, and some searches also lock the gaps between records. A next-key lock covers an index record and the gap before it. An insert into that gap then waits, even if the search found no existing row with the inserted value. A lookup using a unique index to find one exact row takes a narrower lock.

The index records and ranges InnoDB scans determine which concurrent operations wait. A broad scan, or a statement without a suitable index, can make InnoDB lock more records and gaps than the person who wrote the query expected. Check the execution plan for locking and write queries, then test the actual statements with concurrent inserts and updates. MySQL’s statement-locking documentation describes how locks relate to scanned index records and ranges.

REPEATABLE READ means different things

PostgreSQL’s REPEATABLE READ gives a transaction a stable snapshot and prevents phantom reads, which occur when the same query finds new rows that match its condition. This isolation level can still allow serialization anomalies: results that would not occur if the database ran each transaction one at a time. PostgreSQL applications using this isolation level must handle transactions that fail because of concurrent conflicts.

InnoDB’s REPEATABLE READ gives ordinary reads a stable snapshot as well. Its locking range searches and writes can also use gap or next-key locks to prevent conflicting inserts into scanned ranges. That behavior helps protect some range-based operations, but it also means that a query’s index access can cause inserts to wait. The isolation-level name alone does not predict the lock footprint or conflict behavior across both databases.

Serializable transactions fail in different ways

SERIALIZABLE asks the database to prevent outcomes that cannot be explained by running transactions one at a time. PostgreSQL uses Serializable Snapshot Isolation (SSI), which tracks read and write dependencies that could produce such an outcome. Its predicate locks help track those dependencies without blocking other transactions. When PostgreSQL detects a dangerous pattern, it aborts a transaction with a serialization failure, and the application must retry the whole transaction.

In InnoDB, when autocommit is disabled, SERIALIZABLE treats ordinary SELECT statements as shared locking reads. These reads take locks that can make them wait for conflicting work to finish. PostgreSQL’s SSI approach can instead allow transactions to proceed and abort one after it detects a risky dependency. The two databases therefore have different wait and failure patterns at the same isolation-level name. Compare their documented serializable behavior and InnoDB isolation behavior when choosing an isolation level for a workload.

Locking reads help workers claim rows

Both databases provide locking reads for transactions that must protect rows before changing related data. A queue worker, for example, can select an available task with a locking read, mark it as claimed, and commit before doing longer-running work. PostgreSQL offers FOR UPDATE and FOR NO KEY UPDATE. It also offers FOR SHARE and FOR KEY SHARE. InnoDB provides FOR UPDATE and FOR SHARE. These clauses coordinate with conflicting writers or lockers, unlike a plain snapshot read. PostgreSQL documents its locking clauses for SELECT.

Both databases also support NOWAIT and SKIP LOCKED for applicable locking reads. NOWAIT returns an error instead of waiting for a conflicting row lock. SKIP LOCKED lets a worker skip rows another worker has locked, which can help workers claim different queue items; because those skipped rows make the result incomplete, it fits queue-style work rather than queries that need a complete, consistent result set.

Deadlocks and serialization failures require transaction retries

A deadlock occurs when each of several transactions waits for a lock held by another, so none can continue. Transactions that request locks in different orders can deadlock. InnoDB’s range and gap locks can also contribute. InnoDB detects deadlocks by default and rolls back one transaction. Keep write transactions short and request locks in a consistent order to reduce avoidable waits. Add indexes for columns used in locking and update conditions. MySQL’s deadlock documentation recommends treating deadlocks as an expected condition that applications must handle.

PostgreSQL can also detect deadlocks and abort a transaction. Separately, its REPEATABLE READ and SERIALIZABLE levels can return serialization failures; at SERIALIZABLE, PostgreSQL can abort a transaction without a row-lock wait because it detects a risky dependency pattern. When either database rolls back a transaction, run the entire transaction again. Repeating only the statement that failed does not retry the work that the database rolled back. Keep external side effects, such as sending a message, outside the retryable transaction or make them safe to repeat.

Test the transaction sequence on each database

When maintaining code on both products, test the full sequence of reads, locking reads, writes, and commits under concurrency. Include concurrent inserts when a transaction searches a range, and verify that indexes support the predicates the transaction uses. For PostgreSQL, exercise retry handling at the isolation levels that can return serialization failures. For InnoDB, watch for waits caused by scanned index ranges and for deadlock rollbacks.

Neither database is universally more concurrent based on these mechanisms alone. PostgreSQL can avoid some read/write blocking and detect serialization conflicts through SSI, while InnoDB can protect scanned ranges with locks that also make query plans important to concurrency. The right choice depends on the workload, schema, transaction design, and isolation level; the manuals establish behavior, not a universal performance winner.

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