Concurrency and Locking Limits in MySQL and PostgreSQL

When a database handles many requests at once, connection count alone does not show how much work it can do. A connection is a client’s link to the database; a transaction is a group of database operations that succeeds or fails as one unit. Concurrency describes how much work can proceed at once. Locking describes how the database makes transactions wait when they try to change or lock the same data.

Neither PostgreSQL nor MySQL has one universal concurrency number that predicts performance under every workload. The practical limit depends on how many transactions compete for the same data and how long they hold locks. The server’s available resources also set a limit. PostgreSQL documents no fixed maximum number of rows that can be locked at once, but locking rows can write to disk. For MySQL, the details below apply to the InnoDB storage engine. Its index-based locks can cover a range as well as individual records.

To compare the databases, distinguish the number of connections from the transactions competing for the same rows or index ranges. Then measure how that competition affects throughput, wait times, and retries.

What each database locks

PostgreSQL uses multiversion concurrency control, or MVCC. It keeps older versions of rows so a reader can see a consistent view of the data while other transactions make changes. A row lock makes another transaction wait if it tries to change or lock that same row. Ordinary reads do not wait for row locks. Routine inserts, updates, and deletes also take locks on the table, but those locks do not block ordinary reads. Commands such as TRUNCATE and some schema changes take the stronger ACCESS EXCLUSIVE lock, which blocks ordinary SELECT statements. The PostgreSQL locking documentation describes these lock modes and their effects.

InnoDB, MySQL’s default storage engine, attaches row locks to index records. Under its default REPEATABLE READ isolation level, an index search or scan can take a next-key lock, which covers an index entry and the gap before it. The lock can block an insert into that gap before the new row exists, so the index used by a locking query affects which other transactions must wait.

A search using all the values in a unique key, which identifies exactly one row, does not need a gap lock. A search that uses only some of the columns in a unique index made from multiple columns can lock a wider range. The same can happen when the search has no index suited to its condition. The InnoDB locking documentation explains how index access shapes this lock footprint.

An isolation level sets which data changes a transaction can see while it runs and how it interacts with concurrent transactions. These settings affect locking behavior. PostgreSQL defaults to READ COMMITTED, while InnoDB defaults to REPEATABLE READ. In MySQL, READ COMMITTED disables gap locks for searches and index scans, with exceptions for foreign-key and duplicate-key checks. The same isolation-level name can produce different transaction behavior in PostgreSQL and InnoDB. Check the PostgreSQL transaction settings and InnoDB’s documented locking rules when designing a comparison.

Why lock counts do not predict throughput

A database that has many open connections does not necessarily perform more work at once. When hundreds of transactions update different rows, they can make progress independently until the server runs short of CPU, storage, or memory, or cannot handle more connections. If they all update one popular row, they must take turns. Adding connections to that hot row adds waiting rather than parallel progress.

The same distinction applies to lock counts. PostgreSQL’s lack of a documented fixed row-lock count is not a promise of unlimited capacity. Locking has costs, including disk writes and time spent waiting. The cited InnoDB documentation explains lock behavior but does not establish a comparable universal maximum number of row locks. Do not infer a numeric limit from that gap.

The index and the query condition matter especially when a transaction locks a range of index entries. For example, an InnoDB transaction that scans an indexed range under REPEATABLE READ can block inserts into index gaps. An index that matches fewer rows can change which transactions wait. A query condition that covers a smaller range can do the same, as can a different isolation level. Test the actual query and the plan the database uses to run it. Different updates and reads can lock different data.

What the Reddit benchmark shows

A benchmark discussed on r/programming reported PostgreSQL ahead in 14 of 17 tests, with one draw and two select cases favoring MySQL. For single-row inserts, the post reported 21,338 queries per second for PostgreSQL and 4,383 for MySQL. The figures describe results under that benchmark’s setup; they do not establish general database limits.

The author reported testing MySQL 9.5 and PostgreSQL 18.1 with eight CPUs and 16 GB of memory allocated to each container. The MySQL test used a connection pool of 128, compared with 64 for PostgreSQL, and used READ COMMITTED for MySQL to match PostgreSQL’s default isolation level. The author also reported that more connections helped MySQL in that test. These conditions show why a pool size cannot stand in for a locking limit.

The benchmark recorded operation counts and completion times. It did not isolate hot-row contention, lock-wait time, deadlocks, or InnoDB gap-lock effects, so it cannot establish which database handles lock contention better. Its results can still inform a broad performance discussion. For a useful comparison, test independent-row writes and writes to a deliberately hot row. Also test range-based locking reads and mixed readers and writers. Keep the schema, data distribution, isolation level, and client driver explicit. Keep pool settings, hardware limits, and durability settings explicit too. Track response times at different percentiles and count retries alongside throughput.

Find the blocker before changing pool settings

When a transaction waits, identify the transaction blocking it before increasing connection counts or changing database settings. PostgreSQL’s pg_locks view shows lock information, but row-level lock state is normally stored on disk rather than represented as one in-memory entry for every locked row. A process waiting for a row lock commonly appears as waiting for the blocking transaction’s ID. As a result, a snapshot may not show each locked row directly. The PostgreSQL pg_locks documentation explains how to interpret that view.

MySQL 8.4 exposes held and requested locks, along with blocker-waiter relationships, through Performance Schema tables such as data_locks and data_lock_waits. SHOW ENGINE INNODB STATUS can help inspect the most recent deadlock. A deadlock occurs when transactions wait on one another in a cycle. InnoDB detects deadlocks by default and rolls back a victim transaction; the client still needs to handle the failed transaction. Keeping write transactions short and accessing rows in a consistent order reduces the conditions that produce these cycles, as described in the InnoDB deadlock guidance.

Clients should also retry transactions that fail with a serialization error. PostgreSQL can abort a transaction at SERIALIZABLE isolation when concurrent work would produce a result inconsistent with serial execution. That failure is distinct from a deadlock, so handle and measure the two separately.

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