MySQL Bugs: #65164: confused about docs and behavior for InnoDB gap locks

Bug #65164	confused about docs and behavior for InnoDB gap locks
Submitted:	1 May 2012 17:08	Modified:	28 Aug 2013 19:58
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1,5.6	OS:	Any
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
The behavior for this test case is reasonable. But the documentation isn't clear and the output from IS.innodb_locks and SHOW ENGINE INNODB STATUS confuses me.

What I don't understand is that SHOW ENGINE INNODB STATUS and IS.INNODB_LOCKS report that the locks held from the DELETE statement are X,GAP. I don't understand how sessions can concurrently lock the same gap with X,GAP locks as X means exclusive to me. I assume that X,GAP conflicts with X,GAP for the same gap, otherwise it should be called S,GAP, but ...

I think the docs could also be updated to explain that gap locks are used for a unique index with a unique search condition when the search fails.

Finally, the lock modes docs don't mention gap locks or insert intention locks -- http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html. When do they conflict with other requests?

How to repeat:
create table t(i int) engine=innodb;
alter table t add unique index x(i);
insert into t values (2), (3), (4);

connection 1:
begin;
delete from t where i=1;

connection 2:
begin;
delete from t where i=1;

connection 3:
begin;
delete from t where i=1;
insert ignore into t values (1) <-- this hangs

The hang is reasonable behavior as the gap locks held by connections 1 and 2 from DELETE conflict with the insert intention lock needed for the INSERT.

SHOW ENGINE INNODB STATUS has mostly useful output:

------- TRX HAS BEEN WAITING 44 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 4 n bits 72 index `x` of table `test`.`t` trx id 3751695625 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000200; asc       ;;

The output from IS.INNODB_LOCKS also reports the locks held for DELETE as "X,GAP".

+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3751695625:17:4:2 | 3751695625  | X,GAP     | RECORD    | `test`.`t1` | `x1`       |         17 |         4 |        2 | 2         |
| 3751695627:17:4:2 | 3751695627  | X,GAP     | RECORD    | `test`.`t1` | `x1`       |         17 |         4 |        2 | 2         |
| 3751695626:17:4:2 | 3751695626  | X,GAP     | RECORD    | `test`.`t1` | `x1`       |         17 |         4 |        2 | 2         |
+-------------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
+-------------------+-------------------+-----------------+-------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id  |
+-------------------+-------------------+-----------------+-------------------+
| 3751695625        | 3751695625:17:4:2 | 3751695627      | 3751695627:17:4:2 |
| 3751695625        | 3751695625:17:4:2 | 3751695626      | 3751695626:17:4:2 |
+-------------------+-------------------+-----------------+-------------------+

Thank you for the report.

Verified as described.

Mark,
a gap X-lock really is equivalent to an S-lock on a gap.

We do allow "conflicting" locks to be held on a gap by different transactions. Transaction A may hold an S-lock on a gap, and transaction B may hold an X-lock on the SAME gap.

The reason why we have to allow conflicting locks is that if we purge a record from an index, we need to merge gap locks from two gaps. That way we will get "conflicting" locks on the same gap.

Gap locks in InnoDB are "purely inhibitive". That is, they only stop other transactions from inserting to the gap. Thus, a gap X-lock actually has the same effect as a gap S-lock.
Regards, Heikki

Intention locks are described on this page: "InnoDB Lock Modes"
http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html
An "Intention Locks" header has been added to make the content more visible.

Gap locks are described on this page: "InnoDB Record, Gap, and Next-Key
Locks"
http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

The "InnoDB Lock Modes and "InnoDB Record, Gap, and Next-Key Locks" pages now
have reciprocal links.

This change applies to version 5.0 through 5.7 of the reference manual.

Hi Mark,
Regarding: "the docs could also be updated to explain that gap locks are used for a unique index with a unique search condition when the search fails."

Does the information quote below address the "search failure" situation you mention? Did the search fail because your search condition included an id that was not indexed or had a non-unique index? 
 
http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

"Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap."

** Conflicting gap locks has been addressed on this page:
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

** Regarding gap locks being used for unique index with a unique search condition when the search fails, see the information on this page that that discusses gap locking being taken for statements that lock rows using a unique index to search for a unique row. It speaks to when gap locks would be taken (e.g. when the search condition includes only some columns of a multiple-column unique index, id is not indexed or has a nonunique index).
http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

** Regarding "Lock modes docs don't mention gap locks or insert intention locks":
Intention Locks are covered on the following page which provides a lock compatibility matrix:
http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html
GAP locks and insert intention gap locks are described here:
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

Other revisions to locking information include:
- Section headings to improve navigation
- Cross links between "Lock Modes" and "Record, Gap, and Next-key" topics. 

Updates have been applied to versions 5.0 through to 5.7 of the reference manual.

Thank you for the bug report. If you find that further clarification is needed, please let us know.

LOCK_X | LOCK_GAP and LOCK_S | LOCK_GAP are mutually compatible, but we will need the LOCK_X and LOCK_S so that we could use lock_mode_compatible() call to easily identify an insert from a search, so this is from the coding perspective.

And in that check, the LOCK_INSERT_INTENTION plays further role of deciding whether 2 locks are compatible. So essentially

1) Insert will use this "LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION" to test the GAP, and it is conflicts with "LOCK_S | LOCK_GAP" or "LOCK_X | LOCK_GAP"

2) "LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION" is compatible with ""LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION", as the GAP lock is inhibitive in a way that inserts are not blocking with each other

3) Further record lock could apply to the record