Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
Submitted: 20 Jul 2007 11:20 Modified: 2 Aug 2007 3:50
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.0, 5.1, 5.2 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any

[20 Jul 2007 11:20] Ingo Strüwing
Description:
Initially detected in 5.2. Test case translock_innodb fails. The test checks if transactional lock table is propagated to the tables used in triggers of the locked tables. It turned out to be broken.

But it is also repeatable with standard LOCK TABLE. The below test case is for 5.2. But it can also be used for 5.0 if the wait loop is replaced by --sleep 1.

The test case, as it is shown below, produces the error:
query 'INSERT INTO t1 VALUES (1002)' failed: 1100: Table 't2' was not locked with LOCK TABLES
(This is the INSERT before UNLOCK TABLES).

How to repeat:
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c2 INT);
CREATE TABLE t3 (c3 INT);
INSERT INTO t1 VALUES (1001);
INSERT INTO t2 VALUES (2001);
DELIMITER //;
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t2 SET c2= c2 + 110;
END//
DELIMITER ;//
--echo # Take a table lock on t1.
--echo # This should pre-lock t2 through the trigger.
LOCK TABLE t1 WRITE;
    --echo # connection conn1.
    connect (conn1,localhost,root,,);
    --echo # Try to use t2, which is pre-locked through the trigger on t1.
    --echo # The statement should block until UNLOCK TABLES.
    --echo # Contents of t3 shows when INSERT SELECT ran.
    send INSERT INTO t3 SELECT c2 as c3 FROM t2;
--echo # connection default.
connection default;
--echo # Wait for the helper thread to sit on its lock.
while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
        WHERE STATE LIKE '%lock%'
           OR COMMAND LIKE '%sleep%' -- delete this line when fixed
       `)
{
  #query_vertical SELECT COMMAND,STATE,INFO FROM INFORMATION_SCHEMA.PROCESSLIST;
  --sleep 0.1
}
INSERT INTO t1 VALUES (1002);
UNLOCK TABLES;
    --echo # connection conn1.
    connection conn1;
    reap;
    UNLOCK TABLES;
--echo # connection default.
connection default;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
DROP TRIGGER t1_ai;
DROP TABLE t1, t2, t3;
[27 Jul 2007 14:56] 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/31712

ChangeSet@1.2479, 2007-07-27 16:56:29+02:00, thek@adventure.(none) +5 -0
  Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
  
  When a table was explicitly locked with LOCK TABLES no associated
  tables from any related trigger on the subject table were locked.
  As a result of this the user could experience unexpected locking
  behavior and statement failures similar to "failed: 1100: Table'xx'
  was not locked with LOCK TABLES".
  
  This patch fixes this problem by making sure triggers are
  pre-loaded on any statement if the subject table was explicitly
  locked with LOCK TABLES.
[1 Aug 2007 23:26] Bugs System
Pushed into 5.0.48
[1 Aug 2007 23:27] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 3:50] Paul DuBois
Noted in 5.0.48, 5.1.21 changelogs.

OCK TABLES did not pre-lock tables used in triggers of the locked
tables. Unexpected locking behavior and statement failures similar to
failed: 1100: Table 'xx' was not locked with LOCK TABLES could
result.