InnoDBd的auto_increment以及innodb_autoinc_lock_mode

myownstars發表於2013-01-18
REM

InnoDB的auto_increment，類似oracle的sequence，可以自動增長，通常用於主鍵；

auto_increment必須為索引列，其值一旦分配就不能隨著相應sql的回滾而回退；

 

當InnoDB表建立auto_increment列時，其資料字典會分配一個auto-increment計數器，在記憶體中操作維護；

第一次向表插入資料時，InnoDB會執行如下語句檢視列當前最大值

Select max(ai_col) from t for update

在最大值上增加auto_increment_increment(預設為1)並賦予新插入的ai_col列，若表為空則初始值由auto_increment_offset(預設為1)決定；

也可以顯示的為ai_col列賦值，當其大於當前計數器值時則重置計數器為此值，若為Null或0則照舊使用計數器；

 

根據其插入行數可將insert分為3類：

Simple inserts：執行時可以確定行數

Bulk inserts：執行時行數不確定，load data/insert … select/replace … insert

Mixed-mode inserts：只有部分行使用auto_increment值，如insert … on duplicate key update

 

在對錶進行併發bulk insert時，無法確定每行的auto_increment值，在進行recover或語句級複製時，會出現同一行記錄擁有不同的auto_increment值；

 

早期版本，InnoDB在訪問auto_increment計數器時會對相應表新增名為auto-inc的表級鎖，在sql執行結束時釋放，在此之前其他insert會被阻塞；

此舉雖然保證了資料一致，但是降低了併發度，為此InnoDB引入了innodb_autoinc_lock_mode，使用者可根據實際情況定製鎖策略，該引數有如下值 ：

0：採用傳統鎖模式，所有insert操作都要申請auto-inc鎖；

1：bulk-inserts採用auto-inc鎖，而simple insert則使用更為輕量級的mutex，如表已經有了auto-inc鎖，simple insert也要等待直至其釋放；

2：所有insert都不採用auto-inc鎖，生成的auto_increment可能不連貫

 

至於最特殊的mixed-mode insert，會依據innodb_autoinc_lock_mode的值而產生不同的效果

--c1列採用auto_increment，且當前最大值為100，分別將innodb_autoinc_lock_mode設定為0/1/2進行如下操作

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

0：

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • | 101 | b    |
  • |   5 | c    |
  • | 102 | d    |
  • +-----+------+

--下一個auto_increment值為103，因為其按行一次分配一個，而不是在執行時一次性分配

1：

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • | 101 | b    |
  • |   5 | c    |
  • | 102 | d    |

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

--下一條auto_increment為105，在sql執行時一次性分配

2：

  • +-----+------+
  • | c1  | c2   |
  • +-----+------+
  • |   1 | a    |
  • |   x | b    |
  • |   5 | c    |
  • |   y | d    |
  • +-----+------+

--X/Y具體值受其他併發操作影響

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-752853/，如需轉載，請註明出處，否則將追究法律責任。

相關文章