前提條件，percona 5.6版本，事務隔離級別為RR
mysql> show create table test_autoinc_lock/G*************************** 1. row *************************** Table: test_autoinc_lockCreate 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=utf81 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設置為0
session1 begin;delete from test_autoinc_lock where a>7;//這時未提交session2mysql> insert into test_autoinc_lock(a) values(100);//gap鎖的存在，這時處于鎖等待session3mysql> insert into test_autoinc_lock(a) values(2);//這時同樣處于等待狀態，理論上這個不是gap鎖的鎖定范圍，那么它是在等什么呢session4mysql> 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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0trx_autocommit_non_locking: 0
這時查看session3是等待自增鎖，一直處于setting auto-inc lock狀態
session2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這時session3鎖等待超時退出
session3
這時再看session3可以發現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)//注意看這時的最大自增值是13，也就是之前自增最大值上+1，也就是說session2后來釋放了預計生成的自增id，將13留給了session3，自增id值的申請完全是串行順序的。
結論：innodb_autoinc_lock_mode為0時的，也就是官方說的traditional
級別，該自增鎖是表鎖級別，且必須等待當前SQL執行完成后或者回滾掉才會釋放，這樣在高并發的情況下可想而知自增鎖競爭是比較大的。
條件2 innodb_autoinc_lock_mode設置為1
session1mysql> 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)//注意看這時的最大自增值是13session2mysql> insert into test_autoinc_lock(a) values(100);//同樣gap鎖的存在，這時處于鎖等待session3mysql> 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，也就是跳過了預計分配給session2的14，可以看到自增id值立馬就分配給了session3，而不必等session2執行完成
結論：innodb_autoinc_lock_mode為1時的，也就是官方說的consecutive
級別，這時如果是單一的insert SQL，可以立即獲得該鎖，并立即釋放，而不必等待當前SQL執行完成（除非在其他事務中已經有session獲取了自增鎖）。另外當SQL是一些批量insert sql時，比如insert into ...select ...,load data,replace ..select..時，這時還是表級鎖，可以理解成退化為必須等待當前SQL執行完才釋放。
可以認為，該值為1時是相對比較輕量的鎖，也不會對復制產生影響，唯一的缺陷是產生的自增值不一定是完全連續的（不過個人認為這個往往不是很重要，也沒必要根據自增id值來統計行數之類）
條件3 innodb_autoinc_lock_mode設置為2
先說結論：當innodb_autoinc_lock_mode設置為2時，所有insert種類的SQL都可以立馬獲得鎖并釋放，這時的效率最高。但是會引入一個新的問題：當binlog_format為statement時，這時的復制沒法保證安全，因為批量的insert，比如insert ..select..語句在這個情況下，也可以立馬獲取到一大批的自增id值，不必鎖整個表，slave在回放這個sql時必然會產生錯亂。我們做個測試驗證復制不是安全的。
master session1mysql> 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: 1master session2（注意session2在session1執行完成之前執行）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（這時可看到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
我們這時解析下主庫的binlog不難發現問題原因，第一條批量insert還沒執行完時，第二條簡單insert執行時獲得了自增id值為1376236的鎖，這時在主庫寫入是沒有問題的，但是反應到從庫時，因為是基于statement的復制，必然出現主鍵沖突。
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=0use `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 = 274COMMIT/*!*/;# at 75822971#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0SET TIMESTAMP=1477921466/*!*/;BEGIN/*!*/;# at 75823050# at 75823082#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 IntvarSET 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=0SET TIMESTAMP=1477921466/*!*/;insert into test_autoinc_lock(a) select * from test_auto
總結：
1 innodb row復制時，可將innodb_autoinc_lock_mode設置為2，這時可在所有insert情況下表獲得最大并發度
2 innodb statement復制時，可將innodb_autoinc_lock_mode設置為1，保證復制安全的同時，獲得簡單insert語句的最大并發度
3 myisam引擎情況下，無論什么樣自增id鎖都是表級鎖，設置innodb_autoinc_lock_mode參數無效（測試略）
4 實際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因為innodb是主鍵聚集索引，實際的主鍵值必須按照主鍵順序存取，那么自增id本身就是升序的，那么在插入數據時，底層就不必再做額外的排序操作，也減少了索引頁分裂的次數，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）
以上這篇淺談innodb_autoinc_lock_mode的表現形式和選值參考方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持VeVb武林網。
新聞熱點
疑難解答
