This post illustrates testing MySQL’s (version 8.0.30 was used in the tests) SERIALIZABLE with the 3 examples given on Postgres wiki.

Black and White

MySQL handles this correctly. Below is the sequence of events:

• I ran the first update and it returned with

Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

• When I ran the update on the second transaction it got blocked (i.e., has to wait)
• Then I ran commit on the first tx. This unblocked the second transaction with following message:

Query OK, 20 rows affected (4.50 sec)
Rows matched: 20  Changed: 20  Warnings: 0

• and then I was able to commit the second transaction as well

In summary, the second transaction is blocked (has to wait for the first transaction to commit). After that the second transaction is also allowed to commit ending in a serializable state. In case of Postgres one of the transactions will be aborted and will have to be re-tried.

This shows the second tx as blocked:

and this shows both txs are serialized resulting in all dots being of the same color in the end:

so kudos to MySQL on this one. No need to retry any transaction!

Intersecting Data

MySQL was able to handle this case as well. The actual sequence of events was like this:

• the first tx was blocked when I executed the insert statement
• when I executed the insert on the other transaction, I got the deadlock error and the second transaction was aborted by MySQL. Good Job!

Overdraft Protection

MySQL was able to handle this case as well and I saw same behavior as with Intersecting Data.

• the first tx was blocked when I executed the update statement
• when I executed the update on the other transaction, I got the deadlock error and the second transaction was aborted by MySQL. Good Job!