Bug #72858 EXPLAIN .. SELECT .. FOR UPDATE takes locks
Submitted: 3 Jun 2014 22:18 Modified: 26 Feb 2016 13:49
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.17, 5.6.20, 5.7.5 OS:Any
Assigned to: 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] Umesh Shastry
Hello Justin,

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

Thanks,
Umesh
[4 Jun 2014 4:41] Umesh Shastry
// 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.