InnoDB table level lock with Lock table statement relationship

DDL statements only request table locks at the intent level. In the lock table statement, MySQL will request a MySQL level table lock, and InnoDB will also request a INNODB level table lock. The premise is Innodb_table_locks=1.

https://www.percona.com/blog/2012/07/31/innodb-table-locks/

MySQL table level Locks and Innodb table levellocks is separate beings. You almost never would run into problems withinnodb table level locks because InnoDB would only set intentional level lo Cks for Everythingby DDL operations. If you ' re has locking issues with innodbchances is it's row level locks or auto increment table level lock (Mostlywit H MySQL 5.0 and older MySQL versions). MySQL level Locks is entirelydifferent story. Explicitly locking tables on MySQL level would prevent tablesfrom being accessed and won't show up in show ENGINE INNODB STATUS. It is agood practice does not have TABLES when you ' re using Innodb TABLES.

http://blog.csdn.net/wanghai__/article/details/6724825

Http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

A new startup option named Innodb_table_locks was added thatcauses LOCK table to

also acquire InnoDB table locks. This option is enabled by default. This can cause deadlocks the Applicationsthat use Autocommit=1 and LOCK TABLES. If you application encounters deadlocksafter upgrading, you may need to add innodb_table_locks=0 to your my.cnf file.

Beginning with MySQL 4.0.20 and 4.1.2, Themysql locktables Operation acquires

locks on each table if Innodb_table_locks=1. (1 is the default.) In addition to a table lock

On the MySQL layer, it also acquires Aninnodb table lock. Older versions of MySQL do not

Acquire InnoDB table locks. Beginning withmysql 4.0.22 and 4.1.7, the old behavior can be

Selected by setting Innodb_table_locks=0.if no InnoDB table lock is acquired, lock

TABLES completes even if some records ofthe TABLES is being locked by other transactions.

All InnoDB locks held to a transaction arereleased when the transaction is committed or aborted.

Thus, it does not do much sense to Invokelock TABLES on InnoDB TABLES in autocommit=

1 mode, because the acquired InnoDB Tablelocks would be released immediately.

? Sometimes it would is useful to lock further tables in the Courseof a transaction. Unfortunately,

LOCK TABLES in MySQL performs an implicitcommit and UNLOCK TABLES. An InnoDB

The variant of the LOCK TABLES has been plannedthat can is executed in the middle of a transaction.

Locking and transactions

LOCK TABLES acquires and locks on Eachtable if Innodb_table_locks=1 (the default). In addition to a table lock on themysql layer, it also acquires an InnoDB table lock. Versions of MySQL before4.1.2 did not acquire InnoDB table locks; The old behavior can be selected Bysetting innodb_table_locks=0. If no InnoDB table lock is acquired, lock tablescompletes even if some records of the tables be being locked by other TRA Nsactions.

In MySQL 5.6, innodb_table_locks=0 have noeffect for tables locked explicitly with LOCK tables ... WRITE. It does has aneffect 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 to a transaction arereleased when the transaction is committed or aborted. Thus, it does not makemuch sense to invoke LOCK TABLES on InnoDB TABLES in autocommit=1 mode becausethe acquired InnoDB ta BLE locks would be released immediately.

You cannot lock additional tables in Themiddle of a transaction because lock tables performs an implicit COMMIT Andunlock TABLES.

The limit of 1023 concurrent data-modifyingtransactions have been raised in MySQL 5.5 and above. The limit is now a *1023 concurrent transactions that generate undo records. You can remove anyworkarounds this require changing the proper structure of your transactions,such as committing more freq uently.

InnoDB table level lock with Lock table statement relationship