Skip navigation links
12.3.5.3 Other Table-Locking Notes »
Section Navigation      [Toggle]

12.3.5.2. LOCK TABLES and Triggers

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 the WRITE lock request.

  • t2 is locked for writing, even though the request is for a READ lock. This occurs because t2 is inserted into within the trigger, so the READ request is converted to a WRITE 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.