Bug #42491 INSERT on temporary table may pass incorrect lock type to storage engine
Submitted: 30 Jan 2009 19:44 Modified: 30 Jan 2009 20:28
Reporter: Tim Clark Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S2 (Serious)
Version:5.1.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: external_lock, insert, temporary table

[30 Jan 2009 19:44] Tim Clark
Description:
The following sequence of statements will cause a lock_type of F_RDLCK to be passed to the handler::external_lock() function for the final INSERT statement. The correct value for lock_type should be F_WRLCK, since the statement is modifying table t4. The incorrect value is a problem for the IBMDB2I storage engine, since it relies on lock_type to optimize its I/O.

CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
INSERT INTO t3 VALUES (1,'1');
create temporary table t4 select * from t3;
insert into t3 select * from t4;
insert into t4 values(1,"blah");

How to repeat:
Set the default storage engine to InnoDB.
Set a breakpoint in ha_innodb::external_lock().
Execute the statements given above.
Observe that the final INSERT statement calls external_lock with lock_type=F_RDLCK.

Suggested fix:
Always pass F_WRLCK to external_lock when inserting into a table.
[30 Jan 2009 20:03] Tim Clark
As far as I can tell, this bug does not appear in 5.1.26.
[30 Jan 2009 20:21] Davi Arnaut
Duplicate of Bug#41348?
[30 Jan 2009 20:28] Tim Clark
Ah, yes... somehow I missed that one when I searched. This can be closed as a dup of Bug#41348.