Bug #71916 Manual does NOT explain locks set for UPDATE ... WHERE PK='const' properly
Submitted: 3 Mar 2014 16:52 Modified: 26 Oct 2016 13:43
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: missing manual, next-key lock, record lock, UPDATE

[3 Mar 2014 16:52] 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:

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

So, "normally next-key locks" in the first quote assumes that they may be NOT next-key. But is it when isolation level is lower than REPEATABLE-READ or in some other cases? Totally NOT clear.

Consider the following case:

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from t;
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)

Now, what kind of lock is set when we do:

update t set a='c' where a='a';

in transaction? One may assume from the manual that it's next-key lock, but note this:

mysql> explain select * from t where a='a';
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   |
 rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------------+
|  1 | SIMPLE      | t     | const | PRIMARY       | PRIMARY | 257     | const |
    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+
------+-------------+
1 row in set (0.00 sec)

So, we access single row (as type is "const" and we use PRIMARY KEY). why next-key lock here? There can be no phantoms caused by inserting into the gap before this record. Lock monitor of any kind proves this:

---TRANSACTION 37416, ACTIVE 71 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 0x18ec, query id 101 localhost ::1 root cleaning up
TABLE LOCK table `test`.`t` trx id 37416 lock mode IX
RECORD LOCKS space id 250 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 37416 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 6; hex 000000009228; asc      (;;
 2: len 7; hex 98000002f30110; asc        ;;

Note "lock_mode X locks rec but not gap" - this is a record lock. Next-key lock looks like this:

RECORD LOCKS space id 250 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 37416 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 3; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 6; hex 000000009228; asc      (;;
 2: len 7; hex 1800000145307b; asc     E0{;;
...

Note "lock_mode X" - this is a next-key lock.

How to repeat:
Execute the following:

CREATE TABLE `t` (
`a` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`a`)
) ENGINE=InnoDB;
insert into t set a='a';

Then enable lock monitor and do the following:

begin work;
update t set a='c' where a='a';
select sleep(20);

Then disable lock monitor and try to explain content of the error log:

---TRANSACTION 37427, ACTIVE 68 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 0x18ec, query id 120 localhost ::1 root cleaning up
TABLE LOCK table `test`.`t` trx id 37427 lock mode IX
RECORD LOCKS space id 250 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 37427 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 6; hex 000000009233; asc      3;;
 2: len 7; hex 1e000001fa1e2e; asc       .;;

RECORD LOCKS space id 250 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 37427 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 63; asc c;;
 1: len 6; hex 000000009233; asc      3;;
 2: len 7; hex 1e000001fa1e4a; asc       J;;

based on the manual.

Suggested fix:
Explain when exactly next-key locks are set for UPDATE ... WHERE with default transaction isolation level. Definitely there is a case when they are NOT set, as I've demonstrated above.

See also my older bug #71736 for other case to explain.
[4 Mar 2014 9:37] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[26 Oct 2016 13:43] Daniel Price
Posted by developer:
 
If the WHERE condition matches exactly one row, and the search can use an associated unique index, only a row lock is necessary. 

Updates were made to the following page:
http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Changes should appear online soon.

Thank you for the bug report.