Relationship between innodb table level lock and lock table statement, innodbtable

Relationship between innodb table level lock and lock table statement, innodbtable

DDL statements only apply for intention-Level Table locks. In the lock table statement, mysql applies for mysql-Level table locks, and innodb also applies for innodb-Level table locks. The premise is that innodb_table_locks = 1

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

MySQL Table level locks and Innodb Table Levellocks are two separate beings. You almost never will run into problems withInnodb table level locks becauseInnodb will only set intentional level locks for everythingby DDL operations. If you're having locking issues with Innodbchances are it is row level locks or auto increment table level lock (mostlywith MySQL 5.0 and older MySQL versions ). mySQL level locks are entirelydifferent story. explicitly locking tables on MySQL level will prevent tablesfrom being accessed and will not show up in show engine innodb status. it is agood practice not to use lock 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

Also acquire InnoDB table locks.This option is enabled by default. this can cause deadlocks in 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, theMySQLLOCKTABLES operation acquires two

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 are being locked by other transactions.

 

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

Thus, it does not make much sense to invokeLOCK TABLES on InnoDB tables in AUTOCOMMIT =

1 mode, because the acquired InnoDB tablelocks wocould be released immediately.

• Sometimes it wocould be useful to lock further tables in the courseof a transaction. Unfortunately,

Lock tables in MySQL performs an implicitCOMMIT and unlock tables. An InnoDB

Variant of lock tables has been plannedthat can be executed in the middle of a transaction.

 

Locking and Transactions

Lock tables acquires two 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 are being locked by other transactions.

 

In MySQL 5.6, innodb_table_locks = 0 has noeffect for tables locked explicitly with lock tables... WRITE. it does have 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 by 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 table locks wocould 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 has been raised in MySQL 5.5 and above. the limit is now 128*1023 concurrent transactions that generate undo records. you can remove anyworkarounds that require changing the proper structure of your transactions, such as committing more frequently.