Bug #62646 LOCK/UNLOCK in "AUTOCOMMIT = 0 mode" -issue

• Submitted: 6 Oct 2011 18:17
• Modified: 9 Oct 2011 19:28
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.5.16
• OS: Windows (7 - 64 bit)
• CPU Architecture: Any
• Tags: qc

[6 Oct 2011 18:17] Peter Laursen

Description:
This may be the same issue as here (but not sure):
http://bugs.mysql.com/bug.php?id=62641

I was not able to reproduce exactly what Bela reported.  But this looks very similar at least.  Please mark a duplicate if you are able to identify that the problem is the same.

How to repeat:
-- connection 1:

SET AUTOCOMMIT = 0;
USE test;
LOCK TABLES test WRITE;  -- table `test` is an InnoDB table
UNLOCK TABLES;

-- connection 2:

DELIMITER $$

CREATE PROCEDURE `test`.`bluh`()
    BEGIN
    DECLARE a INT;
    SELECT 1 INTO a;
    END $$

DELIMITER ;

-- Error Code: 1192
-- Can't execute the given command because you have active locked tables or an active transaction

-- connection 1;

COMMIT;

-- connection 2 (global setting for AUTOCOMMIT is "0"):

DELIMITER $$

CREATE PROCEDURE `test`.`bluh`()
    BEGIN
    DECLARE a INT;
    SELECT 1 INTO a;
    END $$

DELIMITER ;

-- Error Code: 1192
-- Can't execute the given command because you have active locked tables or an active transaction

Suggested fix:
No clue.

But even first time the SP is attempted created I do not understand a LOCK WRITE+UNLOCK in a transaction on a specific table should prevent creation of a Stored Procedure that does no even reference that table.  And even less I understand why it is not possible after COMMIT in 1st conenction.

Actually I find no other solution than restarting the server to be able to create the SP. But that could be a limitation with my insight ... ! :-)

[7 Oct 2011 11:18] Peter Laursen

Tried MySQL 5.0.90 and 5.1.59 (with 'built-in' InnoDB).  Here the SP creates successfully *in first shot*.

[7 Oct 2011 11:29] Peter Laursen

Now this is getting tricky.  Now I am not able to reproduce on 5.5.16 either.

[9 Oct 2011 19:28] Valeriy Kravchuk

I can't repeat this with 5.5.17 on Mac. Inform us if you'll find some missing detail in sequence of actions that led to problem.