mysql innodb_autoinc_lock_mode 的與資料庫行為

mysql 的auto_incremet  都知道了 


對應的還有一個這個行為的一個鎖的型別的引數,對資料庫的行為會有影響. 


innodb_autoinc_lock_mode  有3個取值 0 , 1 ,2 對應於 
“traditional”,“consecutive”, or “interleaved” lock mode


針對有3中行為: 


1. insert_like  語句 包括: insert ,  insert ... select,  replace ,replace ... select  已經 load data 語句. 


2.simple insert  語句:  insert , replace 這樣. 


3.bulk insert  語句:  insert ...select , replace...select , load data.


4.mixed insert 語句: 例如 INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 
           還有   INSERT ... ON DUPLICATE KEY UPDATE 這種語句. 








innodb_autoinc_lock_mode  取值為 0 : 


這個引數是從5.1 引入的, 對應於傳統的模式.  對於insert like 語句這個auto_incremet  取得的是一個表級鎖. 


持有鎖的時間是到當前語句結束. 確保語句的寫入是可以預見以及可以預測,可以重現的,也就是可以保證語句入庫的資料時順序的. 


對於使用statement format 的 binlog 模式的複製,slave 跟主庫同一行記錄取得的值是一致. 


例如 : 


CREATE TABLE t1 (  c1 INT(11) NOT NULL AUTO_INCREMENT,  c2 VARCHAR(10) DEFAULT NULL,  PRIMARY KEY (c1)) ENGINE=InnoDB;

執行語句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

 在statement 的複製中, 日誌重演這個sql執行,是可以確保主從取得的值是一致的. 


tx1 執行的時候, 是可以保證 這1000筆記錄是連續的取得 autoinc 欄位的值, tx2 要等到tx1 執行完後才能拿到autoinc的值. 


這樣限制了併發. 




innodb_autoinc_lock_mode 取值 1 : 


取值為1 為預設值. 


對於 bulk insert  語句.  每個語句只有這個鎖到語句結束,但是每個語句持有這個鎖只能執行一次. 


對於simple inset  語句是可預計的插入的值的數量,那麼語句是可以不需要持有這個表級鎖到語句結束. 而是更高階別的mutex 鎖.


對於不可以預見插入數量的語句, 起行為跟bulk insert 是一樣的,持有鎖到語句結束,每次只能執行一次. 


對於mixed inset  資料庫會分派多於插入插入數量的 autoinc 值 , 語句結束後,多餘的值將丟棄, 這個時候, autoinc 欄位的值可能不是連續的. 






innodb_autoinc_lock_mode  取值 2 : 




insert like 語句 可以持有這個表級鎖,並執行相同的時間. 




這樣對於statement based  複製來說, 在恢復的時候,結果就變得不確定了. 也就是可能主從庫的autoinc 欄位的值是不一致的. 




這樣會對業務邏輯代理困擾. 




這種模式下,每個語句都可以幾乎在同時產生autoinc值,這樣每個語句的所取得的autoinc欄位的值是不確定的.也不可預測. 




對於simple insert 如果在執行前 可以預測到要插入的值的數量,這個sql的資料獲取的autoinc值是連續的.  否則就不一定是連續的. 


對於mixed insert 語句 取得autoinc欄位的值是不可預測的. 




對於bulk insert 語句 取得autoinc 的值可能存在空缺 (gap)  即取得的值可能不是連續的. 








innodb_autoinc_lock_mode 的取值與複製的關係.




取值 0 跟 1  對複製來說都是安全的, 即可以保證主從資料庫的值是一致的. 




如果卻只為2  在 基於 statement based 複製來說,從庫的資料時不確定的. 即不安全. 




對應 row based 或者 mixed based 的複製  從庫的資料時確定跟主庫一致的,是安全. 




可以看出在 取值2 的時候,可以獲得更大的效能. 












另外如果指定了 autoinc欄位的值 insert 可能會觸發 key 衝突的 錯誤. 




如果insert 指定的值大於autoinc 欄位的最大值, 其結果是不確定的. 








innodb_autoinc_lock_mode 取值為2的時候對於bulk insert 會產生空洞(gap)   同一個sql 寫入資料庫的值不一定是連續的.