關於MySQL innodb_autoinc_lock_mode的詳細介紹

黄舟
發布: 2017-03-18 13:49:26
原創
1181 人瀏覽過

下面小編就為大家帶來一篇關於MySQL innodb_autoinc_lock_mode介紹。小編覺得蠻不錯的,現在就分享給大家,也給大家做個參考。一起跟著小編過來看看吧

innodb_autoinc_lock_mode這個參數控制著在向有auto_increment 列的表插入資料時,相關鎖的行為;

#透過對它的設定可以達到效能與安全(主從的資料一致性)的平衡

【0】我們先對insert做一下分類

首先insert大致上可以分成三類:

1、simple insert 如insert into t(name) values('test')

2、bulk insert 如load data | insert into . .. select .... from ....

3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b') ,(5,'c');

【1】innodb_autoinc_lock_mode 的說明

innodb_auto_lockmode有三個取值:

1、0 這個表示tradition 傳統

2、1 這個表示consecutive 連續

3、2 這個表示interleaved 交錯

#【1.1】tradition(innodb_autoinc_lock_mode=0) 模式:

1、它提供了一個向後相容的能力

#2、在這一模式下,所有的insert語句("insert like") 都要在語句開始的時候得到一個表級的auto_inc鎖,在語句結束的時候才釋放這把鎖,注意呀,這裡說的是語句等級而不是事務級的,一個事務可能包涵有一個或多個語句。

3、它能保證值分配的可預見性,與連續性,可重複性,這個也就保證了insert語句在複製到slave的時候還能產生和master那邊一樣的值(它保證了基於語句複製的安全性)。

4、由於在這種模式下auto_inc鎖一直要保持到語句的結束,所以這個就影響到了並發的插入。

 【1.2】consecutive(innodb_autoinc_lock_mode=1) 模式:

1、這個模式下去simple insert 做了優化,由於simple insert一次性插入值的個數可以立刻得到 確定,所以mysql可以一次產生幾個連續的值,用於這個insert語句;總的來說這個對複製也是安全的(它保證了基於語句複製的安全)

2、這個模式也是mysql的預設模式,這個模式的好處是auto_inc鎖不要一直保持到語句的結束,只要語句得到了對應的值後就可以提前釋放鎖定

【1.3】interleaved(innodb_autoinc_lock_mode=2) 模式

1、由於這個模式下已經沒有了auto_inc鎖,所以這個模式下的性能是最好的;但是它也有一個問題,就是對於同一個語句來說它所得到的auto_incremant值可能不是連續的。

【2】如果你的二進位檔案格式是mixed | row 那麼這三個值中的任何一個對於你來說都是複製安全的。

由於現在mysql已經推薦把二進位的格式設定成row,所以在binlog_format不是statement的情況下最好是innodb_autoinc_lock_mode=2 這樣可能知道更好的性能。

最後以一個關於auto_increment 的例子來結束

範例:不要沒事去更新一個auto_increment 列的值

#第一步:重現一下場景

create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);
select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
登入後複製

第二步:重現一下引發問題的SQL

update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+
登入後複製

#第三步:重現總是的表現形式

insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
登入後複製

#第四步:對問題的總結

執行完第一步的時候mysql知道下一個auto_increment值是4。

執行完第二步驟的時候mysql並不知道4已經被人為的佔用了,所以執行第三步驟的時候就出錯了。

以上是關於MySQL innodb_autoinc_lock_mode的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板