MySQL Bugs: #93986: Transactions in serializable mode are not actually serializable

Bug #93986	Transactions in serializable mode are not actually serializable
Submitted:	20 Jan 2019 10:23	Modified:	22 Feb 2019 13:12
Reporter:	Dyllon Gagnier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
Even when transaction isolation level is set to serializable, it is quite easy to end up with a non-serializable sequence of execution due to auto incrementing IDs. This is a fairly serious issue since it breaks important serialization guarantees necessary for the common pattern of inserting a new row then selecting the id of that row by finding the max id.

How to repeat:
I will give the SQL necessary to replicate one of the problematic scenarios in the form "TRANSACTION: sql_statements_run".

The following SQL relies upon a table named mytable with a single column id which is an autoincrementing primary key integer.

T1: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T2: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T1: BEGIN TRANSACTION; INSERT INTO mytable() VALUES ();
T2: BEGIN TRANSACTION; INSERT INTO mytable() VALUES (); COMMIT;
T1: SELECT MAX(id) FROM mytable; COMMIT;

T1 ends up selecting the ID of the row just inserted by T2 instead of the row it inserted. This violates serializability since there is no way for T1 to insert a row with an id before T2's id and then select out T2's id except by them interleaving.

Suggested fix:
There is an obvious workaround by using the LAST_INSERT_ID() function, but this is still a pretty common pattern used in the wild and probably affects numerous ORMs and libraries.

I think that the main issue comes from the way MySQL does range locks. One possible fix could be to keep a pseudo-record for tables with autoincrement for the purpose of locking the end of the table. Another fix would be to make the locking more aggressive by forcing an insert with an autoincrement column to acquire a shared or exclusive lock on the entire table.

I tested equivalent SQL with PostgreSQL and it seems to do some form of MVCC in this case since it allows T2 to finish, but the select done by T1 selects the id that T1 inserted and not the id inserted by T2.

Hi,

Thank you for your bug report.

If you read our Reference Manual, you will find out that SERIALIZABLE is the same as REPEATABLE READ, except that all SELECTs are transformed into a SELECT ..... IN SHARED MODE. 

That is the only difference.

Another point that must be made is that auto-increment column type is not a standard SQL data type, like SEQUENCE.  It is an extension in MySQL and that value is held table-wide and not transaction-wide. Hence, it will not be visible only in its transaction, when not committed yet. This is because its value is reserved immediately, before transaction is committed. This is something MySQL-specific and not within SQL standards.

This is all explained in our Reference Manual, but if you think that additional explanation, do let us know what additional documentation would you require.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".