Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the table is not locked
Submitted: 12 Aug 2009 3:10 Modified: 13 Oct 2009 13:13
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.4.4-alpha OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any

[12 Aug 2009 3:10] Elena Stepanova
Description:
An attempt to lock an InnoDB table in share mode with InnoDB clause instantly produces error 1205 (lock wait timeout exceeded), even if there are no connections that could have used the table. 

How to repeat:
USE test;
DROP TABLE IF EXISTS share_mode_nowait_test;
CREATE TABLE share_mode_nowait_test ( i INT ) ENGINE = InnoDB;
SET AUTOCOMMIT = 0;
LOCK TABLES share_mode_nowait_test IN SHARE MODE NOWAIT;

# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[12 Aug 2009 3:14] Elena Stepanova
EXCLUSIVE mode with NOWAIT clause causes error 1112 (Table 't' uses an extension that doesn't exist in this MySQL version).
[3 Sep 2009 13:40] Ingo Strüwing
Please see also: Bug #24500 - InnoDB please implement transactional NOWAIT locks. And search for NOWAIT in the manual: http://dev.mysql.com/doc/refman/5.4/en/lock-tables.html
[9 Sep 2009 22:04] 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/82870

2804 Magne Mahre	2009-09-10
      Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the table is not locked
      
      InnoDb does not currently support transactional locking with NOWAIT.
      See Bug #24500 for reference to the InnoDb feature request.
      
      The handler was changed so a more intelligible error code is returned,
      and a couple of test cases was changed to reflect this.
[16 Sep 2009 9:19] 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/83445

2816 Magne Mahre	2009-09-16
      Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the 
                 table is not locked
            
      InnoDB does not currently support transactional locking with NOWAIT.
      See Bug #24500 for reference to the InnoDB feature request.
            
      The handler was changed so a more intelligible error code is returned,
      and a couple of test cases was changed to reflect this.
[16 Sep 2009 13:32] 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/83487

2861 Magne Mahre	2009-09-16
      Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the 
                 table is not locked
            
      InnoDB does not currently support transactional locking with NOWAIT.
      See Bug #24500 for reference to the InnoDB feature request.
            
      The handler was changed so a more intelligible error code is returned,
      and a couple of test cases was changed to reflect this.
[30 Sep 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)
[13 Oct 2009 13:13] Paul Dubois
Noted in 6.0.14 changelog.

InnoDB does not currently support transactional locking with the
NOWAIT modifier, but the error message indicated a lock wait timeout.
Now it indicates that this operation is unsupported.
[17 Dec 2009 11:05] 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/94712

2933 Ingo Struewing	2009-12-17
      WL#3561 - transactional LOCK TABLE
      Backport.
      Backported revision revid:magne.mahre@sun.com-20090916133123-idqa56e09jiha71g
        Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the 
                   table is not locked
              
        InnoDB does not currently support transactional locking with NOWAIT.
        See Bug #24500 for reference to the InnoDB feature request.
              
        The handler was changed so a more intelligible error code is returned,
        and a couple of test cases was changed to reflect this.
     @ mysql-test/include/locktrans.inc
        WL#3561 - transactional LOCK TABLE
        Backport.
     @ mysql-test/r/locktrans_innodb.result
        WL#3561 - transactional LOCK TABLE
        Backport.
     @ mysql-test/r/rpl_locktrans_innodb.result
        WL#3561 - transactional LOCK TABLE
        Backport.
     @ storage/innobase/handler/ha_innodb.h
        WL#3561 - transactional LOCK TABLE
        Backport.
[18 Dec 2009 18:46] 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/94994

2941 Ingo Struewing	2009-12-18
      WL#3561 - transactional LOCK TABLE
      Backport.
      Backported revision revid:magne.mahre@sun.com-20090916133123-idqa56e09jiha71g
        Bug #46664 NOWAIT in SHARE mode causes immediate timeout although the 
                   table is not locked
              
        InnoDB does not currently support transactional locking with NOWAIT.
        See Bug #24500 for reference to the InnoDB feature request.
              
        The handler was changed so a more intelligible error code is returned,
        and a couple of test cases was changed to reflect this.
      
      This leaves the tree with failing *locktrans* test cases.
      They will be fixed with a post-backport fix.