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 TABLESstatement.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:
t1is locked for writing per theWRITElock request.t2is locked for writing, even though the request is for aREADlock. This occurs becauset2is inserted into within the trigger, so theREADrequest is converted to aWRITErequest.t3is locked for reading because it is only read from within the trigger.t4is locked for writing because it might be updated within the trigger.