下面小編就為大家帶來一篇淺談innodb_autoinc_lock_mode的表現形式和選值參考方法。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
前提條件，percona 5.6版本，事務隔離級別為RR
mysql>?show?create?table?test_autoinc_lockG ***************************?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設置為0
session1 ?begin;delete?from?test_autoinc_lock?where?a>7;//這時未提交 session2 mysql>?insert?into?test_autoinc_lock(a)?values(100);//gap鎖的存在，這時處于鎖等待 session3 mysql>?insert?into?test_autoinc_lock(a)?values(2);//這時同樣處于等待狀態，理論上這個不是gap鎖的鎖定范圍，那么它是在等什么呢 session4 mysql>?select?*?from?information_schema.innodb_trxG ***************************?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
這時查看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
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)//注意看這時的最大自增值是13 session2 mysql>?insert?into?test_autoinc_lock(a)?values(100);//同樣gap鎖的存在，這時處于鎖等待 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，也就是跳過了預計分配給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?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執行完成之前執行） 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?statusG ***************************?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=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
總結：
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速度（除非其他方案也能保證主鍵完全自增）
【相關推薦】
1.?退出
2.?退出
3.?退出
4.?退出
5.?退出
