Description:
While discussing and trying to understand the reason of deadlock from Bug #82127 (use the table and data from it) I've noted the following interesting output from INNODB STATUS:
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 636199, ACTIVE 32 sec
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 86 localhost root init
show engine innodb status
TABLE LOCK table `test`.`tu` trx id 636199 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636199 lock_mode X locks rec but not gap
RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636199 lock_mode X locks rec but not gap
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636199 lock_mode X
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636199 lock_mode X locks gap before rec
--------
FILE I/O
--------
...
Note that for the exact same record in the `u` indexd we have:
- record X lock
- next-key (record + gap before it) X lock
- gap X lock
I wonder what could be a legitimate reason for a single transaction to request and hold all three locks on the same record?
How to repeat:
CREATE TABLE `tu`(`id` int(11), `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY(`id`), UNIQUE KEY `u`(`a`,`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0;
insert into tu values(1,1,'a','a'),(2,9999,'xxxx','x'),(3,10000,'b','b'),(4,4,'c','c');
set global innodb_status_output=ON;
set global innodb_status_output_locks=ON;
start transaction;
delete from tu where a = 9999 and b = 'xxxx';
show engine innodb status\G
In the output you'll see:
------------
TRANSACTIONS
------------
Trx id counter 636202
Purge done for trx's n:o < 636201 undo n:o < 0 state: running but idle
History list length 37
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 636201, ACTIVE 4 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 90 localhost root init
show engine innodb status
TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
--------
Normal and expected so far, record X locks on the unique index and primary key, we deleted and could delete one and only one row. Now, repeat the same delete in the same transaction:
delete from tu where a = 9999 and b = 'xxxx';
show engine innodb status\G
and you'll see this beautiful set of locks:
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 636201, ACTIVE 202 sec
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 92 localhost root init
show engine innodb status
TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X
RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks gap before rec
--------
Suggested fix:
Check if there is any good reason to ask for a gap X lock on a record in the secondary unique index when transaction already has the next key lock on it.
Alternatively, if this is just the way locks are reported, please, fix it so the output makes sense.
In any case, please, document in the manual what locks this kind of DELETE sets when it encountered a record already marked as deleted, and why.
