下面小編就為大家帶來一篇淺談innodb_autoinc_lock_mode的表現形式和選值參考方法。小編覺得蠻不錯的,現在就分享給大家,也給大家做個參考。一起跟著小編過來看看吧
前提條件,percona 5.6版本,交易隔離等級為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設定為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_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
這時查看session3是等待自增鎖,一直處於setting auto-inc lock狀態
#session2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
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 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=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. Mysql免費影片教學
以上是實例詳解mysql中innodb_autoinc_lock_mode的詳細內容。更多資訊請關注PHP中文網其他相關文章!