Bug #71735 Manual does not explain locks set by SELECT ... FOR UPDATE properly
Submitted: 16 Feb 2014 12:59 Modified: 27 Oct 2016 13:34
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, missing manual, next-key, SELECT ... FOR UPDATE

[16 Feb 2014 12:59] Valeriy Kravchuk
Description:
Manual page on locks set by different SQL statements does NOT explain properly what locks are really set by SELECT ... FOR UPDATE statement that accessed rows via secondary index. Some details are implicit in the text, others are missing, so real life cases can not be easily explained by the manual.

I see the following statements in the manual related to SELECT ... FOR UPDATE with rows accessed via secondary index:

"A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record."

then:

"If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them."

later:

"For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause)."

and finally:

"For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels."

Please, correct me if I missed anything relevant for this case.

I see the following problems with the above:

1. Manual does not say explicitly that SELECT ... FOR UPDATE sets exclusive next-key locks on the index used, while this is true in general and can be inferred from the text above.

2. Manual does not say explicitly what kind of lock SELECT ... FOR UPDATE sets on cluster index record when access happens via secondary index. It's "lock_mode X locks rec but not gap", so just exclusive locks.

3. Manual does not allow to explain the case presented below, when WHERE secondary_index_column=const clause is used in SELECT ... FOR UPDATE. In this case we do NOT see next-key lock and gap before the records is NOT locked. This case must be explained somehow.

How to repeat:
1. Create a table like this and insert one row there:

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from tt;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

2. Check that secondary index is used for SELECT like this:

mysql> explain select * from tt where c1=1 for update;
+----+-------------+-------+-------+---------------+------+---------+-------+---
---+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | ro
ws | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+---
---+-------+
|  1 | SIMPLE      | tt    | const | c1            | c1   | 5       | const |
 1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+---
---+-------+
1 row in set (0.00 sec)

3. Start InnoDB lock monitor or use new 5.6 feature to get information about all locks written to the error log:

mysql> set global innodb_status_output=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

4. Start transaction in session #1 and do SELECT ... FOR UPDATE used in explain above ("locking read", isn't it):

mysql> begin work;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tt where c1=1 for update;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> select sleep(20);
+-----------+
| sleep(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.00 sec)

We are making sure that at least 20 seconds pass to see lock written to the error log.

5. In session #2 make sure that you actually can insert before and after this record (1,1) in the table:

mysql> insert into tt values(0,0);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tt values(2,2);
Query OK, 1 row affected (0.09 sec)

So, definitely there was no gap lock set. Error log will have a content like this:

---TRANSACTION 36755, ACTIVE 31 sec
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 6, OS thread handle 0x1ec8, query id 153 localhost ::1 root cleaning up
TABLE LOCK table `test`.`tt` trx id 36755 lock mode IX
RECORD LOCKS space id 242 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 36755 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 36755 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000008f08; asc       ;;
 2: len 7; hex 07000001ab255a; asc      %Z;;
 3: len 4; hex 80000001; asc     ;;

So, we do NOT have any next-key lock. Just 2 "index-record locks" in terms of the manual. Now, try to find explanation for this in the manual.

Suggested fix:
1. Make sure to say explicitly that usually SELECT ... FOR UPDATE sets exclusive next-key locks. You say this for UPDATE, for example:

"UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. "

2. Make sure to show how "next-key" and "index-record" lock really looks like in the output of SHOW ENGINE INNODB STATUS. You will not find these terms there.

3. Add explanation for the case presented in "How to repeat" above. Specifically, explain when "gap" before the record is NOT locked by SELECT ... FOR UPDATE.
[16 Feb 2014 13:27] Valeriy Kravchuk
There is actually a hint on another manual page, http://dev.mysql.com/doc/refman/5.6/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."

But I think this kind of detail must be explicitly mentioned on the page I complained about as well.
[18 Feb 2014 11:32] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[27 Oct 2016 13:34] Daniel Price
Posted by developer:
 
These updates were made to the following page for all versions of the reference manual:
http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

* It is now explicitly stated that SELECT ... FOR UPDATE sets exclusive next-key locks.

* SHOW ENGINE INNODB STATUS output examples were added for intention, index record, next-key locks.  

* This information is now present where applicable: Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

Changes should appear online soon.

Thank you for the bug report.