Bug #85557 | Manual does not explain locks set by UPDATE with subquery referring other table | ||
---|---|---|---|
Submitted: | 21 Mar 2017 8:31 | Modified: | 15 May 2018 14:19 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | OS: | Any | |
Assigned to: | CPU Architecture: | Any | |
Tags: | innodb, locks, missing manual |
[21 Mar 2017 8:31]
Valeriy Kravchuk
[21 Mar 2017 12:21]
Valeriy Kravchuk
Surely, when isolation level is read committed we do not have any locks on table we read from: mysql> set transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update tb set c2 = 3 where c1 in (select c1 from ta); Query OK, 1 row affected (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 0 mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2017-03-21 14:19:49 0x7ff540537700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 14316 srv_idle srv_master_thread log flush and writes: 14323 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 24 OS WAIT ARRAY INFO: signal count 24 RW-shared spins 0, rounds 39, OS waits 19 RW-excl spins 0, rounds 30, OS waits 1 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 39.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 1305 Purge done for trx's n:o < 1303 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1304, ACTIVE 6 sec 2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 6, OS thread handle 140691322926848, query id 45 localhost root starting show engine innodb status TABLE LOCK table `test`.`tb` trx id 1304 lock mode IX RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`tb` trx id 1304 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000518; asc ;; 2: len 7; hex 330000013c0110; asc 3 < ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000003; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000000518; asc ;; 2: len 7; hex 330000013c0131; asc 3 < 1;; 3: len 4; hex 80000003; asc ;; 4: len 4; hex 80000003; asc ;; -------- ...
[21 Mar 2017 19:01]
MySQL Verification Team
Hi Valerii, There is a very good reason why these locks need to be set, so this is not Storage Engine bug. However, I do agree that it is not documented well. Verified as 'Documentation" bug.
[15 May 2018 14:07]
Daniel Price
Posted by developer: Bug #84492, which is referenced above, is a duplicate of https://bugs.mysql.com/bug.php?id=78412.
[15 May 2018 14:08]
Daniel Price
Posted by developer: Bug #84492, which is referenced above, is a duplicate of Bug #78412.
[15 May 2018 14:19]
Daniel Price
Posted by developer: The "Locks Set by Different SQL Statements in InnoDB" section now states: "When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s." https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html Thank you for the bug report.