MySQL Bugs: #63665: X row locks do not prevent an IX table lock, contrary to documentation

Bug #63665	X row locks do not prevent an IX table lock, contrary to documentation
Submitted:	8 Dec 2011 11:12	Modified:	8 Dec 2011 13:27
Reporter:	Esben Mose Hansen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	14.14 Distrib 5.1.58, for deb	OS:	Linux (Debian sid)
Assigned to:		CPU Architecture:	Any
Tags:	lock IX X

Description:
According to the manual http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html, IX table locks conflicts with X row locks, and thus cannot be granted when such a lock is held. To quote

"A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks."

The table above shows that IX and X locks conflicts. However, this is not what I experience: I have (below, in how to repeat) and example where an IX lock is granted even though another transaction holds a X row lock. So either the manual is wrong, or can be misread, or there is a bug in the locking mechanism (and hence the S2 status.)

How to repeat:
Start out by using a mysql client to run

drop table if exists test; create table test (A int, B int, primary key (A,B), index (A)) TYPE = InnoDB;  insert into test values (1,1); start transaction; delete from test where a=1;

on some database, using the default isolation level (Repeatable Read). Using the SHOW ENGINE innodb STATUS, we get

TABLE LOCK table "esben_mosegris-esben-trunk"."test" trx id 0 4604458 lock mode IX
RECORD LOCKS space id 7800 page no 3 n bits 72 index "PRIMARY" of table "esben_mosegris-esben-trunk"."test" trx id 0 4604458 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;; 1: len 4; hex 80000001; asc     ;; 2: len 6; hex 00000046422a; asc    FB*;; 3: len 7; hex 00000000392350; asc     9#P;;

Note that the transaction holds both a TABLE lock (IX) and a row lock (X) on a row in the table. 

Now start another mysql client using the same database, and execute

start transaction; delete from test where a=2;

This statement goes straight through, and the new transaction also gets to hold and IX lock on the same table --- even though it should conflict with the X row lock held by the other transaction.

I have pasted more details into the question here

http://stackoverflow.com/questions/8402668/mysql-innodb-locking-ix-should-not-be-granted-w...

in case that makes it easier. Also note that I have created the "magic table" that allows SHOW ENGINE innodb STATUS to print some additional information, as you can probably see.

Suggested fix:
Either correct the behavior (so that the first transaction blocks the second) or improve the documentation to whatever behavior is actually expected.

It would also be nice if the output of SHOW ENGINE innodb STATUS was explained in more in more details, in particular it would be a good thing if I could read exactly what rows and gaps are actually held by each transaction.

Sorry, but you misread that manual page. What is important is that IX lock is set on table level, so you have to check locks at the same level for conflicts, not row (lower level) locks. Table clearly shows that IX lock from one transaction is compatible with IX lock from other transaction (on the same table). Only X or S lock at table level is not compatible (think LOCK TABLE ...).

Manual clearly says:

"Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table."

Thanks for explaining :) And sorry, the manual is not clear at all, and could be improved in several ways.

 1. Nowhere on the page is the *table* lock type X or S mentioned. The closest is that the sentence "To make locking at multiple granularity levels practical, *additional* types of locks called intention locks are used." (emphasis mime). It would be much easier to read if all the table level locks (X, S, IX, IS) were listed at some point. 
 
 2. It is not mentioned anywhere that conflicts are on the same level only, except for the bit your mentioned that could be interpreted sort of that way. Much better if the sentence I quoted were written as 
"A lock is granted to a requesting transaction if it is compatible with *all* existing locks *on the same granularity level*, but not if it conflicts with *any* existing locks *on the same granularity level*."
Emphasis are my insertions. It might be better without the "but.." part, but that is a detail.

 3. Finally, it would still be a huge improvement if it was explained how exactly to see gap locks vs. row locks in the output of show engine innodb status on the following pages.

Again, thank you for the explanation and I hope the manual change I have suggested could save you from being bothered in the future. I actually had 2 people beside me read the manual page, and neither could explain the observed behavior.

One last improvement: It would be good if all the granularity levels were listed (ROW level, TABLE level). That would eliminate any confusion whether e.g. gap locks are on the same level as ROW locks, or on a separate granularity levels).