If you lock a table explicitly with LOCK
TABLES
, any tables used in triggers are also locked
implicitly:
The locks are taken as the same time as those acquired explicitly with the
LOCK TABLES
statement.The lock on a table used in a trigger depends on whether the table is used only for reading. If so, a read lock suffices. Otherwise, a write lock is used.
If a table is locked explicitly for reading with
LOCK TABLES
, but needs to be locked for writing because it might be modified within a trigger, a write lock is taken rather than a read lock. (That is, an implicit write lock needed due to the table's appearance within a trigger causes an explicit read lock request for the table to be converted to a write lock request.)
Suppose that you lock two tables, t1
and
t2
, using this statement:
LOCK TABLES t1 WRITE, t2 READ;
If t1
or t2
have any
triggers, tables used within the triggers will also be locked.
Suppose that t1
has a trigger defined like
this:
CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW BEGIN UPDATE t4 SET count = count+1 WHERE id = NEW.id AND EXISTS (SELECT a FROM t3); INSERT INTO t2 VALUES(1, 2); END;
The result of the LOCK TABLES
statement is that t1
and
t2
are locked because they appear in the
statement, and t3
and t4
are locked because they are used within the trigger:
t1
is locked for writing per theWRITE
lock request.t2
is locked for writing, even though the request is for aREAD
lock. This occurs becauset2
is inserted into within the trigger, so theREAD
request is converted to aWRITE
request.t3
is locked for reading because it is only read from within the trigger.t4
is locked for writing because it might be updated within the trigger.