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
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:
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
t2 is inserted into within the
trigger, so the
READ request is converted
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.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices