Bug #72858 EXPLAIN .. SELECT .. FOR UPDATE takes locks

• Submitted: 3 Jun 2014 22:18
• Modified: 26 Feb 2016 13:49
• Reporter: Justin Swanhart
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.6.17, 5.6.20, 5.7.5
• OS: Any
• CPU Architecture: Any

[3 Jun 2014 22:18] Justin Swanhart

Description:
CREATE TABLE t1(c1 int primary key);
insert into t1 values (1),(2),(3);
start transaction;
explain select * from t1 where c1 = 1 FOR UPDATE;

Now look in SHOW ENGINE INNODB STATUS and you will find the transaction is holding one row lock:
---TRANSACTION 81442, ACTIVE 4 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f8ff8691700, query id 41 localhost root init
show engine innodb status

How to repeat:
see above

Suggested fix:
Ignore FOR UPDATE in the context of EXPLAIN.  EXPLAIN should never hold locks.

[4 Jun 2014 4:39] MySQL Verification Team

Hello Justin,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[4 Jun 2014 4:41] MySQL Verification Team

// 5.6.20

---TRANSACTION 1948, ACTIVE 6 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 225, OS thread handle 0x7f5b0a0af700, query id 285 localhost root init
show engine innodb status

[26 Feb 2016 13:49] Paul DuBois

Noted in 5.6.30, 5.7.12, 5.8.0 changelogs.

EXPLAIN for SELECT ... FOR UPDATE statements acquired locks.