案例詳細(xì)說明mysql中innodb_autoinc_lock_mode

前提條件，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，也就是說session2后來釋放了預(yù)計(jì)生成的自增id，將13留給了session3，自增id值的申請(qǐng)完全是串行順序的。

結(jié)論：innodb_autoinc_lock_mode為0時(shí)的，也就是官方說的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，也就是跳過了預(yù)計(jì)分配給session2的14，可以看到自增id值立馬就分配給了session3，而不必等session2執(zhí)行完成

結(jié)論：innodb_autoinc_lock_mode為1時(shí)的，也就是官方說的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ù)的（不過個(gè)人認(rèn)為這個(gè)往往不是很重要，也沒必要根據(jù)自增id值來統(tǒng)計(jì)行數(shù)之類）

條件3 innodb_autoinc_lock_mode設(shè)置為2

先說結(jié)論：當(dāng)innodb_autoinc_lock_mode設(shè)置為2時(shí)，所有insert種類的SQL都可以立馬獲得鎖并釋放，這時(shí)的效率最高。但是會(huì)引入一個(gè)新的問題：當(dāng)binlog_format為statement時(shí)，這時(shí)的復(fù)制沒法保證安全，因?yàn)榕�量的insert，比如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)問題原因，第一條批量insert還沒執(zhí)行完時(shí)，第二條簡(jiǎn)單insert執(zhí)行時(shí)獲得了自增id值為1376236的鎖，這時(shí)在主庫(kù)寫入是沒有問題的，但是反應(yīng)到從庫(kù)時(shí)，因?yàn)槭腔�于statement的復(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引擎情況下，無論什么樣自增id鎖都是表級(jí)鎖，設(shè)置innodb_autoinc_lock_mode參數(shù)無效（測(cè)試略）

4 實(shí)際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因?yàn)閕nnodb是主鍵聚集索引，實(shí)際的主鍵值必須按照主鍵順序存取，那么自增id本身就是升序的，那么在插入數(shù)據(jù)時(shí)，底層就不必再做額外的排序操作，也減少了索引頁(yè)分裂的次數(shù)，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）

【相關(guān)推薦】

1. Mysql免費(fèi)視頻教程

2. MySQL中添加新用戶權(quán)限的實(shí)例詳解

3. MySQL修改密碼和訪問限制的實(shí)例詳解

4. 用正則表達(dá)式替換數(shù)據(jù)庫(kù)中的內(nèi)容的實(shí)例詳解

5. php將圖片儲(chǔ)存mysql中的實(shí)例詳解

以上就是實(shí)例詳解mysql中innodb_autoinc_lock_mode的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！