Related to this topic:
http://forums.mysql.com/read.php?22,10506,11347#msg-11347
It seems that in the situation described below it is possible to set X lock on the same gap while as I understand from docs it should not be possible since X lock conflicts with all other locks.
How to reproduce (mysql(1), mysql(2) and mysql(3) are different sessions):
mysql(1)> create table innodb_monitor (id int);
mysql(1)> create table innodb_lock_monitor (id int);
mysql(1)> create table T (C int not null primary key) engine=InnoDB;
mysql(1)> start transaction;
mysql(1)> select * from T where C = 42 for update;
mysql(2)> start transaction;
mysql(2)> select * from T where C = 42 for update;
mysql(3)> show engine innodb status\G;
mysql(1)> insert into T set C = 42; [waits]
mysql(2)> insert into T set C = 42; [deadlock]
In the innodb monitor output (got before deadlock) there is following information about transactions:
------------
TRANSACTIONS
------------
Trx id counter 3EDBDF
Purge done for trx's n:o < 3EDBCD undo n:o < 0
History list length 675
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 54, OS thread handle 0xa6affb40, query id 1067 localhost root
show engine innodb status
---TRANSACTION 3EDBDE, ACTIVE 4 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 1066 localhost root
TABLE LOCK table `test_dl`.`T` trx id 3EDBDE lock mode IX
RECORD LOCKS space id 0 page no 1513 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDBDE lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
---TRANSACTION 3EDBDD, ACTIVE 15 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 1064 localhost root
TABLE LOCK table `test_dl`.`T` trx id 3EDBDD lock mode IX
RECORD LOCKS space id 0 page no 1513 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDBDD lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
And it seems that last two got the same X lock on the gap at the end of the table.
So the question is whether this is correct behavior?
And if it is correct then why it is possible?