Bug #46678 Transactional locks do not protect a table from concurrent DROP or RENAME
Submitted: 12 Aug 2009 17:21 Modified: 13 Oct 2010 17:07
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version: OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[12 Aug 2009 17:21] Elena Stepanova
Description:
If an InnoDB table is locked with IN [SHARE|EXCLUSIVE] MODE, it can still be dropped or renamed freely by another connection.

How to repeat:
# t/drop_rename_in_txn_mode.test:

--source include/have_innodb.inc

use test;

--disable_warnings
drop table if exists tbl_to_rename, tbl_to_drop;
--enable_warnings

create table tbl_to_rename ( i int ) engine = innodb;
create table tbl_to_drop ( i int ) engine = innodb;

set autocommit = 0;
lock tables tbl_to_rename in exclusive mode, tbl_to_drop in exclusive mode;

connect(con1,localhost,root,,test);

connection con1;

rename table tbl_to_rename to tbl_renamed;
drop table tbl_to_drop;

connection default;

--error 1146
select * from tbl_to_rename;
--error 1146
select * from tbl_to_drop;
[3 Sep 2009 13:59] Ingo Strüwing
Transactional table locks are like transactional row locks. If DDLs block on transactional row locks, they should also block on transactional table locks. If they don't block on transactional row locks, they shouldn't block on transactional table locks either.

Transactional locks are handled entirely within the storege engine. If this needs to be fixed, I suggest to assign to InnoDB (provided that InnoDB is involved in every DDL on its tables and can thus block the DDL).
[23 Sep 2009 12:38] 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/84347

2836 Jon Olav Hauglid	2009-09-23
      Bug #46678 Transactional locks do not protect a table from 
                 concurrent DROP or RENAME 
      
      The problem was that transactional LOCK TABLE did not protect 
      the table(s) from DDL operations issued from other connections. 
      Transactional LOCK TABLE is implemented using InnoDB table 
      level locks. But by design, InnoDB ignores these locks for DDL 
      operations.
      
      This patch changes the implementation of transactional LOCK TABLE
      so that affected tables are also kept locked using MDL (MDL_SHARED).
      Before, LOCK TABLE released it's MDL locks at the end of the 
      statement.
      
      Test case added to locktrans_innodb.test.
[24 Sep 2009 13:57] 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/84506

2838 Jon Olav Hauglid	2009-09-24
      Bug #46678 Transactional locks do not protect a table from 
                 concurrent DROP or RENAME 
      
      The problem was that transactional LOCK TABLE did not protect 
      the table(s) from DDL operations issued from other connections. 
      Transactional LOCK TABLE is implemented using InnoDB table 
      level locks. But by design, InnoDB ignores these locks for DDL 
      operations.
      
      This patch changes the implementation of transactional LOCK TABLE
      so that affected tables are also kept locked using MDL (MDL_SHARED).
      Before, LOCK TABLE released it's MDL locks at the end of the 
      statement.
      
      Test case added to locktrans_innodb.test.
[24 Sep 2009 15:01] 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/84522

2838 Jon Olav Hauglid	2009-09-24
      Bug #46678 Transactional locks do not protect a table from 
                 concurrent DROP or RENAME 
      
      The problem was that transactional LOCK TABLE did not protect 
      the table(s) from DDL operations issued from other connections. 
      Transactional LOCK TABLE is implemented using InnoDB table 
      level locks. But by design, InnoDB ignores these locks for DDL 
      operations.
      
      This patch changes the implementation of transactional LOCK TABLE
      so that affected tables are also kept locked using MDL (MDL_SHARED).
      Before, LOCK TABLE released it's MDL locks at the end of the 
      statement.
      
      Test case added to locktrans_innodb.test.
[25 Sep 2009 10:49] 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/84611

2840 Jon Olav Hauglid	2009-09-25
      Bug #46678 Transactional locks do not protect a table from 
                 concurrent DROP or RENAME 
      
      The problem was that transactional LOCK TABLE did not protect 
      the table(s) from DDL operations issued from other connections. 
      Transactional LOCK TABLE is implemented using InnoDB table 
      level locks. But by design, InnoDB ignores these locks for DDL 
      operations.
      
      This patch changes the implementation of transactional LOCK TABLE
      so that affected tables are also kept locked using MDL (MDL_SHARED).
      Before, LOCK TABLE released it's MDL locks at the end of the 
      statement.
      
      Test case added to locktrans_innodb.test.
[25 Sep 2009 10:50] Jon Olav Hauglid
Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha)
[30 Sep 2009 8:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090927203924-087s36mrs0uxepwb) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 0:11] Paul DuBois
Noted in 6.0.14 changelog.

Transactional locks acquired with LOCK TABLE did not protect InnoDB
tables from DDL operations initiated from other sessions.

Setting report to NDI pending push into 5.4.x.
[28 Apr 2010 10:38] 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/106791

3134 Ingo Struewing	2010-04-28
      Backport of revid:jon.hauglid@sun.com-20090925104844-8tsal7dxq3rndaey
        Bug #46678 Transactional locks do not protect a table from 
                   concurrent DROP or RENAME 
        
        The problem was that transactional LOCK TABLE did not protect 
        the table(s) from DDL operations issued from other connections. 
        Transactional LOCK TABLE is implemented using InnoDB table 
        level locks. But by design, InnoDB ignores these locks for DDL 
        operations.
        
        This patch changes the implementation of transactional LOCK TABLE
        so that affected tables are also kept locked using MDL (MDL_SHARED).
        Before, LOCK TABLE released it's MDL locks at the end of the 
        statement.
        
        Test case added to locktrans_innodb.test.
[13 Oct 2010 15:59] Konstantin Osipov
Triage: this should be SRFEATURE since it's specific to WL#3561.
The bug is not present in 5.5, only in a feature tree.
Need Doc Info is not a correct state either, since the bug is not present in any public 5.5 version.
[13 Oct 2010 16:00] Konstantin Osipov
Triage: please close the bug (since it's fixed) after setting the correct version number.