关于MYSQL 的 AUTO-INC Locks
今天看到一題MYSQL OCP 試題，關于 AUTO-INC locks ，才知在自增長鍵值時，是使用表鎖。
試題如下，同時把相關的資料也轉發如下。
QUESTION 94
Which two statements are true about InnoDB auto-increment locking?A. The auto-increment lock can be a table-level lock.
B. InnoDB never uses table-level locks.
C. Some settings for innodb_autoinc_lock_mode can help reduce locking.
D. InnoDB always protects auto-increment updates with a table-level lock.
E. InnoDB does not use locks to enforce auto-increment uniqueness.
Correct Answer: AD
Section: (none)
Explanation
Explanation/Reference:
Explanation: A (not B): InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns.
D (Not E): This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a
predictable and repeatable order for a given sequence of INSERT statements.
Reference: 14.6.5.2 Configurable InnoDB Auto-Increment Locking
http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html
AUTO-INC Locks
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.
AUTO-INC鎖是當向使用含有AUTO_INCREMENT列的表中插入數據時需要獲取的一種特殊的表級鎖?
在最簡單的情況下，如果一個事務正在向表中插入值，則任何其他事務必須等待對該表執行自己的插入操作，以便第一個事務插入的行的值是連續的。?
innodb_autoinc_lock_mode配置選項控制用于自動增量鎖定的算法。 它允許您選擇如何在可預測的自動遞增值序列和插入操作的最大并發性之間進行權衡。
AUTO_INCREMENT Handling in InnoDB
InnoDB提供了一個可配置的鎖定機制，可以顯著提高使用AUTO_INCREMENT列向表中添加行的SQL語句的可伸縮性和性能。 要對InnoDB表使用AUTO_INCREMENT機制，必須將AUTO_INCREMENT列定義為索引的一部分，以便可以對表執行相當于索引的SELECT MAX（ai_col）查找以獲取最大列值。 通常，這是通過使列成為某些表索引的第一列來實現的。
本節介紹AUTO_INCREMENT鎖定模式的行為，對不同AUTO_INCREMENT鎖定模式設置的使用含義，以及InnoDB如何初始化AUTO_INCREMENT計數器。
-
InnoDB AUTO_INCREMENT鎖定模式
-
InnoDB AUTO_INCREMENT鎖定模式使用含義
-
InnoDB AUTO_INCREMENT計數器初始化
InnoDB AUTO_INCREMENT鎖定模式
本節介紹用于生成自動遞增值的AUTO_INCREMENT鎖定模式的行為，以及每種鎖定模式如何影響復制。 自動遞增鎖定模式在啟動時使用innodb_autoinc_lock_mode配置參數進行配置。
以下術語用于描述innodb_autoinc_lock_mode設置:
-
“INSERT-like” statements(類INSERT語句)?
所有可以向表中增加行的語句,包括INSERT,?INSERT ... SELECT,?REPLACE,?REPLACE ... SELECT, and?LOAD DATA.包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts. -
“Simple inserts”?
可以預先確定要插入的行數（當語句被初始處理時）的語句。 這包括沒有嵌套子查詢的單行和多行INSERT和REPLACE語句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。 -
“Bulk inserts”?
事先不知道要插入的行數（和所需自動遞增值的數量）的語句。 這包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句，但不包括純INSERT。 InnoDB在處理每行時一次為AUTO_INCREMENT列分配一個新值。 -
“Mixed-mode inserts”?
這些是“Simple inserts”語句但是指定一些（但不是全部）新行的自動遞增值。 示例如下，其中c1是表t1的AUTO_INCREMENT列：?
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一種類型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最壞的情況下實際上是INSERT語句隨后又跟了一個UPDATE，其中AUTO_INCREMENT列的分配值不一定會在 UPDATE 階段使用
-
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
傳統的鎖定模式提供了在MySQL 5.1中引入innodb_autoinc_lock_mode配置參數之前存在的相同行為。傳統的鎖定模式選項用于向后兼容性，性能測試以及解決“Mixed-mode inserts”的問題，因為語義上可能存在差異。
在此鎖定模式下，所有“INSERT-like”語句獲得一個特殊的表級AUTO-INC鎖，用于插入具有AUTO_INCREMENT列的表。此鎖定通常保持到語句結束（不是事務結束），以確保為給定的INSERT語句序列以可預測和可重復的順序分配自動遞增值，并確保自動遞增由任何給定語句分配的值是連續的。
在statement-based replication的情況下，這意味著當在從服務器上復制SQL語句時，自動增量列使用與主服務器上相同的值。多個INSERT語句的執行結果是確定性的，SLAVE再現與MASTER相同的數據。如果由多個INSERT語句生成的自動遞增值交錯，則兩個并發INSERT語句的結果將是不確定的，并且不能使用基于語句的復制可靠地傳播到從屬服務器。
為了解釋清楚,查看下面的例子:
假設有兩個事務正在運行，每個事務都將行插入到具有AUTO_INCREMENT列的表中。 一個事務正在使用插入1000行的INSERT … SELECT語句，另一個事務正在使用插入一行的“Simple inserts”語句:
InnoDB不能預先得知有多少行會從TX1的select部分獲取到,所以在事務進行過程中,InnoDB一次只會為AUTO_INCREMENT列分配一個值.?
通過一個表級鎖的控制,保證了在同一時刻只有一個引用表t1的INSERT語句可以執行,直到整個INSERT語句結束,并且由不同語句生成自動遞增數不會交錯?
由Tx1?INSERT ... SELECT語句生成的自動遞增值將是連續的，并且Tx2中的INSERT語句使用的（單個）自動遞增值將小于或大于用于Tx1的所有那些值，具體取決于 那個語句先執行。
只要SQL語句在從二進制日志（當使用基于語句的復制或在恢復方案中）重放時以相同的順序執行，結果將與Tx1和Tx2首次運行時的結果相同。 因此，持續至語句結束的表級鎖定( table-level locks)保證了在statement-based replication中對auto-increment列的插入數據的安全性. 但是，當多個事務同時執行insert語句時，這些表級鎖定會限制并發性和可伸縮性。
在前面的示例中，如果沒有表級鎖，則Tx2中用于INSERT的自動遞增列的值取決于語句執行的確切時間。 如果Tx2的INSERT在Tx1的INSERT正在運行時（而不是在它開始之前或完成之后）執行，則由兩個INSERT語句分配的特定自動遞增值將是不確定的，并且可能每次運行都會得到不同的值
在連續鎖定模式下，InnoDB可以避免為“Simple inserts”語句使用表級AUTO-INC鎖，其中行數是預先已知的，并且仍然保留基于語句的復制的確定性執行和安全性。
如果不使用二進制日志作為恢復或復制的一部分來重放SQL語句，則可以使用interleaved lock模式來消除所有使用表級AUTO-INC鎖，以實現更大的并發性和性能,其代價是由于并發的語句交錯執行,同一語句生成的AUTO-INCREMENT值可能會產生GAP
-
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
這是默認的鎖定模式.在這個模式下,“bulk inserts”仍然使用AUTO-INC表級鎖,并保持到語句結束.這適用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句。同一時刻只有一個語句可以持有AUTO-INC鎖.
“Simple inserts”（要插入的行數事先已知）通過在mutex（輕量鎖）的控制下獲得所需數量的自動遞增值來避免表級AUTO-INC鎖， 它只在分配過程的持續時間內保持，而不是直到語句完成。 不使用表級AUTO-INC鎖，除非AUTO-INC鎖由另一個事務保持。 如果另一個事務保持AUTO-INC鎖，則“簡單插入”等待AUTO-INC鎖，如同它是一個“批量插入”。
此鎖定模式確保,當行數不預先知道的INSERT存在時(并且自動遞增值在語句過程執行中分配)由任何“類INSERT”語句分配的所有自動遞增值是連續的，并且對于基于語句的復制(statement-based replication)操作是安全的。
這種鎖定模式顯著地提高了可擴展性,并且保證了對于基于語句的復制(statement-based replication)的安全性.此外，與“傳統”鎖定模式一樣，由任何給定語句分配的自動遞增數字是連續的。 與使用自動遞增的任何語句的“傳統”模式相比，語義沒有變化.?
但有一個特例:
The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.
-
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
在這種鎖定模式下,所有類INSERT(“INSERT-like” )語句都不會使用表級AUTO-INC lock,并且可以同時執行多個語句。這是最快和最可擴展的鎖定模式，但是當使用基于語句的復制或恢復方案時，從二進制日志重播SQL語句時，這是不安全的。
在此鎖定模式下，自動遞增值保證在所有并發執行的“類INSERT”語句中是唯一且單調遞增的。但是，由于多個語句可以同時生成數字（即，跨語句交叉編號），為任何給定語句插入的行生成的值可能不是連續的。
如果執行的語句是“simple inserts”，其中要插入的行數已提前知道，則除了“混合模式插入”之外，為單個語句生成的數字不會有間隙。然而，當執行“批量插入”時，在由任何給定語句分配的自動遞增值中可能存在間隙。
InnoDB AUTO_INCREMENT鎖定模式使用含義
- 在復制環節中使用自增列?
如果你在使用基于語句的復制(statement-based replication)請將innodb_autoinc_lock_mode設置為0或1，并在主從上使用相同的值。 如果使用innodb_autoinc_lock_mode = 2（“interleaved”）或主從不使用相同的鎖定模式的配置，自動遞增值不能保證在從機上與主機上相同。
如果使用基于行的或混合模式的復制，則所有自動增量鎖定模式都是安全的，因為基于行的復制對SQL語句的執行順序不敏感（混合模式會在遇到不安全的語句是使用基于行的復制模式）。
-
“Lost” auto-increment values and sequence gaps?
在所有鎖定模式（0,1和2）中，如果生成自動遞增值的事務回滾，那些自動遞增值將“丟失”。 一旦為自動增量列生成了值，無論是否完成“類似INSERT”語句以及包含事務是否回滾，都不能回滾。 這種丟失的值不被重用。 因此，存儲在表的AUTO_INCREMENT列中的值可能存在間隙。 -
Specifying NULL or 0 for the AUTO_INCREMENT column?
在所有鎖定模式（0,1和2）中，如果用戶在INSERT中為AUTO_INCREMENT列指定NULL或0，InnoDB會將該行視為未指定值，并為其生成新值。 -
為AUTO_INCREMENT列分配一個負值?
在所有鎖定模式（0,1和2）中，如果您為AUTO_INCREMENT列分配了一個負值，則不會定義自動增量機制的行為。 -
如果AUTO_INCREMENT值大于指定整數類型的最大整數?
在所有鎖定模式（0,1和2）中，如果值大于可以存儲在指定整數類型中的最大整數，則不定義自動遞增機制的行為。 -
Gaps in auto-increment values for “bulk inserts”?
當innodb_autoinc_lock_mode設置為0（“traditional”）或1（“consecutive”）時,任何給定語句生成的自動遞增值是連續的，沒有間隙，因為表級AUTO-INC鎖會持續到 語句結束,并且一次只能執行一個這樣的語句。
當innodb_autoinc_lock_mode設置為2（“interleaved”）時，在“bulk inserts”生成的自動遞增值中可能存在間隙，但只有在并發執行“INSERT-Like”語句時才會產生這種情況。
對于鎖定模式1或2，在連續語句之間可能出現間隙，因為對于批量插入，每個語句所需的自動遞增值的確切數目可能不為人所知，并且可能進行過度估計。
- 由“mixed-mode inserts”分配的自動遞增值?
考慮一下場景,在“mixed-mode insert”中,其中一個“simple insert”語句指定了一些（但不是全部）行的AUTO-INCREMENT值。 這樣的語句在鎖模式0,1和2中表現不同。例如，假設c1是表t1的AUTO_INCREMENT列，并且最近自動生成的序列號是100。
Now, consider the following “mixed-mode insert” statement:
當innodb_autoinc_lock_mode=0時:
下一個可用的auto-increment值103.因為innodb_autoinc_lock_mode=0時,auto-increment值一次只分配一個,而不是在開始時全部分配.不論是否有并發的其他類INSERT語句同時執行,都會是這樣的結果
當innodb_autoinc_lock_mode=1時:
不同于innodb_autoinc_lock_mode=0時的情況,此時下一個可用的auto-increment值105,因為auto-increment值在語句一開始就分配了,分配了四個,但是只用了倆.不論是否有并發的其他類INSERT語句同時執行,都會是這樣的結果
當innodb_autoinc_lock_mode=2時:
x和y的值是唯一的，并大于任何先前生成的行。 然而，x和y的具體值取決于通過并發執行語句生成的自動增量值的數量。
最后考慮下面的情況,當最近的 AUTO-INCREMENT 值為4時,執行下面的語句:
無論innodb_autoinc_lock_mode如何設置,都會報錯duplicate-key error 23000 (Can't write; duplicate key in table)?
因為5已經分配給了(NULL, 'b'),所以導致插入(5, 'C')時報錯
- 在INSERT語句序列的中間修改AUTO_INCREMENT列值?
在所有鎖定模式（0,1和2）中，在INSERT語句序列中間修改AUTO_INCREMENT列值可能會導致duplicate key錯誤。
InnoDB AUTO_INCREMENT計數器初始化
本章節討論 InnoDB如何初始化AUTO_INCREMENT計數器?
如果你為一個Innodb表創建了一個AUTO_INCREMENT列,則InnoDB數據字典中的表句柄包含一個稱為自動遞增計數器的特殊計數器，用于為列分配新值。 此計數器僅存在于內存中，而不存儲在磁盤上。
要在服務器重新啟動后初始化自動遞增計數器，InnoDB將在首次插入行到包含AUTO_INCREMENT列的表時執行以下語句的等效語句。
InnoDB增加語句檢索的值，并將其分配給表和表的自動遞增計數器。 默認情況下，值增加1.此默認值可以由auto_increment_increment配置設置覆蓋。
如果表為空，InnoDB使用值1.此默認值可以由auto_increment_offset配置設置覆蓋。
如果在自動遞增計數器初始化前使用SHOW TABLE STATUS語句查看表, InnoDB將初始化計數器值,但不會遞增該值.這個值會儲存起來以備之后的插入語句使用.這個初始化過程使用了一個普通的排它鎖來讀取表中自增列的最大值. InnoDB遵循相同的過程來初始化新創建的表的自動遞增計數器。
在自動遞增計數器初始化之后，如果您未明確指定AUTO_INCREMENT列的值，InnoDB會遞增計數器并將新值分配給該列。如果插入顯式指定列值的行，并且該值大于當前計數器值，則將計數器設置為指定的列值。
只要服務器運行，InnoDB就使用內存中自動遞增計數器。當服務器停止并重新啟動時，InnoDB會重新初始化每個表的計數器，以便對表進行第一次INSERT，如前所述。
服務器重新啟動還會取消CREATE TABLE和ALTER TABLE語句中的AUTO_INCREMENT = N表選項的效果
（轉自： http://blog.csdn.net/ashic/article/details/53810319）
總結
以上是生活随笔為你收集整理的关于MYSQL 的 AUTO-INC Locks的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MongoDb 中 serverStat
- 下一篇： 一次从节点同步出错的解决