How MySQL and PostgreSQL handle concurrency under the hood

When choosing a relational database to use for a new project, MySQL and PostgreSQL are nearly the two most popular open-source database options that come to mind. Although there isn’t much difference for developers to tell when writing SQL or using ORM, there is a slight difference in performance potentially since MySQL and Postgres have their own implementations on records locking when dealing with concurrent operations. Today we will mock a simple scenario and run some SQL queries to demonstrate how MySQL and Postgres use lock mechanisms differently to handle concurrency under the hood.

Table of contents

• Database table and data preparement
• Running same queries on different databases
• Locking behavior in MySQL
• Locking behavior in PostgreSQL
• Conclusion

Database table and data preparement

The version of the databases we are going to use are MySQL 8.0 and PostgreSQL 14. Simply run the following command to set up the table and mock data in both databases.

MySQL 8.0

CREATE TABLE `order_record` (
  `order_id` VARCHAR(36) NOT NULL,
  `account_id` VARCHAR(32) NOT NULL,
  `order_money` INT NOT NULL,
  `status` INT NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  KEY `order_record_idx1` (`account_id`)
) ENGINE=InnoDB;

INSERT INTO `order_record`
  (`order_id`, `account_id`, `order_money`, `status`, `create_time`)
VALUES
  ('976d40af-e4ad-4654-99a7-cbe1772e1771', '4189019448', 300, 1, '2022-07-29 07:18:20'),
  ('976d40b7-d9b9-4123-9ef1-3e153fda352d', '4423628756', 1000, 0, '2022-09-05 11:21:04'),
  ('976d40bc-20d7-4788-9e0b-a01e51628183', '4423628756', 1000, 0, '2022-09-05 11:21:04'),
  ('976d40c0-f3b7-4909-84d0-76c71151e0f2', '4420236455', 40, 0, '2022-09-08 21:19:07'),
  ('976d40c5-8c70-4303-8653-ac7775ce565f', '4420236455', 799, 0, '2022-09-09 06:39:30'),
  ('976d40ca-87e0-4a92-998f-cc6e681cb247', '4420236455', 701, 0, '2022-09-09 11:17:02');

PostgreSQL 14

CREATE TABLE order_record (
  order_id uuid NOT NULL,
  account_id varchar(32) NOT NULL,
  order_money int4 NOT NULL,
  status int4 NOT NULL,
  create_time timestamp NOT NULL DEFAULT now(),
  CONSTRAINT order_record_pk PRIMARY KEY (order_id)
);
CREATE INDEX order_record_idx1 ON order_record USING btree (account_id);

INSERT INTO order_record
  (order_id, account_id, order_money, status, create_time)
VALUES
  ('976d40af-e4ad-4654-99a7-cbe1772e1771'::uuid, '4189019448', 300, 1, '2022-07-29 07:18:20.266'),
  ('976d40b7-d9b9-4123-9ef1-3e153fda352d'::uuid, '4423628756', 1000, 0, '2022-09-05 11:21:03.666'),
  ('976d40bc-20d7-4788-9e0b-a01e51628183'::uuid, '4423628756', 1000, 0, '2022-09-05 11:21:04.391'),
  ('976d40c0-f3b7-4909-84d0-76c71151e0f2'::uuid, '4420236455', 40, 0, '2022-09-08 21:19:07.075'),
  ('976d40c5-8c70-4303-8653-ac7775ce565f'::uuid, '4420236455', 799, 0, '2022-09-09 06:39:29.638'),
  ('976d40ca-87e0-4a92-998f-cc6e681cb247'::uuid, '4420236455', 701, 0, '2022-09-09 11:17:01.958');

Running same queries on different databases

Ok, now let’s jump into the scenario. Connection 1 wants to update the status field from 0 to 1 for all the records of account_id = '4423628756' in the order records table. Before it updates the field, connection 1 needs to retrieve the records first and do some operations in the code. To avoid the records being modified by others, connection 1 also adds the FOR UPDATE syntax at the end of the query to obtain the exclusive lock on these records. Meanwhile, there comes connection 2 which intends to insert a new record for account_id = '4423628756'. Let’s see how both databases react to this scenario.

MySQL 8.0

Conn 1	Conn 2 (general auto-commit mode)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE; > 2 row(s) fetched.
	INSERT INTO order_record   (order_id, account_id, order_money, status) VALUES   (‘976d47fc-94e4-494b-a863-8c9bf1d4e7cc’, ‘4423628756’, 1000, 0); > (blocking…)
