COMMIT; UNLOCK TABLES; 

When you call LOCK TABLES , InnoDB  internally takes its own table lock, and MySQL takes its own table lock. InnoDB  releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES . You should not have autocommit = 1 , because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES , and deadlocks can very easily happen. InnoDB  does not acquire the internal table lock at all if autocommit = 1 , to help old applications avoid unnecessary deadlocks. 

## LOCK TABLES and Triggers 

• ROLLBACK  does not release table locks. 

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 are also 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. 

## Table-Locking Restrictions and Conditions 

• 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. 

You can safely use KILL  to terminate a session that is waiting for a table lock. See Section 13.7.8.4, “KILL Statement” . 

LOCK TABLES  and UNLOCK TABLES  cannot be used within stored programs. 

