前提條件，percona 5.6版本，事務(wù)隔離級(jí)別為RR
mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
Table: test_autoinc_lock
Create Table: CREATE TABLE `test_autoinc_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
8 rows in set (0.00 sec)
條件1 innodb_autoinc_lock_mode設(shè)置為0
session1
begin;delete from test_autoinc_lock where a>7;//這時(shí)未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap鎖的存在，這時(shí)處于鎖等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//這時(shí)同樣處于等待狀態(tài)，理論上這個(gè)不是gap鎖的鎖定范圍，那么它是在等什么呢
session4
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 2317
trx_state: LOCK WAIT
trx_started: 2016-10-31 19:28:05
trx_requested_lock_id: 2317:20
trx_wait_started: 2016-10-31 19:28:05
trx_weight: 1
trx_mysql_thread_id: 9
trx_query: insert into test_autoinc_lock(a) values(2)
trx_operation_state: setting auto-inc lock
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
這時(shí)查看session3是等待自增鎖，一直處于setting auto-inc lock狀態(tài)
session2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這時(shí)session3鎖等待超時(shí)退出
session3
這時(shí)再看session3可以發(fā)現(xiàn)insert完成。
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
9 rows in set (0.00 sec)//注意看這時(shí)的最大自增值是13，也就是之前自增最大值上+1，也就是說(shuō)session2后來(lái)釋放了預(yù)計(jì)生成的自增id，將13留給了session3，自增id值的申請(qǐng)完全是串行順序的。
結(jié)論：innodb_autoinc_lock_mode為0時(shí)的，也就是官方說(shuō)的traditional
級(jí)別，該自增鎖是表鎖級(jí)別，且必須等待當(dāng)前SQL執(zhí)行完成后或者回滾掉才會(huì)釋放，這樣在高并發(fā)的情況下可想而知自增鎖競(jìng)爭(zhēng)是比較大的。
條件2 innodb_autoinc_lock_mode設(shè)置為1
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test_autoinc_lock where a>7;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
9 rows in set (0.00 sec)//注意看這時(shí)的最大自增值是13
session2
mysql> insert into test_autoinc_lock(a) values(100);//同樣gap鎖的存在，這時(shí)處于鎖等待
session3
mysql> insert into test_autoinc_lock(a) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 13 | 2 |
| 2 | 3 |
| 3 | 5 |
| 15 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
10 rows in set (0.00 sec)//session3直接完成了，并且注意觀察插入的自增id值是15，也就是跳過(guò)了預(yù)計(jì)分配給session2的14，可以看到自增id值立馬就分配給了session3，而不必等session2執(zhí)行完成
結(jié)論：innodb_autoinc_lock_mode為1時(shí)的，也就是官方說(shuō)的consecutive
級(jí)別，這時(shí)如果是單一的insert SQL，可以立即獲得該鎖，并立即釋放，而不必等待當(dāng)前SQL執(zhí)行完成（除非在其他事務(wù)中已經(jīng)有session獲取了自增鎖）。另外當(dāng)SQL是一些批量insert sql時(shí)，比如insert into ...select ...,load data,replace ..select..時(shí)，這時(shí)還是表級(jí)鎖，可以理解成退化為必須等待當(dāng)前SQL執(zhí)行完才釋放。
可以認(rèn)為，該值為1時(shí)是相對(duì)比較輕量的鎖，也不會(huì)對(duì)復(fù)制產(chǎn)生影響，唯一的缺陷是產(chǎn)生的自增值不一定是完全連續(xù)的（不過(guò)個(gè)人認(rèn)為這個(gè)往往不是很重要，也沒(méi)必要根據(jù)自增id值來(lái)統(tǒng)計(jì)行數(shù)之類）
條件3 innodb_autoinc_lock_mode設(shè)置為2
先說(shuō)結(jié)論：當(dāng)innodb_autoinc_lock_mode設(shè)置為2時(shí)，所有insert種類的SQL都可以立馬獲得鎖并釋放，這時(shí)的效率最高。但是會(huì)引入一個(gè)新的問(wèn)題：當(dāng)binlog_format為statement時(shí)，這時(shí)的復(fù)制沒(méi)法保證安全，因?yàn)榕康膇nsert，比如insert ..select..語(yǔ)句在這個(gè)情況下，也可以立馬獲取到一大批的自增id值，不必鎖整個(gè)表，slave在回放這個(gè)sql時(shí)必然會(huì)產(chǎn)生錯(cuò)亂。我們做個(gè)測(cè)試驗(yàn)證復(fù)制不是安全的。
master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1
master session2（注意session2在session1執(zhí)行完成之前執(zhí)行）
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id | a |
+---------+------+
| 1376236 | 2 |
+---------+------+
1 row in set (0.00 sec)
slave session1（這時(shí)可看到1376236主鍵沖突）
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.9.73.139
Master_User: ucloudbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 75823243
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 541
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
Skip_Counter: 0
Exec_Master_Log_Pos: 75822971
我們這時(shí)解析下主庫(kù)的binlog不難發(fā)現(xiàn)問(wèn)題原因，第一條批量insert還沒(méi)執(zhí)行完時(shí)，第二條簡(jiǎn)單insert執(zhí)行時(shí)獲得了自增id值為1376236的鎖，這時(shí)在主庫(kù)寫(xiě)入是沒(méi)有問(wèn)題的，但是反應(yīng)到從庫(kù)時(shí)，因?yàn)槭腔趕tatement的復(fù)制，必然出現(xiàn)主鍵沖突。
SET INSERT_ID=1376236/*!*/;
#161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c Query thread_id=20 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1477921471/*!*/;
insert into test_autoinc_lock(a) values(2)
/*!*/;
# at 75822940
#161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274
COMMIT/*!*/;
# at 75822971
#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0
SET TIMESTAMP=1477921466/*!*/;
BEGIN
/*!*/;
# at 75823050
# at 75823082
#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar
SET INSERT_ID=1/*!*/;
#161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba Query thread_id=57 exec_time=30 error_code=0
SET TIMESTAMP=1477921466/*!*/;
insert into test_autoinc_lock(a) select * from test_auto
總結(jié)：
1 innodb row復(fù)制時(shí)，可將innodb_autoinc_lock_mode設(shè)置為2，這時(shí)可在所有insert情況下表獲得最大并發(fā)度
2 innodb statement復(fù)制時(shí)，可將innodb_autoinc_lock_mode設(shè)置為1，保證復(fù)制安全的同時(shí)，獲得簡(jiǎn)單insert語(yǔ)句的最大并發(fā)度
3 myisam引擎情況下，無(wú)論什么樣自增id鎖都是表級(jí)鎖，設(shè)置innodb_autoinc_lock_mode參數(shù)無(wú)效（測(cè)試略）
4 實(shí)際上提問(wèn)者說(shuō)到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因?yàn)閕nnodb是主鍵聚集索引，實(shí)際的主鍵值必須按照主鍵順序存取，那么自增id本身就是升序的，那么在插入數(shù)據(jù)時(shí)，底層就不必再做額外的排序操作，也減少了索引頁(yè)分裂的次數(shù)，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）
以上這篇淺談innodb_autoinc_lock_mode的表現(xiàn)形式和選值參考方法就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
