Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.3.27, 10.5.8
-
None
-
Ubuntu 18.04 x64
Description
I have a problem with blocking records when using SELECT and READ COMMITTED isolation level for InnoDB. The Document https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.htm says that READ COMMITTED isolation level provide non-blocking read, but MariaDB 10.5.8 violates this rule.
Following select under if statement locks records:
IF EXISTS( SELECT * FROM `test_table` ) THEN
|
SELECT * FROM `test_table`;
|
ELSE
|
SELECT 'there is nothing'; |
END IF;
|
I made a simple test to check the deadlock. There are two attached files:
- test_schema.sql - database schema
- locks_test_script.sql - scripts for test deadlock
Run scripts from locks_test_script.sql under two sessions. Run the script for session 1 first and after that run script for session 2:
-- session 1 ------------- |
USE `test`;
|
SET autocommit = 0; |
COMMIT;
|
SHOW ENGINE INNODB STATUS;
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
call READ_PROCEDURE();
|
SHOW ENGINE INNODB STATUS;
|
-- end session 1 ------------- |
-- session 2 ------------ |
USE `test`;
|
SET autocommit = 0; |
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
UPDATE `test_table` SET `test_table`.`strTextExtended` = 'UPDATED'; |
SHOW ENGINE INNODB STATUS;
|
-- end session 2 ------------ |
There are two locks after call READ_PROCEDURE:
---TRANSACTION 284064029008304, ACTIVE 0 sec |
2 lock struct(s), heap size 1128, 1 row lock(s) |
MySQL thread id 5, OS thread handle 29560, query id 136 localhost ::1 root starting |
SHOW ENGINE INNODB STATUS
|
And the script for session 2 hangs up:
MariaDB [test]> UPDATE `test_table` SET `test_table`.`strTextExtended` = 'UPDATED'; |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
call "SHOW ENGINE INNODB STATUS;" after UPDATE `test_table` ...
------------
|
TRANSACTIONS
|
------------
|
Trx id counter 119 |
Purge done for trx's n:o < 118 undo n:o < 0 state: running |
History list length 53 |
LIST OF TRANSACTIONS FOR EACH SESSION:
|
---TRANSACTION 118, ACTIVE 118 sec |
1 lock struct(s), heap size 1128, 2 row lock(s) |
MySQL thread id 6, OS thread handle 29560, query id 143 localhost ::1 root starting |
SHOW ENGINE INNODB STATUS
|
---TRANSACTION 284064029008304, ACTIVE 217 sec |
2 lock struct(s), heap size 1128, 1 row lock(s) |
MySQL thread id 5, OS thread handle 29560, query id 136 localhost ::1 root |
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-