[MDEV-26687] Absence of expected locks result in different table states under RC Created: 2021-09-26  Updated: 2021-11-01

Status: Open
Project: MariaDB Server
Component/s: Documentation, Storage Engine - InnoDB
Affects Version/s: 10.5.12
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: dinary Assignee: Ian Gilfillan
Resolution: Unresolved Votes: 0
Labels: innodb
Environment:

OS: Ubuntu-20.04


 Description   

When two transactions are committed concurrently under the Read Committed isolation level, an UPDATE is not blocked even it conflicts with the UPDATE of another concurrent transaction from the user's point of view, resulting in a different result from the counterpart under the Repeatable Read isolation level.

create table t(a int, b int);
 
insert into t values(null, 1), (null, 2), (null, 3);
 
 
 
/* s1 */ begin; 
/* s1 */ update t set a = 10; 
/* s2 */ begin; 
/* s2 */ update t set b = 20 where a;
 
/* s1 */ commit;
 
/* s2 */ commit;

==================== Read Committed ==========================

the UPDATE of s2 is NOT blocked and the final table state is

mysql> select * from t;
 
+------+------+
 
| a    | b    |
 
+------+------+
 
|   10 |    1 |
 
|   10 |    2 |
 
|   10 |    3 |
 
+------+------+

=================== Repeatable Read ==========================

the UPDATE of s2 is blocked and the final table state is

mysql> select * from t;
 
+------+------+
 
| a    | b    |
 
+------+------+
 
|   10 |   20 |
 
|   10 |   20 |
 
|   10 |   20 |
 
+------+------+



 Comments   
Comment by Marko Mäkelä [ 2021-09-27 ]

I think that this is by design, and only needs to be documented.

Non-matched rows are not being locked in the so-called ‘semi-consistent read’ that was implemented to address MySQL Bug #3300.

The function ha_innobase::try_semi_consistent_read() may only enable semi-consistent read when the TRANSACTION ISOLATION LEVEL is READ COMMITTED or READ UNCOMMITTED.

Comment by dinary [ 2021-09-28 ]

Thanks for your explanation. But I think it is not in line with expectations from the user's point of view.

I notice the example and explanation of "semi-consistent" read in MySQL reference manual and think the following expression is incorrect:

"If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it."

Actually, if the transaction uses the latest committed version to judge whether a target row matches the WHERE condition, it cannot claim that the row matches "must" be updated. Because there may be another uncommitted transaction modifying the same target row to make it unmatch the WHERE condition.

In addition, I think the example used to explain the "semi-consistent" read is not representative enough. Specifically, modifications in transaction A do not affect the judgment of the WHERE clause in transaction B, and vice versa. Consider the following example:

/* init */ CREATE TABLE t (a INT) ENGINE = InnoDB;
/* init */ INSERT INTO t VALUES (1),(2);

Transaction A:
/* s1 */ begin;
/* s1 */ update t set a = 2 where a = 1;
/* s1 */ commit;

Transaction B:
/* s2 */ begin;
/* s2 */ update t set a = 1 where a = 2;
/* s2 */ commit;

From the user's point of view, the final state of table should be [(1),(1)] or [(2),(2)] after they are executed serially or concurrently.

But if we use "semi-consistent" read as described above, and these two transactions are submitted in the following order

/* s1 */ begin;
/* s1 */ update t set a = 2 where a = 1;
/* s2 */ begin;
/* s2 */ update t set a = 1 where a = 2;
/* s1 */ commit;
/* s2 */ commit;

the final state of the table will be [(2), (1)], which violates the intuition of users.

Generated at Thu Feb 08 09:47:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.