MySQL Forums
Forum List  »  InnoDB

X lock on the same gap
Posted by: Boris Serebrov
Date: September 24, 2012 09:13AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
X lock on the same gap
2076
September 24, 2012 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.