MySQL Bugs: #116628: Two concurent SERIALIZABLE transactions are not isolated properly.

Bug #116628	Two concurent SERIALIZABLE transactions are not isolated properly.
Submitted:	12 Nov 2024 10:43	Modified:	15 Nov 2024 10:23
Reporter:	Theodor Moroianu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	9.1.0	OS:	Linux (Official container from dockerhub)
Assigned to:		CPU Architecture:	x86 (AMD Ryzen 7 4800U)
Tags:	Isolation level, SERIALIZABLE, transactions

Description:
When running concurent transactions under the SERIALIZABLE isolation level, updates committed by one transaction become visible to the other transaction when committed.

The following PoC better illustrates the bug:

-- Set the isolation level and start transactions.
conn_0> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn_0> START TRANSACTION;

conn_1> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn_1> START TRANSACTION;

-- Make a query from the connection 1 to force a snapshot of the DB.
conn_1> SELECT * FROM table_1;

-- Check what the answer to this query should be before any updates (1).
conn_0> select * from table_2 where 77 <> table_2.field_1;

-- Update the table `table_2`.
conn_0> update table_2 set 
  wkey = 31, 
  field_3 = table_2.field_2, 
  field_4 = (select min(wkey) from table_1)
where 77 <> table_2.field_1;

-- Check what the answer to this query is after the update (2).
conn_0> select * from table_2 where 77 <> table_2.field_1;

conn_0> COMMIT;

-- Check what the answer is this query is. This should be equal to (1) 
-- as the changes were committed after this serializable transaction started,
-- but the result of this query is actually (2).
conn_1> select * from table_2 where 77 <> table_2.field_1;

How to repeat:

