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: | |
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
[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.