Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.7.8
-
None
-
OS: Ubuntu-20.04 LTS
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.
Attachments
Issue Links
- relates to
-
MDEV-16232 Use fewer mini-transactions
-
- Stalled
-
Activity
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.
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.