[MDEV-30835] Inconsistent blocking of UPDATE and DELETE with the same WHERE clause Created: 2023-03-12  Updated: 2023-03-13

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.7.8
Fix Version/s: None

Type: Bug Priority: Major
Reporter: John Jove Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: transactions
Environment:

OS: Ubuntu-20.04 LTS

Issue Links:
Relates
relates to MDEV-16232 Use fewer mini-transactions Stalled

 Description   

Isolation Level: Read Uncommitted & Read Committed.
UPDATE statement is not blocked by the INSERT statement of another transaction, while DELETE statement that has the same WHERE clause as the UPDATE statement is blocked by the same INSERT statement of another transaction.

Test Case 1:

/* init */ CREATE TABLE t0(c0 DOUBLE);
 
/* init */ INSERT INTO t0(c0) VALUES (9.1);
 
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
 
 
/* t1 */ BEGIN;
 
/* t1 */ INSERT INTO t0(c0) VALUES (2.5);
 
/* t2 */ BEGIN;
 
/* t2 */ UPDATE t0 SET c0=3.3 WHERE c0 = 2.5;
 
/* t1 */ COMMIT;
 
/* t2 */ COMMIT;

Test Case 2:

/* init */ CREATE TABLE t0(c0 DOUBLE);
 
/* init */ INSERT INTO t0(c0) VALUES (9.1);
 
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
 
 
/* t1 */ BEGIN;
 
/* t1 */ INSERT INTO t0(c0) VALUES (2.5);
 
/* t2 */ BEGIN;
 
/* t2 */ DELETE FROM t0 WHERE c0 = 2.5; -- blocked
 
/* t1 */ COMMIT;
 
/* t2 */ COMMIT;

We expect UPDATE statement in test case 1 to be blocked.

 Comments   
Comment by Marko Mäkelä [ 2023-03-13 ]

thiru, can you please post your mtr test of this and your findings?

As far as I understand, the reason why the locks are being relaxed for UPDATE is the "semi-consistent read" that I originally implemented in MySQL Bug #3300. Implementing it for DELETE was not considered at that time.

I would expect that once MDEV-16232 allows UPDATE and DELETE to rely on implicit locks, the lock conflicts would be reduced, or possibly avoided altogether.

Comment by Thirunarayanan Balathandayuthapani [ 2023-03-13 ]

Update test case:

--source include/have_innodb.inc
 
CREATE TABLE t1(f1 int not null)engine=innodb;
 
insert into t1 values(1);
 
 
 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN;
 
INSERT INTO t1(f1) VALUES (2);
 
 
 
connect(con1,localhost,root,,,);
 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN;
 
UPDATE t1 set f1 =3 where f1=2;
 
 
 
connection default;
 
commit;
 
 
 
connection con1;
 
commit;
 
 
 
connection default;
 
select * from t1;
 
disconnect con1;
 
drop table t1;

Delete test case:

--source include/have_innodb.inc
 
CREATE TABLE t1(f1 int not null)engine=innodb;
 
insert into t1 values(1);
 
 
 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN;
 
INSERT INTO t1(f1) VALUES (2);
 
 
 
connect(con1,localhost,root,,,);
 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN;
 
DELETE FROM t1 WHERE f1=2;
 
 
 
connection default;
 
commit;
 
 
 
connection con1;
 
commit;
 
 
 
connection default;
 
select * from t1;
 
disconnect con1;
 
drop table t1;

For update statement, prebuilt->row_read_type is ROW_READ_TRY_SEMI_CONSISTENT.
But in delete statement, prebuilt->row_read_type is ROW_READ_WITH_LOCKS.

{{ table->file->try_semi_consistent_read(1);}} is being called only for update statment.

Generated at Thu Feb 08 10:19:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.