innodb table level lock 與lock table語句關係，innodbtable

innodb table level lock 與lock table語句關係，innodbtable

DDL語句只申請意向層級的表鎖。在lock table語句中，mysql會申請mysql層級表鎖，同時innodb也會申請innodb層級表鎖。前提是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 because innodb 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 to

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, theMySQL LOCKTABLES 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 would be released immediately.

• Sometimes it would 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 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 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.