1. Setup the database:
CREATE TABLE `table_1` (
  `wkey` int DEFAULT NULL,
  `pkey` int NOT NULL,
  `c_hvpitb` int DEFAULT NULL,
  `c_6u8vjd` double DEFAULT NULL,
  `c_kkwenb` text,
  `c_zvsgqc` double DEFAULT NULL,
  `c_pqm_t` text,
  `c_gq8kac` int DEFAULT NULL,
  PRIMARY KEY (`pkey`),
  KEY `t_uzwrt` (`pkey`,`c_6u8vjd`,`c_zvsgqc`,`c_gq8kac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `table_1` VALUES (2,17000,56,50.2,'sqvlqc',NULL,'vi98j',69);

CREATE TABLE `table_2` (
  `wkey` int DEFAULT NULL,
  `pkey` int NOT NULL,
  `field_3` double DEFAULT NULL,
  `field_2` double DEFAULT NULL,
  `field_1` int DEFAULT NULL,
  `c_wpjupd` double DEFAULT NULL,
  `field_4` int DEFAULT NULL,
  `c_lj0sic` double DEFAULT NULL,
  PRIMARY KEY (`pkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `table_2` VALUES (1,11000,NULL,29.31,100,48.33,NULL,88.61);

2. Run the instructions:
conn_0> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn_0> START TRANSACTION;

conn_1> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn_1> START TRANSACTION;

-- Make a query from the connection 1 to force a snapshot of the DB.
conn_1> SELECT * FROM table_1;

-- Check what the answer to this query should be before any updates (1).
conn_0> select * from table_2 where 77 <> table_2.field_1;

-- Update the table `table_2`.
conn_0> update table_2 set 
  wkey = 31, 
  field_3 = table_2.field_2, 
  field_4 = (select min(wkey) from table_1)
where 77 <> table_2.field_1;

-- Check what the answer to this query is after the update (2).
conn_0> select * from table_2 where 77 <> table_2.field_1;

conn_0> COMMIT;

-- Check what the answer is this query is. This should be equal to (1) 
-- as the changes were committed after this serializable transaction started,
-- but the result of this query is actually (2).
conn_1> select * from table_2 where 77 <> table_2.field_1;

A full log of the execution on my machine is the following:
 * Instruction #0:
     - Instruction:  SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
     - Transaction: conn_0
     - Output: None
     - Executed order: 0
     - Affected rows: 0
 * Instruction #1:
     - Instruction:  START TRANSACTION;
     - Transaction: conn_0
     - Output: None
     - Executed order: 1
     - Affected rows: 0
 * Instruction #2:
     - Instruction:  SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
     - Transaction: conn_1
     - Output: None
     - Executed order: 2
     - Affected rows: 0
 * Instruction #3:
     - Instruction:  START TRANSACTION;
     - Transaction: conn_1
     - Output: None
     - Executed order: 3
     - Affected rows: 0
 * Instruction #4:
     - Instruction:  SELECT * FROM table_1;
     - Transaction: conn_1
     - Output: [(2, 17000, 56, 50.2, 'sqvlqc', None, 'vi98j', 69)]
     - Executed order: 4
     - Affected rows: 1
 * Instruction #5:
     - Instruction:  select * from table_2 where 77 <> table_2.field_1;
     - Transaction: conn_0
     - Output: [(1, 11000, None, 29.31, 100, 48.33, None, 88.61)]
     - Executed order: 5
     - Affected rows: 1
 * Instruction #6:
     - Instruction:  update table_2 set wkey = 31, field_3 = table_2.field_2, field_4 = (select min(...
     - Transaction: conn_0
     - Output: None
     - Executed order: 6
     - Affected rows: 1
 * Instruction #7:
     - Instruction:  select * from table_2 where 77 <> table_2.field_1;
     - Transaction: conn_0
     - Output: [(31, 11000, 29.31, 29.31, 100, 48.33, 2, 88.61)]
     - Executed order: 7
     - Affected rows: 1
 * Instruction #8:
     - Instruction:  COMMIT;
     - Transaction: conn_0
     - Output: None
     - Executed order: 8
     - Affected rows: 0
 * Instruction #9:
     - Instruction:  select * from table_2 where 77 <> table_2.field_1;
     - Transaction: conn_1
     - Output: [(31, 11000, 29.31, 29.31, 100, 48.33, 2, 88.61)]
     - Executed order: 9
     - Affected rows: 1
     - Common output with previous instructions: [7]

Hi Mr. Moroianu,

Thank you for your bug report.

However, it is not a bug.

Other transactions are waiting for the locks that first transaction in  SERIALIZABLE mode is holding, but once first transaction commits, all locks are deallocated and the other transactions do not have anything to wait for, hence they can perform the changes on the table(s).

SERIALIZABLE is on of the modes in concurrency control. But, when one transactions remains alone , then you do not have any concurrency .......

Not a bug.

Thank you for your answer! However I think this is indeed a bug.

The problem I am reporting is not a locking issue, but an isolation bug.

The second transaction (conn_1) starts before the first one (conn_0) commits, hence it should not see the changes conn_0 makes.

The PoC is essentially:
1. Transaction 1 and 2 start.
2. Transaction 1 makes some changes, commits.
3. Transaction 2 sees the changes made by transaction 1, violating SI.

As you said, when the second transaction remains alone there is no concurrency, but the MVCC should ensure that the database view of the transaction is consistent. The second transaction should never see any changes made by the first one (even when committed), as this would violate the SERIALIZABLE isolation level.

Hi Mr. Moroianu,

We understand what you are writing about, but this is not a bug.

However, what you describe is how another isolation level works and not how SERIALIZABLE works.

This is SERIALIZABLE concurrency control, this is not REPEATABLE READ concurrency control.

Only REPEATABLE READ does what you are describing. It is explained fully in SQL Standard and in the classical books on transaction processing, like this one:

https://www.amazon.com/Transaction-Processing-Concepts-Techniques-Management/dp/1558601902

Not a bug ......

Hi Mr. Moroianu,

Let us help you a bit more, by quoting from the SQL Standard itself:

"
A serializable execution is defined to be an execution of the operations of concurrently executing
SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions
"
Here, "serial execution" means that transactions are executed one after another.....

It is that simple .......

Not a bug.

Thank you for your explanation!

However, I still believe this is a bug. From the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html) on Repeatable Read:
Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

This constraint is broken, as the transaction 1 makes a snapshot before transaction 1 updates the table, but then sees those modifications.

The bug does indeed occur under SERIALIZABLE, not REPEATABLE READ, but again the documentation states that SERIALIZABLE is more strict, so all constraints should be respected:
This level (SERIALIZABLE) is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled.

Hi Mr. Moroianu,

No, there is no bug here.

SERIALIZABLE means that result of concurrent transactions is the same as if each transaction is executed one AFTER the other. That is, first one is started and finished and second one is then started. 

Regardless of the fact that those were concurrent transactions. These are definitions of this MVCC variant.

Hence, finally, no this is not a bug.