Bug #71637 Manual mentions IS_GAP and IX_GAP locks, but never explains them
Submitted: 9 Feb 2014 14:23 Modified: 23 Feb 2016 15:18
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: information_schema, innodb_locks, missing manual

[9 Feb 2014 14:23] Valeriy Kravchuk
Description:
This manual page, https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html, explains possible values for LOCK_MODE column like this:

"Mode of the lock. One of S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections Section 14.2.2.3, “InnoDB Lock Modes” and Section 14.2.2.2, “The InnoDB Transaction Model and Locking” for information on InnoDB locking."

But both these pages referenced above:

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-model.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

while they mention gap locks in general, says nothing about IS_GAP and IX_GAP locks. What are these locks? When are they set? Do they even exist in reality?

I do know about "insert intention" locks, but how these are related to IX_GAP and IS_GAP mentioned above?

How to repeat:
Try hard to find out what are these IS_GAP and IX_GAP locks in the manual, or try to see them alive in the information_schema.innodb_locks table.

Suggested fix:
Document the reality, not some imaginary ideas of the reality. Provide examples showing the locks you mention in the manual. This topic (InnoDB locks and monitoring them) is too often misunderstood by users, so do not add extra confusion in the manual, please.
[9 Feb 2014 14:42] Valeriy Kravchuk
Corrected tag.
[11 Feb 2014 11:43] MySQL Verification Team
Thank you for the bug report.
[23 Feb 2016 15:18] Daniel Price
Posted by developer:
 
The LOCK_MODES field description in the INNODB_LOCKS Table documentation has been revised. The changes should appear online within 24 hours.

https://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html

From storage/innobase/handler/i_s.cc:

#define IDX_LOCK_MODE		2
	{STRUCT_FLD(field_name,		"lock_mode"),
	 /* S[,GAP] X[,GAP] IS[,GAP] IX[,GAP] AUTO_INC UNKNOWN */
	 STRUCT_FLD(field_length,	32),
	 STRUCT_FLD(field_type,		MYSQL_TYPE_STRING),
	 STRUCT_FLD(value,		0),
	 STRUCT_FLD(field_flags,	0),
	 STRUCT_FLD(old_name,		""),
	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},

Thank you for the bug report.