當(dāng)表中存在 AUTO_INCREMENT 列時(shí)，MySQL 在生成自增值的過(guò)程中，需要一種機(jī)制保證并發(fā)插入時(shí)不產(chǎn)生重復(fù) ID，這就涉及 自增鎖。

這個(gè)鎖用于 控制自增值分配的順序和唯一性，而不是保護(hù)整行數(shù)據(jù)。

自 MySQL 5.1 起支持通過(guò)參數(shù)innodb_autoinc_lock_mode來(lái)控制自增鎖模式，常見(jiàn)值有 0 / 1 / 2。

#查看
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

#運(yùn)行時(shí)設(shè)置
SET GLOBAL innodb_autoinc_lock_mode = 2;

#配置設(shè)置
[mysqld]
innodb_autoinc_lock_mode = 1

1、innode_autoinc_lock_mode=0（傳統(tǒng)模式）

1）特點(diǎn)

• 插入時(shí)使用 表級(jí) AUTO-INC 鎖，也就是說(shuō)：如果一個(gè)事務(wù)正在往表中插入記錄，所有其他事務(wù)的插入必須等待。
• 串行生成自增 ID，鎖一直持有到 語(yǔ)句結(jié)束

說(shuō)這種模式下可以保證，一個(gè)事物內(nèi)，一次insert插入多條，或多次insert插入數(shù)據(jù)的ID一定是連續(xù)的，不受其他事物影響（其他事物需要等待）。但是也無(wú)法保證整張表的ID都是連續(xù)的，因?yàn)榇嬖诨貪L。

2）問(wèn)題

• 并發(fā)插入性能差，容易成為瓶頸

3）易混淆點(diǎn)

innodb_autoinc_lock_mode = 0 只解決“并發(fā)插入順序”問(wèn)題，并不能解決“事務(wù)回滾回收自增 ID”的問(wèn)題。

也就是說(shuō)：

• 同一事務(wù)內(nèi)、多事務(wù)并發(fā)下，插入順序是串行的
• 回滾的自增 ID 不會(huì)被重用，所以事務(wù)回滾仍然會(huì)導(dǎo)致自增 ID 跳號(hào)。

所以，不要在代碼中使用自增ID作為業(yè)務(wù)連續(xù)性的邏輯處理。

2、innode_autoinc_lock_mode=1（默認(rèn)，連續(xù)模式）

性能和安全性這種，多數(shù)業(yè)務(wù)推薦使用。

1）特點(diǎn)

• Simple INSERT：幾乎不加表鎖
• Bulk / 非確定性 INSERT：仍使用 AUTO-INC 鎖
• 這種模式下可以支持：保證同一條語(yǔ)句內(nèi)自增值連續(xù)（insert into table(a,b,c) values (), ()...）

什么是Simple INSERT？

#一下為Simple INSERT
INSERT INTO t(a) VALUES (1);
INSERT INTO t(a) VALUES (1),(2);

#非 Simple INSERT
INSERT INTO t(a) SELECT ...;
LOAD DATA INFILE ...

2）和AUTO-INC鎖對(duì)比

上面介紹了，AUTO-INC模式下可以保證一個(gè)事物內(nèi)的插入操作產(chǎn)生的ID是連續(xù)的；

而autonic_lock_mode=1這種模式下只能保證一個(gè)insert多次插入的ID是連續(xù)的。

因?yàn)?，在這種模式下，AUTO_INCREMENT 的分配與事務(wù)提交無(wú)關(guān)，只與“INSERT 語(yǔ)句執(zhí)行順序”有關(guān)，所以同一個(gè)事務(wù)內(nèi)多次 INSERT，也不能保證 ID 連續(xù)

看一個(gè)例子：

#1.事務(wù)A插入，但是不提交，插入后id=1
BEGIN;
INSERT INTO t(v) VALUES (1);

#2.事務(wù)B插入，并提交，此時(shí)id=2
BEGIN;
INSERT INTO t(v) VALUES (2);
COMMIT;

#3.事務(wù)A繼續(xù)插入，插入后id=3
INSERT INTO t(v) VALUES (3);

#4.這時(shí)在事務(wù)A中插敘，發(fā)現(xiàn)id為1和3
SELECT id, v FROM t;

可以看到，因?yàn)闆](méi)有了鎖，ID自增器類(lèi)似于一個(gè)發(fā)號(hào)器，每個(gè)insert都從這里取號(hào)。

3、innode_autoinc_lock_mode=2（交錯(cuò)模式/無(wú)鎖模式）

1）特點(diǎn)

• 完全不使用 AUTO-INC 表鎖，多線程并發(fā)性能最好
• 自增值只保證唯一，不保證連續(xù)

這種模式下，即使同一條 INSERT 內(nèi) ID 可能不連續(xù)，即：出現(xiàn)跳號(hào)。

2）例子

#線程A
INSERT INTO t(a) VALUES (1),(2);

#線程B
INSERT INTO t(a) VALUES (3),(4);

#查詢(xún)結(jié)果
實(shí)際 ID 可能交錯(cuò)：1,3,2,4

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論