實例詳解mysql中innodb_autoinc_lock_mode

Y2J
發布: 2017-05-24 13:36:51
原創
2478 人瀏覽過

下面小編就為大家帶來一篇淺談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

這時再看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免費影片教學

2. MySQL中新增使用者權限的實例詳解

3. MySQL修改密碼和存取限制的實例詳解

4. 用正規表示式取代資料庫中的內容的實例詳解

5. php將圖片儲存mysql中的實例詳解

以上是實例詳解mysql中innodb_autoinc_lock_mode的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板