MySQL Bugs: #55065: LOCK TABLES ... READ does not lock tables used in triggers

Bug #55065	LOCK TABLES ... READ does not lock tables used in triggers
Submitted:	8 Jul 2010 2:33	Modified:	8 Jul 2010 8:11
Reporter:	Andrew Dalgleish	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From this page:
http://dev.mysql.com/doc/refman/5.1/en/lock-tables-and-triggers.html
-----
If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly ...
-----
This works ok if you use LOCK TABLES... WRITE, but not LOCK TABLES... READ.

How to repeat:
USE test;
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
DROP TRIGGER IF EXISTS trigger1;

CREATE TABLE table1 ( col1 INT);
CREATE TABLE table2 ( col1 INT);

CREATE TRIGGER trigger1 AFTER INSERT ON table1
FOR EACH ROW INSERT INTO table2 (col1) VALUES (NEW.col1);

-- connection 1
LOCK TABLES table1 READ;

-- connection 2 (succeeds but should not)
INSERT INTO table2 VALUES (1);

Possibly an incomplete fix for bug 29929

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS table2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TRIGGER IF EXISTS trigger1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE table1 ( col1 INT);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE table2 ( col1 INT);
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> CREATE TRIGGER trigger1 AFTER INSERT ON table1
    -> FOR EACH ROW INSERT INTO table2 (col1) VALUES (NEW.col1);
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> -- connection 1
mysql> LOCK TABLES table1 READ;
Query OK, 0 rows affected (0.00 sec)

Your MySQL connection id is 2
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO table2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql>

It's not supposed to.

It's not supposed to.

The tables that are used implicitly are only locked when they may become necessary to satisfy queries against the explicitly locked tables.

I.e. if you LOCK TABLE v1 READ, tables used in view v1 are locked to allow various READ operations with v1. This is so-called pre-locking, to avoid possible deadlocks when trying to lock underlying tables down the road.
With triggers, triggers are not executed on any read operations, i.e. tables
used in triggers are not needed for SELECTs, hence they are not locked.

It's a rule of thumb to explicitly LOCK TABLE everything that needs to be locked, and not expect the server to do it. Implicit pre-locking algorithm may change in future or go away completely.

To see the changes between 5.1 and 5.5 in pre-locking try this:

create table t1 (a int);
create function f1() returns int return select max(a) from t1;
create view v1 as select f1() as a;
lock table v1 write;
drop table t1; <-- succeeds in 5.1, gives "TABLE NOT LOCKED" in 5.5.