UPDATE order_record SET status = 1 WHERE account_id = ‘4423628756’ AND status = 0; > 2 row(s) modified.	> (still blocking…)
SELECT * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE; > 2 row(s) fetched.	> (still another blocking…)
COMMIT;	> 1 row(s) modified.

As we can see, MySQL blocks the insertion until the update is complete. We will explain the reason for this later. For now, let’s run the same operations in Postgres.

PostgreSQL 14

Conn 1	Conn 2 (general auto-commit mode)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE; > 2 row(s) fetched.
	INSERT INTO order_record   (order_id, account_id, order_money, status) VALUES   (‘976d47fc-94e4-494b-a863-8c9bf1d4e7cc’, ‘4423628756’, 1000, 0); > 1 row(s) modified.
UPDATE order_record SET status = 1 WHERE account_id = ‘4423628756’ AND status = 0; > 2 row(s) modified.
SELECT * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE; > 2 row(s) fetched.
COMMIT;

Surprisingly, Postgres does not block the insertion as MySQL behaves, even though both MySQL and Postgres give us the same results as expected. If it is about to compete for concurrency performance, then Postgres should outperform MySQL in this case. Let’s dive deeper into how both databases handle the queries internally.

Locking behavior in MySQL

When we add the FOR UPDATE keyword in the select syntax in MySQL, MySQL internally applies a next-key lock (a lock combines record lock and gap lock) on index order_record_idx1 with all rows that match account_id = '4423628756' and the gap before/after the first and last rows to avoid any record changes. Therefore, when connection 2 intends to insert a new row with account_id = '4423628756', it has to wait for the lock to be released first.

We can confirm this by inspecting the acquired locks from performance_schema.data_locks system table.

Conn 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE; > 2 row(s) fetched.
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks;

INDEX_NAME       |LOCK_TYPE|LOCK_MODE    |LOCK_DATA                                           |
-----------------+---------+-------------+----------------------------------------------------+
                 |TABLE    |IX           |                                                    |
order_record_idx1|RECORD   |X            |supremum pseudo-record                              |
order_record_idx1|RECORD   |X            |'4423628756', '976d40b7-d9b9-4123-9ef1-3e153fda352d'|
order_record_idx1|RECORD   |X            |'4423628756', '976d40bc-20d7-4788-9e0b-a01e51628183'|
PRIMARY          |RECORD   |X,REC_NOT_GAP|'976d40b7-d9b9-4123-9ef1-3e153fda352d'              |
PRIMARY          |RECORD   |X,REC_NOT_GAP|'976d40bc-20d7-4788-9e0b-a01e51628183'              |

Below is the visualized interpretation of the output. Lock with IX mode is placed on the table scope to tell others that there is currently a transaction intending to obtain exclusive lock on some rows in the table. Locks with X,REC_NOT_GAP mode are placed directly on the record to prevent the record from being modified. And locks with X mode is the next-key lock that prevents the gap before the record from being modified. The 5 locks cover the entire range of account_id = '4423628756', making no other connection can alter data within it.

Lock Mode:
IX: Intention Exclusive Lock
X: Exclusive Next-Key Lock
X,REC_NOT_GAP: Exclusive Record Lock

Now, we have revealed the locking mechanisms in MySQL. But why’s that next-key lock needed? Couldn’t we lock only the rows without the gap instead? The purpose of MySQL having the next-key lock mechanism under the hood is to stick to the standard of the repeatable-read isolation level, which should ensure that all read queries are repeatable. I.e., it always returns the same result every time, even if there are changes made by other committed transactions. Think about it, if MySQL allows the insertion without blocking, the last select statement from connection 1 would have seen the record inserted by connection 2, which is unacceptable for the repeatable-read isolation level. Thus, we can understand the motivation why the usage of the next-key lock is necessary.

In the next part, let’s see how Postgres achieves repeatable-read without blocking.

Locking behavior in PostgreSQL

Postgres, on the other hand, doesn’t have any gap lock or next-key lock mechanism behind it. It introduces a different design in which every record in the database table is an imutable tuple, with every tuple coming with a hidden field called ctid showing its physical location in the table. Therefore, if anyone updates an existing record (tuple), what Postgres does is insert a new record (tuple) and mark the old record (tuple) as deleted. This results in every change to the existing data will produce a new record (tuple) and having a new ctid.

Another design for Postgres is that it creates a hidden field called xmin for all rows in all the tables. The xmin field is the transaction id in which the transaction creates this record (tuple). With these two designs, we will see how Postgres achieves repeatable-read without locking the gap and blocking insertions from other transactions as MySQL does. Let’s uncover the usage of ctid and xmin first.

Conn 1	Conn 2
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT TXID_CURRENT(); > 1071
SELECT ctid, xmin, * FROM order_record WHERE account_id = ‘4423628756’ FOR UPDATE;

