Several locks taken to same record inside a transaction.

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

------------

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

--------

delete from tu where a = 9999 and b = 'xxxx';

show engine innodb status\G

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

--------