生活随笔
收集整理的這篇文章主要介紹了
自增锁引发的悲剧
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
背景
先描述下故障吧
1. MySQL5.6.27
2. InnoDB
3. Centos基本介紹完畢，應該跟大部分公司的實例一樣CREATETABLE`new_table`(`id` int(11) NOT NULL AUTO_INCREMENT,`x` varchar(200) DEFAULT '',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5908151 DEFAULT CHARSET=utf8
CREATE TABLE `old_table` (`id` int(11) NOT NULL AUTO_INCREMENT, `xx` varchar(200) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5908151 DEFAULT CHARSET=utf8 - step1: 業務需要導入歷史數據到新表，新表有寫入
1. insertintonew_table(x)selectxxfromold_table2. 批量插入在new_table上
showprocesslist;看到好多語句都處于executing階段，DB假死，任何語句都非常慢，too many connection
showengineinnodbstatu\G結果：==
lock==
模擬問題，場景復現
讓問題再次發生才好定位解決問題
| t_inc | CREATETABLE`t_inc`(`id` int(11) NOT NULL AUTO_INCREMENT,`x` varchar(199) DEFAULT '',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5908151 DEFAULT CHARSET=utf8 |
CREATE TABLE `t_inc_template` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `cookie_unique` varchar(255) NOT NULL DEFAULT '' COMMENT '', PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=5857489 DEFAULT CHARSET=utf8 session1：insertintot_inc(x)selectcookie_uniquefromt_inc_template;session2：mysqlslap -hxx -ulc_rx -plc_rx -P3306 --concurrency=
10 --iterations=1000 --create-schema='lc' --query="insert into t_inc(x) select 'lanchun';" --number-of-queries=10 產生并發,然其自動分配自增id。 | 260126 | lc_rx | x:22833 | NULL | Sleep | 8 | | NULL | | 260127 | lc_rx | x:22834 | lc | Query | 8 | executing | insertintot_inc(x)select'lanchun'| | 260128 | lc_rx | x:22835 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260129 | lc_rx | x:22836 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260130 | lc_rx | x:22837 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260131 | lc_rx | x:22838 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260132 | lc_rx | x:22840 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260133 | lc_rx | x:22839 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260134 | lc_rx | x:22842 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260135 | lc_rx | x:22841 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | | 260136 | lc_rx | x:22843 | lc | Query | 8 | executing | insert into t_inc(x) select 'lanchun' | - step3 show engine innodb status
TABLE LOCKtable
`lc`.`t_inc`trx id113776506lockmodeAUTO-INC waiting一堆這樣的waiting然后卡死
好了問題已經復現，大概也知道是什么原因造成了，那就是：AUTO-INC lock
自增鎖
接下來聊聊自增鎖
和auto_increment相關的insert種類
INSERT-like解釋：任何會產生新記錄的語句，都叫上
INSERT-like，比如：INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA 總之包括：“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts. simple insert插入的記錄行數是確定的：比如：
insert into values，replace
但是不包括： INSERT ... ON DUPLICATE KEY UPDATE. Bulk inserts插入的記錄行數不能馬上確定的，比如：
INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA Mixed-mode inserts這些都是simple-
insert，但是部分auto increment值給定或者不給定1. INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 2. INSERT...ONDUPLICATEKEYUPDATE 以上都是Mixed-mode inserts 鎖模式
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)優點：極其安全缺點：對于這種模式，寫入性能最差，因為任何一種
insert-like語句，都會產生一個table-levelAUTO-INClock
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)原理：這是默認鎖模式，當發生bulk inserts的時候，會產生一個特殊的AUTO-INC table-level
lock直到語句結束，注意：（這里是語句結束就釋放鎖，并不是事務結束哦，因為一個事務可能包含很多語句）對于Simple inserts，則使用的是一種輕量級鎖，只要獲取了相應的auto increment就釋放鎖，并不會等到語句結束。PS：當發生AUTO-INC table-level lock的時候，這種輕量級的鎖也不會加鎖成功，會等待。。。。 優點：非常安全，性能與innodb_autoinc_lock_mode = 0相比要好很多。 缺點：還是會產生表級別的自增鎖 深入思考： 為什么這個模式要產生表級別的鎖呢？ 因為：他要保證bulk insert自增id的連續性，防止在bulk insert的時候，被其他的insert語句搶走auto increment值。 innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)原理：當進行bulk
insert的時候，不會產生table級別的自增鎖，因為它是允許其他insert插入的。來一個記錄，插入分配一個auto 值，不會預分配。優點：性能非常好，提高并發，SBR不安全
缺點：一條bulk insert，得到的自增id可能不連續 SBR模式下：會導致復制出錯，不一致 延伸
當innodb_autoinc_lock_mode = 2 ，SBR為什么不安全
表結構：a primary key auto_increment,b varchar(3)
time_logic_clocksession1：bulk insert（）session2： insert like
| 0 | 1,A | ? | ? |
| 1 | ? | 2,AA |
| 2 | 3,B | ? | ? |
| 3 | 4,C | ? | ? |
| 4 | ? | 5,CC |
| 5 | 6,D | ? |
最終的結果是：
因為binlog中session2的語句先執行完，導致結果為
RBR為什么就安全呢？
因為RBR都是根據row image來的，跟語句沒關系的。
好了，通過以上對比分析，相信大家都知道該如何抉擇了吧？
總結
- 如果你的binlog-format是row模式，而且不關心一條bulk-insert的auto值連續（一般不用關心），那么設置innodb_autoinc_lock_mode = 2 可以提高更好的寫入性能。
轉載于:https://www.cnblogs.com/zping/p/6513710.html
總結
以上是生活随笔為你收集整理的自增锁引发的悲剧的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