ctid |xmin|order_id                            |account_id|order_money|status|create_time            |
-----+----+------------------------------------+----------+-----------+------+-----------------------+
(0,2)|1049|976d40b7-d9b9-4123-9ef1-3e153fda352d|4423628756|       1000|     0|2022-09-05 11:21:03.666|
(0,3)|1049|976d40bc-20d7-4788-9e0b-a01e51628183|4423628756|       1000|     0|2022-09-05 11:21:04.391|

The SELECT TXID_CURRENT(); statement allows us to find out the current transaction id for a connection, right now is 1071 for connection 1. Next, in the third query, we also add two additional fields ctid and xmin to show their physical location in the table and which transaction they were created from. We can see the records (tuples) are in the 2nd and 3rd position in the table, created from transaction id 1049. The FOR UPDATE syntax in Postgres doesn’t have any magical effect, which locks the exact rows we see above, merely.

For now, let’s jump to connection 2 and insert a new record (tuple) with returning statement to see its result immediately.

Conn 1	Conn 2
	BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	SELECT TXID_CURRENT(); > 1072
	INSERT INTO order_record   (order_id, account_id, order_money, status) VALUES   (‘976d47fc-94e4-494b-a863-8c9bf1d4e7cc’, ‘4423628756’, 1000, 0) RETURNING ctid, xmin, *;

ctid |xmin|order_id                            |account_id|order_money|status|create_time            |
-----+----+------------------------------------+----------+-----------+------+-----------------------+
(0,7)|1072|976d47fc-94e4-494b-a863-8c9bf1d4e7cc|4423628756|       1000|     0|2022-9-11 01:26:42.219|

Because the transaction from connection 2 happens after connection 1’s transaction, we can see that the transaction id and xmin are both greater than connection 1. This is the trick that Postgres plays with repeatable-read. From connection 1’s point of view, it will disregard any records (tuples) with xmin greater than its transaction id (1071) when performing queries, meaning those records (tuples) are invisible because they are created after the transaction starts. In our case, since the inserted record has xmin = 1072, connection 1 will never retrieve it and send it to the user.

Let’s verify this by updating the status field from 0 to 1 for all the records of account_id = '4423628756' from connection 1.

Conn 1	Conn 2
UPDATE order_record SET status = 1 WHERE account_id = ‘4423628756’ AND status = 0; RETURNING ctid, xmin, *;

ctid |xmin|order_id                            |account_id|order_money|status|create_time            |
-----+----+------------------------------------+----------+-----------+------+-----------------------+
(0,8)|1071|976d40b7-d9b9-4123-9ef1-3e153fda352d|4423628756|       1000|     1|2022-09-05 11:21:03.666|
(0,9)|1071|976d40bc-20d7-4788-9e0b-a01e51628183|4423628756|       1000|     1|2022-09-05 11:21:04.391|

As we can see, only the two previous records (tuples) with xmin = 1049 are updated and returned. And they have their new ctids (0,8) and (0,9) respectively, which proves that they are indeed being recreated and inserted into the table. Also, their xmins has changed to the current transaction id 1071 too. The visualized interpretation is shown below.

So far, we have gone through how Postgres takes advantage of its internal data structure design to achieve both repeatable-read and without blocking insertion from others. While in the previous section MySQL has to place locks across the range due to its in-place update in comparison.

Conclusion

We have built a basic grasp of how MySQL and Postgres use lock mechanisms differently to handle concurrency due to their designs.

In fact, this is the real scenario that I encountered recently when I was supporting another project based on Postgres. I used to think in the MySQL way because of my previous experiences, thus, when the first time I came across the phenomenon in the project, it really catches my interest. And finally, I decided to take this scenario as a material to explain the mechanisms behind MySQL and Postgres while trying not to flood too many trivial details to overcomplicate it.

Lastly, using only xmin to decide the visibility scope for repeatable-read in PostgreSQL is oversimplified. There is a specific terminology called MVCC (multi-version concurrency control) regarding the repeatable-read behavior. You can look up PostgreSQL’s official documents for MVCC to see more details about how the visibility is decided.

That’s all for today’s article. I hope it’s helpful for you. Thanks for reading!

References

• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• https://dev.mysql.com/doc/refman/5.6/en/information-schema-innodb-locks-table.html
• https://www.postgresql.org/docs/14/ddl-system-columns.html
• https://postgrespro.com/blog/pgsql/5967899
• https://dev.to/techschoolguru/understand-isolation-levels-read-phenomena-in-mysql-postgres-c2e#repeatable-read-isolation-level-in-mysql