Linux

spachev, an INSERT into a table that lacks any secondary indexes, InnoDB can use implicit locking. That is, if the hidden system column DB_TRX_ID in a clustered index leaf page record is referring to an active transaction, other transactions will treat that record as explicitly locked.

If a locking conflict occurs, then the waiting transaction will convert an implicit lock that is held by the conflicting transaction into an explicit one, and then create a waiting lock request for itself.

I do not remember off the top of my head whether secondary indexes would always involve explicit locking (such as gap locks). In MDEV-16406 you can find some notes on this.

Are you able to repeat this problem with something newer than 10.2.27? In 10.6, the lock_sys was heavily refactored, which could affect the reproducibility of timing-sensitive bugs. Already in 10.3 there are some fixes that are not present in the 10.3 series, such as MDEV-26206, which fixed a regression caused by MDEV-16675 in 10.3.

I think that vlad.lesin knows the locking code best, after fixing MDEV-20605 and MDEV-27025. Could this report actually be a duplicate of MDEV-27025?

It would be useful to know what the indexes in the table look like. In InnoDB, each index is kind of a table on its own. The more indexes, the better chance for locking conflicts.

marko:

CREATE TABLE t1 (

`name` varchar(255) DEFAULT NULL,

`c1` varchar(100) DEFAULT NULL,

`t1` datetime DEFAULT NULL,

`t2` datetime DEFAULT NULL,

`id` char(32) NOT NULL DEFAULT '',

`c2` varchar(40) DEFAULT NULL,

`c3` datetime DEFAULT NULL,

`c4` varchar(40) DEFAULT NULL,

`t3` datetime DEFAULT NULL,

`c5` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Columns and the table renamed to avoid possibly revealing sensitive information. The problem index is "name".

Another detail - when the deadlock happens, one of the shared row locks currently being held by the transaction is for the same index record as the exclusive lock that it has obtained. The workload that leads to this involves a number of INSERT IGNORE with the result being the record not being inserted due to the duplicate key conflict on name.

Another detail - all of the locks, all the spurious shared locks as well as the one exclusive lock have info bits 0x20, which is deleted mark.

Are you able to repeat this problem with something newer than 10.2.27?

The question is still relevant. 10.2 is coming to EOL soon, it looks unlikely that the problem would be fixed till then, so unless it's happening on higher versions, it will be discarded as irrelevant after we stop releasing 10.2.

No, because this case happens under a very particular production load pattern, and we are not running anything newer than 10.2 with those patterns in production.

spachev, did you check the lock mode of those 200+ record lock bitmaps? Did you try using the READ COMMITTED isolation level? I am wondering if the problematic locks are gap locks, and whether this bug could have been fixed in MDEV-28422.