Bug #21096 locking issue ; temporary table conflicts.
Submitted: 17 Jul 2006 15:45 Modified: 5 Oct 2006 15:21
Reporter: Jeff C Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:4.1, 5.0.25-BK, 5.1.12-BK, 5.1.11 OS:Linux (Linux, RHEL4)
Assigned to: Tomash Brechko CPU Architecture:Any

[17 Jul 2006 15:45] Jeff C
Description:
Creating a temporary table with the same name of an existing table can result in locking issues.

How to repeat:
You need 2 sessions

SESSION (1)

 use test;
 create table testing (id int primary key);
 insert into testing (id) values (1),(2),(3),(4),(5);
 select * from testing;
 lock table testing write;

SESSION (2)

 use test;
 create temporary table testing (id int primary key);
 insert into testing (id) values (6),(7),(8),(9),(10);
 select * from testing;
 -- This will create the locking issue
 drop temporary table testing; 

SESSION (1)

 -- You can see the temporary table session is: waiting for table
 show processlist;   
 -- This will unlock the temporary table session.
 unlock tables;
[17 Jul 2006 16:41] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, also - with 5.0.25-BK on Linux.
[3 Aug 2006 15:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10015

ChangeSet@1.2530, 2006-08-03 19:18:40+04:00, kroki@moonlight.intranet +3 -0
  BUG#21096: locking issue ; temporary table conflicts.
  
  The problem was that during DROP TEMPORARY TABLE we tried to acquire
  the name lock, though temporary tables belongs to one connection, and
  no race is possible.
  
  The solution is to not use table name locking while executing
  DROP TEMPORARY TABLE.
[3 Aug 2006 16:18] Tomash Brechko
This bug is related to bug#21431.
[24 Aug 2006 23:04] Konstantin Osipov
Approved by email with a few comments.
[29 Aug 2006 13:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11003

ChangeSet@1.2530, 2006-08-29 16:59:20+04:00, kroki@moonlight.intranet +3 -0
  BUG#21096: locking issue ; temporary table conflicts.
  
  The problem was that during DROP TEMPORARY TABLE we tried to acquire
  the name lock, though temporary tables belongs to one connection, and
  no race is possible.
  
  The solution is to not use table name locking while executing
  DROP TEMPORARY TABLE.
[29 Aug 2006 14:54] Tomash Brechko
Queued to 4.1-runtime.
[3 Oct 2006 19:35] Dmitry Lenev
Fixed in 4.1.22, 5.0.27 and 5.1.12.
[5 Oct 2006 15:21] Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

Creating a TEMPORARY table with the same name as an existing table
that was locked by another client could result in a lock conflict for
DROP TEMPORARY TABLE because the server unnecessarily tried to
acquire a name lock.
[5 Oct 2006 15:22] Paul DuBois
Sorry, 5.0.27 changelog, not 5.0.26.
[25 Oct 2006 16:43] Paul DuBois
The 5.0.x fix is in 5.0.30.