LOCK TABLES and UNLOCK TABLES Statements 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. If you lock a table explicitly with  LOCK TABLES , any tables used in triggers are also locked implicitly: Suppose that you lock two tables,  t1  and  t2 , using this statement: If  t1  or  t2  have any triggers, tables used within the triggers are also locked. Suppose that  t1  has a trigger defined like this: 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: 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. 2730 • 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. Table-Locking Restrictions and Conditions 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; LOCK TABLES t1 WRITE, t2 READ; •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.) •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. •The locks are taken as the same time as those acquired explicitly with the  LOCK TABLES  statement. • ROLLBACK  does not release table locks. LOCK TABLES and Triggers