If you lock a table explicitly with
TABLES, any tables used in triggers are also locked
The locks are taken as the same time as those acquired explicitly with the
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,
t2, using this statement:
LOCK TABLES t1 WRITE, t2 READ;
t2 have any
triggers, tables used within the triggers will also be locked.
t1 has a trigger defined like
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
statement is that
t2 are locked because they appear in the
are locked because they are used within the trigger:
t1is locked for writing per the
t2is locked for writing, even though the request is for a
READlock. This occurs because
t2is inserted into within the trigger, so the
READrequest is converted to a
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.