MySQL Bugs: #114952: Incorrect result for locking reads after INSERT of another transaction.

Bug #114952	Incorrect result for locking reads after INSERT of another transaction.
Submitted:	10 May 7:42	Modified:	10 May 10:31
Reporter:	zhuang liu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.25	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	x86

Description:
Isolation Level: READ COMMITTED
Incorrect result for locking reads after INSERT of another transaction.

How to repeat:
/* init */ CREATE TABLE t(a INT PRIMARY KEY);
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ INSERT INTO t(a) VALUES (2);
/* t2 */ SELECT a FROM t FOR UPDATE;  -- actual: [2], expected: [1, 2]
/* t1 */ INSERT INTO t(a) VALUES (1);
/* t1 */ COMMIT;
/* t2 */ COMMIT;

The SELECT FOR UPDATE statement of t2 was initially blocked by t1. It recovered after t1's COMMIT, but its query result only contained record (2). Interestingly, swapping the order of the two INSERT statements for t1 eliminates this anomaly.

Hi Mr. liu,

Thank you for your bug report.

However, we can not see how this can be a bug.

You are expecting that a tuple with a single column locks rows with values of 1 and 2, while only one tuple was inserted, and that one has only a value of 2.

Not a bug.

More important than this fact is something else.. You are using an obsolete release of 8.0. 

Hundreds of bugs have been fixed interim.

Current release is 8.0.37.

Unsupported.

I tried it on mysql 8.0.37 and the problem can be reproduced. It has been confirmed as a bug by Mariadb. For details, see https://jira.mariadb.org/browse/MDEV-34106. In fact, the SELECT FOR UPDATE statement of t2 will be blocked until t1 is committed. At this time, table t contains two rows [(1), (2)], but the result returned by the SELECT FOR UPDATE statement is incorrect.

Hi,

Changing status to "Not a bug".

This is not a bug , because you have two problems here ......

Let us look at the relevant part of your test case:

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ INSERT INTO t(a) VALUES (2);
/* t2 */ SELECT a FROM t FOR UPDATE;  -- actual: [2], expected: [1, 2]
/* t1 */ INSERT INTO t(a) VALUES (1);
/* t1 */ COMMIT;
/* t2 */ COMMIT;

Before  you do SELECT ........ FOR UPDATE in transaction t2,, there is only INSERT with a value of 2.

Hence, how can SELECT ..... FOR UPDATE, lock a row with a value of 1, when it is not inserted ????

However, even if transaction t1 were to insert a value of 1, it would not have been locked by transaction t2.

Why ??? Because this is READ-COMMITTED and transaction t1 has NOT committed anything yet.

Not a bug.

Maybe my test case is not very intuitive, let me modify it:

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ INSERT INTO t(a) VALUES (2);
/* t2 */ SELECT a FROM t FOR UPDATE;  -- blocked
/* t1 */ INSERT INTO t(a) VALUES (1);
/* t1 */ COMMIT;  -- SELECT ... FOR UPDATE returned : [2], expected: [1, 2]
/* t2 */ COMMIT;
/* t2 */ SELECT a FROM t FOR UPDATE;  -- return: [1, 2]

Hi,

This is irrelevant.

That SELECT ....... FOR UPDATE was issued before t1 committed.

I feel this bahavior is something wrong.
Because t1 was transaction, the result of t1 have to be observed all or nothing under the t2's isolation level READ-COMMITTED.

I can easily reproduce this both of 8.0.37 and 8.4.0.

Thank you Mr. liu,

That means that both releases are working correctly.

Hello Zhuang Liu!
I think your expectations for READ COMMITTED are too high.
The name of this isolation level is perhaps confusing, as it sounds as if your transaction is somehow guaranteed to see everything that was committed, while in reality all it promises, is that the rows which are returned represent results of transactions which were committed before the query has finished. Note the subtle difference:
- it doesn't guarantee you'll see all such rows (this is the problem you face here)
- it doesn't guarantee all returned rows will be from the same state of db (this isn't a problem you face here, but in general you can get a result set which has rows inserted by t2, t5, and t7 but not those inserted by t6 or t4)

You may wonder what is SELECT ..FOR UPDATE in READ COMMITTED mode good for then? I think it's mostly fine for "point selects": queries where you expect exactly one row to be returned because you specify primary_key=constant.

The behaviour which you see, while perhaps confusing, is actually well documented:
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
>  READ COMMITTED 
>  [...]
>  For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, 
> and DELETE statements, InnoDB locks only index records, not the gaps before 
> them, and thus permits the free insertion of new records next to locked 
> records. Gap locking is only used for foreign-key constraint checking and 
> duplicate-key checking.
>
> Because gap locking is disabled, phantom row problems may occur, as other 
> sessions can insert new rows into the gaps. For information about phantom 
> rows, see Section 17.7.4, “Phantom Rows”. 

A pretty good mental model of how InnoDB operates during SELECT FOR UPDATE in RC is the following: it uses the B-tree to find rows you've asked (first descending from root to the first potentially matching row, then traversing leaves from left to right), and whenever it finds a row which matches the WHERE clause it tries to acquire LOCK_X on it (But not on the gaps between rows. Also it doesn't acquire LOCK_S on rows which do not match - unless the condition is so difficult to evaluate that InnoDB has to LOCK_S it first, then report it to Server layer, which checks the complicated condition, and in case it doesn't match, InnoDB is asked by Server layer to releas the LOCK_S), once it acquires the LOCK_X it moves on to find more matching rows. So, the collection of LOCK_X-s acquired only ensures that the returned rows will not be modified by other transactions until you finish your transaction. This is not the same thing as preventing others from inserting new rows matching the search condition.

In some sense, your complain boils down to: there is no way to assign a serial order to t1 and t2 (i.e. "t1 happens-before t2" or "t2 happens-before t1") which would be consistent with my observations!
Which is completely right. And to be expected, given that this is not SERIALIZABLE but READ COMMITTED isolation level.

If you want to prevent this kinds of phenomena, you can use SERIALIZABLE, or REPEATABLE READ (which for all practical matters behaves just like SERIALIZABLE in case of FOR UPDATE clause).
With this change, the behaviour will be as you expect.
And the technical reason for that is that in SERIALIZABLE and RR, InnoDB will take locks on gaps between records, too.
So, once InnoDB will pass by the gap into which row with id=1 could be inserted, it will also lock this gap, so nobody can insert into it.

Now, lets change the order of records insertion and see we get the expected result.

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ INSERT INTO t(a) VALUES (1);
/* t2 */ SELECT a FROM t FOR UPDATE;  -- blocked
/* t1 */ INSERT INTO t(a) VALUES (2);
/* t1 */ COMMIT;  -- SELECT ... FOR UPDATE returned : [1, 2], expected: [1, 2]
/* t2 */ COMMIT;
/* t2 */ SELECT a FROM t FOR UPDATE;  -- return: [1, 2]

This IMHO proves the following point from Jakub in the previous comment. 

> it doesn't guarantee you'll see all such rows (this is the problem you face here)
> once it acquires the LOCK_X it moves on to find more matching rows