MySQL Bugs: #9581: LOCK TABLEs is ignored after UNLOCK TABLES if TRIGGERs are involved

Bug #9581	LOCK TABLEs is ignored after UNLOCK TABLES if TRIGGERs are involved
Submitted:	2 Apr 2005 13:10	Modified:	10 Jul 2005 16:17
Reporter:	Jan Kneschke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux/x86)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Triggers and DML work in 5.0.3 if you handle the locking externally.

But only once.

How to repeat:
DELIMITER $$
DROP TABLE IF EXISTS test4$$
CREATE TABLE test4 (
  id INT NOT NULL,
  mtime DATETIME NOT NULL
) engine = innodb$$

DROP TABLE IF EXISTS test5$$
CREATE TABLE test5 (
  id INT NOT NULL
) engine = innodb$$
DROP TRIGGER test5.tau_log_update$$
CREATE TRIGGER tau_log_update
  AFTER UPDATE ON test5 FOR EACH ROW
BEGIN
  INSERT INTO test4
    ( id, mtime ) VALUES
    ( OLD.id, CURRENT_TIMESTAMP);
END$$
DELIMITER ;

## first round is ok
SET @@autocommit = 0;
BEGIN;
## explicit locking for the trigger
## this is currently necessary
LOCK TABLE test4 WRITE, test5 WRITE, mysql.proc READ;
INSERT INTO test5 VALUES ( 2 ), ( 3 );
UPDATE test5 SET id = 1 WHERE id = 2;
SHOW WARNINGS;
SELECT * FROM test4;
# +----+---------------------+
# | id | mtime               |
# +----+---------------------+
# |  2 | 2005-04-02 15:03:03 |
# +----+---------------------+
COMMIT;
UNLOCK TABLES;

## second round fails.

SET @@autocommit = 0;
BEGIN;
## explicit locking for the trigger
## this is currently necessary
LOCK TABLE test4 WRITE, test5 WRITE, mysql.proc READ;
INSERT INTO test5 VALUES ( 2 ), ( 3 );
UPDATE test5 SET id = 1 WHERE id = 2;
SHOW WARNINGS;
(root@localhost) [pasta]> SHOW WARNINGS;
# +-------+------+-----------------------------------------------+
# | Level | Code | Message                                       |
# +-------+------+-----------------------------------------------+
# | Error | 1100 | Table 'test4' was not locked with LOCK TABLES |
# +-------+------+-----------------------------------------------+
SELECT * FROM test4;
# +----+---------------------+
# | id | mtime               |
# +----+---------------------+
# |  2 | 2005-04-02 15:03:03 |
# +----+---------------------+
COMMIT;
UNLOCK TABLES;

Suggested fix:
fix the locking/unlocking.

Hi, Jan!

This bug was fixed in version 5.0.10 by the same patch as bug #8406.

And BTW you don't need to explicitly lock tables used in trigger any longer.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.10 change history; closed.