MySQL Bugs: #46664: NOWAIT in SHARE mode causes immediate timeout although the table is not locked

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

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

EXCLUSIVE mode with NOWAIT clause causes error 1112 (Table 't' uses an extension that doesn't exist in this MySQL version).

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

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.

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.

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.

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)

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.

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.

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.