• REPLACE  is done like an INSERT  if there is no collision on a unique key. Otherwise, an exclusive next- key lock is placed on the row to be replaced. 

• INSERT INTO T SELECT ... FROM S WHERE ...  sets an exclusive index record lock (without a gap lock) on each row inserted into T . If the transaction isolation level is READ COMMITTED , InnoDB does the search on S  as a consistent read (no locks). Otherwise, InnoDB  sets shared next-key locks on rows from S . InnoDB  has to set locks in the latter case: During roll-forward recovery using a statement- based binary log, every SQL statement must be executed in exactly the same way it was done originally. 

CREATE TABLE ... SELECT ...  performs the SELECT  with shared next-key locks or as a consistent read, as for INSERT ... SELECT . 

When a SELECT  is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ...  or UPDATE t ... WHERE col IN (SELECT ... FROM s ...) , InnoDB  sets shared next-key locks on rows from table s . 

• InnoDB  sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT  column while initializing a previously specified AUTO_INCREMENT  column on a table. 

With innodb_autoinc_lock_mode=0 , InnoDB  uses a special AUTO-INC  table lock mode where the lock is obtained and held to the end of the current SQL statement (not to the end of the entire transaction) while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC  table lock is held. The same behavior occurs for “bulk inserts”  with innodb_autoinc_lock_mode=1 . Table-level AUTO-INC  locks are not used with innodb_autoinc_lock_mode=2 . For more information, See Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB” . 

InnoDB  fetches the value of a previously initialized AUTO_INCREMENT  column without setting any locks. 

•If a FOREIGN KEY  constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB  also sets these locks in the case where the constraint fails. 

• LOCK TABLES  sets table locks, but it is the higher MySQL layer above the InnoDB  layer that sets these locks. InnoDB  is aware of table locks if innodb_table_locks = 1  (the default) and autocommit = 0 , and the MySQL layer above InnoDB  knows about row-level locks. 

Otherwise, InnoDB ’s automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity, as discussed in Section 15.7.5.2, “Deadlock Detection” . 

• LOCK TABLES  acquires two locks on each table if innodb_table_locks=1  (the default). In addition to a table lock on the MySQL layer, it also acquires an InnoDB  table lock. To avoid acquiring InnoDB  table locks, set innodb_table_locks=0 . If no InnoDB  table lock is acquired, LOCK TABLES  completes even if some records of the tables are being locked by other transactions. 

In MySQL 8.0, innodb_table_locks=0  has no effect for tables locked explicitly with LOCK TABLES ... WRITE . It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE  implicitly (for example, through triggers) or by LOCK TABLES ... READ . 

•All InnoDB  locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES  on InnoDB  tables in autocommit=1  mode because the acquired InnoDB  table locks would be released immediately. 

•You cannot lock additional tables in the middle of a transaction because LOCK TABLES  performs an implicit COMMIT  and UNLOCK TABLES . 

