Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.0, 10.5.8
-
docker mariadb:10.5, centos 7
Description
During regression testing of our application on MariaDB 10.5 we hit a gap lock that doesn't occur in previous versions. Our transactions use isolation level READ COMMITTED so there shouldn't be any gap locks.
Test:
DROP TABLE IF EXISTS `test`;
|
CREATE TABLE `test` (
|
ID varchar(40) NOT NULL,
|
TEST1 varchar(40) DEFAULT NULL,
|
TEST2 varchar(15) NOT NULL,
|
TEST3 bigint(20) DEFAULT NULL,
|
KEY `IDX_TEST` (TEST1, TEST2, TEST3)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
|
|
INSERT INTO test (ID, TEST1, TEST2, TEST3) VALUES ('row_a', 'A.123', 'C', 3);
|
INSERT INTO test (ID, TEST1, TEST2, TEST3) VALUES ('row_a', 'B.456', 'C', 3);
|
|
SET GLOBAL INNODB_STATUS_OUTPUT_LOCKS = 'ON';
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
BEGIN;
|
DELETE FROM test WHERE TEST1 = 'A.123a' and TEST2 = 'C' and TEST3 = 3;
|
SHOW ENGINE INNODB STATUS\G
|
The delete statement matches 0 rows, but creates a gap lock.
Using a different delete statement doesn't create a gap lock.
DELETE FROM test WHERE TEST1 = 'G.123a' and TEST2 = 'X' and TEST3 = 31;
|
Expected Behavior:
no gap lock on the delete statement.
Attachments
Issue Links
- is caused by
-
MDEV-19544 Remove innodb_locks_unsafe_for_binlog
-
- Closed
-
- relates to
-
MDEV-16232 Use fewer mini-transactions
-
- Stalled
-
-
MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration
-
- Closed
-
-
MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration
-
- Closed
-
I do not think that we have made any significant changes to the locking subsystem since MariaDB 10.3, when trx_sys.rw_trx_hash was introduced.
Write operations will always use the same type of locking, independent of the transaction isolation level. The only special isolation level is SERIALIZABLE, which will cause all reads to be locking ones, as if LOCK IN SHARE MODE had been appended to each SELECT statement.
The READ COMMITTED isolation level behaves just like the default REPEATABLE READ, with the exception that a new read view will be created at the start of each SQL statement.
The READ UNCOMMITTED isolation level will avoid creating a read view altogether, and always return the latest available version. For locking operations, the latest available version is the only possible version.
A DELETE or UPDATE always consists of a locking read (which will acquire an explicit record lock until we address MDEV-16232) followed by the modification. A DELETE will update the record header and the hidden columns DB_TRX_ID,DB_ROLL_PTR in the clustered index record. In secondary indexes, only the record header and the PAGE_MAX_TRX_ID will be updated.
When it comes to gap locks, there is the open bug
MDEV-20605.Note that InnoDB does not have a concept of a ‘table row lock’. It has a concept of ‘index record locks’. You are defining one secondary index, and the DELETE statement might use it.
The observed change in behavior might be due to a different query plan. Which version had you tested earlier?