在數據庫應用，我們經常要用到唯一編號，以標識記錄。在MySQL中可通過數據列的AUTO_INCREMENT屬性來自動生成。

 

       當插入記錄時，如果為AUTO_INCREMENT數據列明確指定了一個數值，則會出現兩種情況，

情況一，如果插入的值與已有的編號重復，則會出現出錯信息，因為AUTO_INCREMENT數據列的值必須是唯一的；

情況二，如果插入的值大于已編號的值，則會把該插入到數據列中，并使在下一個編號將從這個新值開始遞增。也就是說，可以跳過一些編號。

如果自增序列的最大值被刪除了，則在插入新記錄時，該值被重用。

如果用UPDATE命令更新自增列，如果列值與已有的值重復，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。

 

       在使用AUTO_INCREMENT時，應注意以下幾點：

AUTO_INCREMENT是數據列的一種屬性，只適用于整數類型數據列。

設置AUTO_INCREMENT屬性的數據列應該是一個正數序列，所以應該把該數據列聲明為UNSIGNED，這樣序列的編號個可增加一倍。

AUTO_INCREMENT數據列必須有唯一索引，以避免序號重復(即是主鍵或者主鍵的一部分)。

AUTO_INCREMENT數據列必須具備NOT NULL屬性。

AUTO_INCREMENT數據列序號的最大值受該列的數據類型約束，如TINYINT數據列的最大編號是127,如加上UNSIGNED，則最大為255。一旦達到上限，AUTO_INCREMENT就會失效。

  

在mysql5.1.22之前，mysql的“INSERT-like”語句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)會在執行整個語句的過程中使用一個AUTO-INC鎖將表鎖住，直到整個語句結束（而不是事務結束）。

因此在使用INSERT…SELECT、INSERT…values(…),values(…)時，LOAD DATA等耗費時間較長的操作時，會將整個表鎖住，而阻塞其他的“INSERT-like”、Update等語句，推薦使用程序將這些語句分成多條語句，一一插入，減少單一時間的鎖表時間。

mysql5.1.22之后mysql進行了改進，引入了參數 innodb_autoinc_lock_mode，通過這個參數控制mysql的鎖表邏輯。

在介紹這個之前先引入幾個術語，方便說明 innodb_autoinc_lock_mode。

1.“INSERT-like”：

INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

2.“Simple inserts”：

就是通過分析insert語句可以確定插入數量的insert語句, INSERT, INSERT … VALUES(),VALUES()

 

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mod，傳統模式)。

這種方式就和mysql5.1.22以前一樣，為了向后兼容而保留了這種模式，如同前面介紹的一樣，這種方式的特點就是“表級鎖定”，并發性較差。

二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode，連續模式)。

這種方式是新版本中的默認方式，推薦使用，并發性相對較高，特點是“consecutive”，即保證同一條insert語句中新插入的auto_increment id都是連續的。

這種模式下：

“Simple inserts”：直接通過分析語句，獲得要插入的數量，然后一次性分配足夠的auto_increment id，只會將整個分配的過程鎖住。

“Bulk inserts”：因為不能確定插入的數量，因此使用和以前的模式相同的表級鎖定。

 

三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode，交叉模式)。

這種模式是來一個分配一個，而不會鎖表，只會鎖住分配id的過程，和innodb_autoinc_lock_mode = 1的區別在于，不會預分配多個，這種方式并發性最高。

但是在replication中當binlog_format為statement-based時（簡稱SBR statement-based replication）存在問題，因為是來一個分配一個，這樣當并發執行時，“Bulk inserts”在分配的時會同時向其他的INSERT分配，會出現主從不一致（從庫執行結果和主庫執行結果不一樣），因為binlog只會記錄開始的insert id。

 

最后說一句今天遇到這個問題，在LOCK_MODE為AUTO_INC，而且事物回滾非常的慢，不得已停止數據庫重新啟動

mysql> select * from innodb_locks;

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+

| 2954466:518 | 2954466     | AUTO_INC  | TABLE     | `test`.`kkkm` | NULL       |       NULL |      NULL |     NULL | NULL      |

| 2954465:518 | 2954465     | AUTO_INC  | TABLE     | `test`.`kkkm` | NULL       |       NULL |      NULL |     NULL | NULL      |

+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+