Bug #71736 Manual does not explain locks set by UPDATE properly
Submitted: 16 Feb 2014 13:23 Modified: 19 Oct 2016 17:28
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 shared lock, UPDATE

[16 Feb 2014 13:23] Valeriy Kravchuk
Description:
Manual page on locks set by different SQL statements (http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html) says the following related to UPDATE:

"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."

and finally:

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

Correct me if I missed some other relevant part.

None of the above explains when UPDATE may set shared next-key or gap locks, while in reality this happens, see "How to repeat".

How to repeat:
1. Create a table with a single row in it, like this:

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 server uses secondary index while reading all the rows in the table:

mysql> explain select * from tt;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
|  1 | SIMPLE      | tt    | index | NULL          | c1   | 5       | NULL |
1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
1 row in set (0.00 sec)

You may start wondering why is this so, but it's a separate topic discussed elsewhere. In general (not sure about this case) it makes perfect sense.

3. Enable InnoDB lock monitor to get information about locks added to the error log, for example using new 5.6 feature for this:

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, perform update like below and make sure server gets enough time to output information about locks to the error log:

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

mysql> update tt set id=id+1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

Now, in the error log you'll find something like this:

---TRANSACTION 36797, ACTIVE 21 sec
6 lock struct(s), heap size 1184, 7 row lock(s), undo log entries 2
MySQL thread id 6, OS thread handle 0x1ec8, query id 195 localhost ::1 root User sleep
select sleep(20)
TABLE LOCK table `test`.`tt` trx id 36797 lock mode IX
RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 36797 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 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000008fbd; asc       ;;
 2: len 7; hex 660000019a2152; asc f    !R;;
 3: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 242 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 36797 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000008fbd; asc       ;;
 2: len 7; hex e6000001f80110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 242 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 36797 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 242 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 36797 lock mode S
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 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 242 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 36797 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

Note next-key shared (S) locks on key c1, on record (1,1) and supremum, as well as a shared (S) gap lock on a gap before record (2,1), the result of update. Now, try to find explanation for any of these shared locks on the manual page mentioned in this bug report.

Suggested fix:
Explain in details all locks that are actually set by UPDATE statement while data are read by secondary index, at default (REPEATABLE-READ) isolation level at least...
[18 Feb 2014 11:38] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[3 Mar 2014 16:37] Valeriy Kravchuk
Corrected tag.
[9 Mar 2016 7:52] Andrii Nikitin
Posted by developer:
 
First quote in bug description mentions that UPDATE uses 'next key locks' and 'gap locks' , which are explained in details at https://dev.mysql.com/doc/en/innodb-record-level-locks.html 

So, from formal point of view - this report may be considered as 'Not a bug', unless I am missing something.
[9 Mar 2016 7:57] Valeriy Kravchuk
I'd still prefer for "shared next key" lock to be explained explicitly.
[9 Mar 2016 9:47] Andrii Nikitin
Posted by developer:
 
As discussed privately we cam to following conclusions:
1. next key locking *is* mentioned in innodb-locks-set.html (see first quote in bug description)
2. next key locking is explained in dedicated page innodb-record-level-locks.html 
3. In example above: next key lock is X on PRIMARY key because "UPDATE sets an exclusive next-key lock on every record the search encounters. "
4. In example above: next key lock is S on c1 index because index wasn't used to find record.

Summary: innodb-record-level-locks.html probably should explicitly clarify why exactly #4 happens. (I.e. when "next key" is shared and when it is exclusive).
[19 Oct 2016 16:55] Daniel Price
Posted by developer:
 
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

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;

INSERT INTO tt values(1,1);

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

mysql> begin work;

mysql> update tt set id=id+1;

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

------------
TRANSACTIONS
------------
Trx id counter 4622
Purge done for trx's n:o < 4622 undo n:o < 0 state: running but idle
History list length 104
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4617, ACTIVE 109 sec
6 lock struct(s), heap size 1184, 7 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f6358e66700, query id 18 localhost root cleaning up

TABLE LOCK table `test`.`tt` trx id 4617 lock mode IX

RECORD LOCKS space id 48 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 4617 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 6; hex 000000001209; asc       ;;
 2: len 7; hex 080000013a0727; asc     : ';;
 3: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 48 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 4617 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000001209; asc       ;;
 2: len 7; hex 88000001980110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 48 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 4617 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 48 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 4617 lock mode S
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 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 48 page no 4 n bits 72 index `c1` of table `test`.`tt` trx id 4617 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;; 

Lock Order:

1. LOCK_X PRIMARY: heap no 1			--for delete
2. LOCK_X PRIMARY: heap no 2(supremum)		--treated as a GAP
3. LOCK_X PRIMARY: heap no 3(inherit)		--for insert
4. LOCK_X SECONDARY: heap no 2(implicit)	--for delete
5. LOCK_S SECONDARY: heap no 1			--for duplicate check scan
6. LOCK_S SECONDARY: heap no 2			--for duplicate check scan
7. LOCK_S SECONDARY: heap no 3(inherit)		--for insert
[19 Oct 2016 17:28] Daniel Price
Posted by developer:
 
The following information was added:

"When UPDATE modifies a clustered index record, implicit locks are taken
on affected secondary index records. The UPDATE operation also takes
shared locks on affected secondary index records when performing duplicate
check scans prior to inserting new secondary index records, and when
inserting new secondary index records."

http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

The change should appear online soon.

Thank you for the bug report.