Bug #71638 Manual does NOT explain "insert intention" lock mode properly
Submitted: 9 Feb 2014 14:41 Modified: 13 May 2016 19:29
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: innodb, insert intention, missing manual

[9 Feb 2014 14:41] Valeriy Kravchuk
Description:
It's well known that "insert intention" locks exist, one can see them in the output of SHOW ENGINE INNODB STATUS:

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 36016, ACTIVE 62 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 0x1030, query id 147 localhost ::1 root init

show engine innodb status
Trx read view will not see trx with id >= 36017, sees < 35970
---TRANSACTION 35970, ACTIVE 1145 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x810, query id 146 localhost ::1 root upda
te
insert into t values(4, "valerii")
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 244 page no 4 n bits 72 index `i` of table `test`.`t` trx
id 35970 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000e6c02; asc     l ;;

So, this is a kind of a gap lock. For some reason manual, https://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html, does not mention this lock type at all.

It refers to https://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html though for gap locks explanation, but there we can read:

"A type of gap lock called an insertion intention gap lock is set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting. For more information about intention locks, see Section 14.2.2.3, “InnoDB Lock Modes”. "

So, there is a reference back to the page on lock modes that, actually, says nothing about "insert intention" (why "insertion intention" in the manual by the way?) locks!

How to repeat:
Try to find out in the manual what does that "insert intention" lock (that you see in the INNODB STATUS) really means.

Suggested fix:
Describe all existing row lock modes, with examples and real outputs. Make sure to use the same terms ("insert intention") that are produced by InnoDB in the outputs (or fix InnoDB code to use proper terms).
[12 Feb 2014 10:24] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[13 May 2016 19:29] Daniel Price
Posted by developer:
 
Insert intention lock documentation has been revised.
http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
Thank you for the bug report.