| 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: | |
| Category: | MySQL Server: Locking | Severity: | S1 (Critical) |
| Version: | 5.0, 5.1, 5.2 | OS: | Any |
| Assigned to: | Kristofer Pettersson | CPU Architecture: | Any |
[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.
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;