目錄
- 1、背景
- 2、表鎖導(dǎo)致的慢查詢(xún)的問(wèn)題
- 3、線(xiàn)上修改表結(jié)構(gòu)有哪些風(fēng)險(xiǎn)？
- 4、一個(gè)死鎖問(wèn)題的分析
- 5、鎖等待問(wèn)題的分析
- 6、小結(jié)
1、背景
對(duì)于數(shù)據(jù)庫(kù)系統(tǒng)來(lái)說(shuō)在多用戶(hù)并發(fā)條件下提高并發(fā)性的同時(shí)又要保證數(shù)據(jù)的一致性一直是數(shù)據(jù)庫(kù)系統(tǒng)追求的目標(biāo)，既要滿(mǎn)足大量并發(fā)訪問(wèn)的需求又必須保證在此條件下數(shù)據(jù)的安全，為了滿(mǎn)足這一目標(biāo)大多數(shù)數(shù)據(jù)庫(kù)通過(guò)鎖和事務(wù)機(jī)制來(lái)實(shí)現(xiàn)，MySQL數(shù)據(jù)庫(kù)也不例外。盡管如此我們?nèi)匀粫?huì)在業(yè)務(wù)開(kāi)發(fā)過(guò)程中遇到各種各樣的疑難問(wèn)題，本文將以案例的方式演示常見(jiàn)的并發(fā)問(wèn)題并分析解決思路。
2、表鎖導(dǎo)致的慢查詢(xún)的問(wèn)題
首先我們看一個(gè)簡(jiǎn)單案例，根據(jù)ID查詢(xún)一條用戶(hù)信息：
mysql> select * from user where id=6;
這個(gè)表的記錄總數(shù)為3條，但卻執(zhí)行了13秒。
出現(xiàn)這種問(wèn)題我們首先想到的是看看當(dāng)前MySQL進(jìn)程狀態(tài)：
從進(jìn)程上可以看出select語(yǔ)句是在等待一個(gè)表鎖，那么這個(gè)表鎖又是什么查詢(xún)產(chǎn)生的呢？這個(gè)結(jié)果中并沒(méi)有顯示直接的關(guān)聯(lián)關(guān)系，但我們可以推測(cè)多半是那條update語(yǔ)句產(chǎn)生的（因?yàn)檫M(jìn)程中沒(méi)有其他可疑的SQL），為了印證我們的猜測(cè)，先檢查一下user表結(jié)構(gòu)：
果然user表使用了MyISAM存儲(chǔ)引擎，MyISAM在執(zhí)行操作前會(huì)產(chǎn)生表鎖，操作完成再自動(dòng)解鎖。如果操作是寫(xiě)操作，則表鎖類(lèi)型為寫(xiě)鎖，如果操作是讀操作則表鎖類(lèi)型為讀鎖。正如和你理解的一樣寫(xiě)鎖將阻塞其他操作(包括讀和寫(xiě))，這使得所有操作變?yōu)榇?；而讀鎖情況下讀-讀操作可以并行，但讀-寫(xiě)操作仍然是串行。以下示例演示了顯式指定了表鎖（讀鎖），讀-讀并行，讀-寫(xiě)串行的情況。
顯式開(kāi)啟/關(guān)閉表鎖，使用lock table user read/write; unlock tables;
session1:
session2：
可以看到會(huì)話(huà)1啟用表鎖（讀鎖）執(zhí)行讀操作，這時(shí)會(huì)話(huà)2可以并行執(zhí)行讀操作，但寫(xiě)操作被阻塞。接著看：
session1:
session2:
當(dāng)session1執(zhí)行解鎖后，seesion2則立刻開(kāi)始執(zhí)行寫(xiě)操作，即讀-寫(xiě)串行。
總結(jié)：
到此我們把問(wèn)題的原因基本分析清楚，總結(jié)一下——MyISAM存儲(chǔ)引擎執(zhí)行操作時(shí)會(huì)產(chǎn)生表鎖，將影響其他用戶(hù)對(duì)該表的操作，如果表鎖是寫(xiě)鎖，則會(huì)導(dǎo)致其他用戶(hù)操作串行，如果是讀鎖則其他用戶(hù)的讀操作可以并行。所以有時(shí)我們遇到某個(gè)簡(jiǎn)單的查詢(xún)花了很長(zhǎng)時(shí)間，看看是不是這種情況。
解決辦法：
1）、盡量不用MyISAM存儲(chǔ)引擎，在MySQL8.0版本中已經(jīng)去掉了所有的MyISAM存儲(chǔ)引擎的表，推薦使用InnoDB存儲(chǔ)引擎。
2）、如果一定要用MyISAM存儲(chǔ)引擎，減少寫(xiě)操作的時(shí)間；
3、線(xiàn)上修改表結(jié)構(gòu)有哪些風(fēng)險(xiǎn)？
如果有一天業(yè)務(wù)系統(tǒng)需要增大一個(gè)字段長(zhǎng)度，能否在線(xiàn)上直接修改呢？在回答這個(gè)問(wèn)題前，我們先來(lái)看一個(gè)案例：
以上語(yǔ)句嘗試修改user表的name字段長(zhǎng)度，語(yǔ)句被阻塞。按照慣例，我們檢查一下當(dāng)前進(jìn)程：
從進(jìn)程可以看出alter語(yǔ)句在等待一個(gè)元數(shù)據(jù)鎖，而這個(gè)元數(shù)據(jù)鎖很可能是上面這條select語(yǔ)句引起的，事實(shí)正是如此。在執(zhí)行DML（select、update、delete、insert）操作時(shí)，會(huì)對(duì)表增加一個(gè)元數(shù)據(jù)鎖，這個(gè)元數(shù)據(jù)鎖是為了保證在查詢(xún)期間表結(jié)構(gòu)不會(huì)被修改，因此上面的alter語(yǔ)句會(huì)被阻塞。那么如果執(zhí)行順序相反，先執(zhí)行alter語(yǔ)句，再執(zhí)行DML語(yǔ)句呢？DML語(yǔ)句會(huì)被阻塞嗎？例如我正在線(xiàn)上環(huán)境修改表結(jié)構(gòu)，線(xiàn)上的DML語(yǔ)句會(huì)被阻塞嗎？答案是：不確定。
在MySQL5.6開(kāi)始提供了online ddl功能，允許一些DDL語(yǔ)句和DML語(yǔ)句并發(fā)，在當(dāng)前5.7版本對(duì)online ddl又有了增強(qiáng)，這使得大部分DDL操作可以在線(xiàn)進(jìn)行。詳見(jiàn)：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
所以對(duì)于特定場(chǎng)景執(zhí)行DDL過(guò)程中，DML是否會(huì)被阻塞需要視場(chǎng)景而定。
總結(jié)：通過(guò)這個(gè)例子我們對(duì)元數(shù)據(jù)鎖和online ddl有了一個(gè)基本的認(rèn)識(shí)，如果我們?cè)跇I(yè)務(wù)開(kāi)發(fā)過(guò)程中有在線(xiàn)修改表結(jié)構(gòu)的需求，可以參考以下方案：
1、盡量在業(yè)務(wù)量小的時(shí)間段進(jìn)行；
2、查看官方文檔，確認(rèn)要做的表修改可以和DML并發(fā)，不會(huì)阻塞線(xiàn)上業(yè)務(wù)；
3、推薦使用percona公司的pt-online-schema-change工具，該工具被官方的online ddl更為強(qiáng)大，它的基本原理是：通過(guò)insert… select…語(yǔ)句進(jìn)行一次全量拷貝，通過(guò)觸發(fā)器記錄表結(jié)構(gòu)變更過(guò)程中產(chǎn)生的增量，從而達(dá)到表結(jié)構(gòu)變更的目的。
例如要對(duì)A表進(jìn)行變更，主要步驟為：
創(chuàng)建目的表結(jié)構(gòu)的空表，A_new;
在A表上創(chuàng)建觸發(fā)器，包括增、刪、改觸發(fā)器;
通過(guò)insert…select…limit N 語(yǔ)句分片拷貝數(shù)據(jù)到目的表
Copy完成后，將A_new表rename到A表。
4、一個(gè)死鎖問(wèn)題的分析
在線(xiàn)上環(huán)境下死鎖的問(wèn)題偶有發(fā)生，死鎖是因?yàn)閮蓚€(gè)或多個(gè)事務(wù)相互等待對(duì)方釋放鎖，導(dǎo)致事務(wù)永遠(yuǎn)無(wú)法終止的情況。為了分析問(wèn)題，我們下面將模擬一個(gè)簡(jiǎn)單死鎖的情況，然后從中總結(jié)出一些分析思路。
演示環(huán)境：MySQL5.7.20 事務(wù)隔離級(jí)別：RR
表user：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
下面演示事務(wù)1、事務(wù)2工作的情況：
事務(wù)1
事務(wù)2
事務(wù)監(jiān)控
T1
begin;
Query OK, 0 rows affected (0.00 sec)
begin;
Query OK, 0 rows affected (0.00 sec)
T2
select * from user where id=3 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | sun | 20 |
+----+------+------+
1 row in set (0.00 sec)
select * from user where id=4 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 4 | zhou | 21 |
+----+------+------+
1 row in set (0.00 sec)
select * from information_schema.INNODB_TRX；
通過(guò)查詢(xún)?cè)獢?shù)據(jù)庫(kù)innodb事務(wù)表，監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為2，即事務(wù)1、事務(wù)2。
T3
update user set name='haha' where id=4;
因?yàn)閕d=4的記錄已被事務(wù)2加上行鎖，該語(yǔ)句將阻塞
監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為2。 T4 阻塞狀態(tài)
update user set name='hehe' where id=3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
id=3的記錄已被事務(wù)1加上行鎖，而本事務(wù)持有id=4的記錄行鎖，此時(shí)InnoDB存儲(chǔ)引擎檢查出死鎖，本事務(wù)被回滾。
事務(wù)2被回滾，事務(wù)1仍在運(yùn)行中，監(jiān)控當(dāng)前運(yùn)行事務(wù)數(shù)為1。 T5
Query OK, 1 row affected (20.91 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由于事務(wù)2被回滾，原來(lái)阻塞的update語(yǔ)句被繼續(xù)執(zhí)行。
監(jiān)控當(dāng)前運(yùn)行事務(wù)數(shù)為1。 T6
commit；
Query OK, 0 rows affected (0.00 sec)
事務(wù)1已提交、事務(wù)2已回滾，監(jiān)控當(dāng)前運(yùn)行事務(wù)數(shù)為0。
這是一個(gè)簡(jiǎn)單的死鎖場(chǎng)景，事務(wù)1、事務(wù)2彼此等待對(duì)方釋放鎖，InnoDB存儲(chǔ)引擎檢測(cè)到死鎖發(fā)生，讓事務(wù)2回滾，這使得事務(wù)1不再等待事務(wù)B的鎖，從而能夠繼續(xù)執(zhí)行。那么InnoDB存儲(chǔ)引擎是如何檢測(cè)到死鎖的呢？為了弄明白這個(gè)問(wèn)題，我們先檢查此時(shí)InnoDB的狀態(tài)：
show engine innodb status\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-01-14 12:17:13 0x70000f1cc000
*** (1) TRANSACTION:
TRANSACTION 5120, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating
update user set name='haha' where id=4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5121, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating
update user set name='hehe' where id=3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 0000000013fa; asc ;;
2: len 7; hex 520000060129a6; asc R ) ;;
3: len 4; hex 68616861; asc haha;;
4: len 4; hex 80000015; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 0000000013fe; asc ;;
2: len 7; hex 5500000156012f; asc U V /;;
3: len 4; hex 68656865; asc hehe;;
4: len 4; hex 80000014; asc ;;
*** WE ROLL BACK TRANSACTION (2)
InnoDB狀態(tài)有很多指標(biāo)，這里我們截取死鎖相關(guān)的信息，可以看出InnoDB可以輸出最近出現(xiàn)的死鎖信息，其實(shí)很多死鎖監(jiān)控工具也是基于此功能開(kāi)發(fā)的。
在死鎖信息中，顯示了兩個(gè)事務(wù)等待鎖的相關(guān)信息（藍(lán)色代表事務(wù)1、綠色代表事務(wù)2），重點(diǎn)關(guān)注：WAITING FOR THIS LOCK TO BE GRANTED和HOLDS THE LOCK(S)。
WAITING FOR THIS LOCK TO BE GRANTED表示當(dāng)前事務(wù)正在等待的鎖信息，從輸出結(jié)果看出事務(wù)1正在等待heap no為5的行鎖，事務(wù)2正在等待 heap no為7的行鎖；
HOLDS THE LOCK(S)：表示當(dāng)前事務(wù)持有的鎖信息，從輸出結(jié)果看出事務(wù)2持有heap no為5行鎖。
從輸出結(jié)果看出，最后InnoDB回滾了事務(wù)2。
那么InnoDB是如何檢查出死鎖的呢？
我們想到最簡(jiǎn)單方法是假如一個(gè)事務(wù)正在等待一個(gè)鎖，如果等待時(shí)間超過(guò)了設(shè)定的閾值，那么該事務(wù)操作失敗，這就避免了多個(gè)事務(wù)彼此長(zhǎng)等待的情況。參數(shù)innodb_lock_wait_timeout正是用來(lái)設(shè)置這個(gè)鎖等待時(shí)間的。
如果按照這個(gè)方法，解決死鎖是需要時(shí)間的（即等待超過(guò)innodb_lock_wait_timeout設(shè)定的閾值），這種方法稍顯被動(dòng)而且影響系統(tǒng)性能，InnoDB存儲(chǔ)引擎提供一個(gè)更好的算法來(lái)解決死鎖問(wèn)題，wait-for graph算法。簡(jiǎn)單的說(shuō)，當(dāng)出現(xiàn)多個(gè)事務(wù)開(kāi)始彼此等待時(shí)，啟用wait-for graph算法，該算法判定為死鎖后立即回滾其中一個(gè)事務(wù)，死鎖被解除。該方法的好處是：檢查更為主動(dòng)，等待時(shí)間短。
下面是wait-for graph算法的基本原理：
為了便于理解，我們把死鎖看做4輛車(chē)彼此阻塞的場(chǎng)景：
4輛車(chē)看做4個(gè)事務(wù)，彼此等待對(duì)方的鎖，造成死鎖。wait-for graph算法原理是把事務(wù)作為節(jié)點(diǎn)，事務(wù)之間的鎖等待關(guān)系，用有向邊表示，例如事務(wù)A等待事務(wù)B的鎖，就從節(jié)點(diǎn)A畫(huà)一條有向邊到節(jié)點(diǎn)B，這樣如果A、B、C、D構(gòu)成的有向圖，形成了環(huán)，則判斷為死鎖。這就是wait-for graph算法的基本原理。
總結(jié)：
1、如果我們業(yè)務(wù)開(kāi)發(fā)中出現(xiàn)死鎖如何檢查出？剛才已經(jīng)介紹了通過(guò)監(jiān)控InnoDB狀態(tài)可以得出，你可以做一個(gè)小工具把死鎖的記錄收集起來(lái)，便于事后查看。
2、如果出現(xiàn)死鎖，業(yè)務(wù)系統(tǒng)應(yīng)該如何應(yīng)對(duì)？從上文我們可以看到當(dāng)InnoDB檢查出死鎖后，對(duì)客戶(hù)端報(bào)出一個(gè)Deadlock found when trying to get lock; try restarting transaction信息，并且回滾該事務(wù)，應(yīng)用端需要針對(duì)該信息，做事務(wù)重啟的工作，并保存現(xiàn)場(chǎng)日志事后做進(jìn)一步分析，避免下次死鎖的產(chǎn)生。
5、鎖等待問(wèn)題的分析
在業(yè)務(wù)開(kāi)發(fā)中死鎖的出現(xiàn)概率較小，但鎖等待出現(xiàn)的概率較大，鎖等待是因?yàn)橐粋€(gè)事務(wù)長(zhǎng)時(shí)間占用鎖資源，而其他事務(wù)一直等待前個(gè)事務(wù)釋放鎖。
事務(wù)1
事務(wù)2
事務(wù)監(jiān)控
T1
begin;
Query OK, 0 rows affected (0.00 sec)
begin;
Query OK, 0 rows affected (0.00 sec)
T2
select * from user where id=3 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | sun | 20 |
+----+------+------+
1 row in set (0.00 sec)
其他查詢(xún)操作
select * from information_schema.INNODB_TRX；
通過(guò)查詢(xún)?cè)獢?shù)據(jù)庫(kù)innodb事務(wù)表，監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為2，即事務(wù)1、事務(wù)2。
T3 其他查詢(xún)操作
update user set name='hehe' where id=3;
因?yàn)閕d=3的記錄被事務(wù)1加上行鎖，所以該語(yǔ)句將阻塞（即鎖等待）
監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為2。 T4 其他查詢(xún)操作
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
鎖等待時(shí)間超過(guò)閾值，操作失敗。注意：此時(shí)事務(wù)2并沒(méi)有回滾。
監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為2。 T5 commit; 事務(wù)1已提交，事務(wù)2未提交，監(jiān)控到當(dāng)前運(yùn)行事務(wù)數(shù)為1。
從上述可知事務(wù)1長(zhǎng)時(shí)間持有id=3的行鎖，事務(wù)2產(chǎn)生鎖等待，等待時(shí)間超過(guò)innodb_lock_wait_timeout后操作中斷，但事務(wù)并沒(méi)有回滾。如果我們業(yè)務(wù)開(kāi)發(fā)中遇到鎖等待，不僅會(huì)影響性能，還會(huì)給你的業(yè)務(wù)流程提出挑戰(zhàn)，因?yàn)槟愕臉I(yè)務(wù)端需要對(duì)鎖等待的情況做適應(yīng)的邏輯處理，是重試操作還是回滾事務(wù)。
在MySQL元數(shù)據(jù)表中有對(duì)事務(wù)、鎖等待的信息進(jìn)行收集，例如information_schema數(shù)據(jù)庫(kù)下的INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS，你可以通過(guò)這些表觀察你的業(yè)務(wù)系統(tǒng)鎖等待的情況。你也可以用一下語(yǔ)句方便的查詢(xún)事務(wù)和鎖等待的關(guān)聯(lián)關(guān)系：
|
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query wating_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
結(jié)果：
waiting_trx_id: 5132
waiting_thread: 11
wating_query: update user set name='hehe' where id=3
blocking_trx_id: 5133
blocking_thread: 10
blocking_query: NULL
總結(jié)：
1、請(qǐng)對(duì)你的業(yè)務(wù)系統(tǒng)做鎖等待的監(jiān)控，這有助于你了解當(dāng)前數(shù)據(jù)庫(kù)鎖情況，以及為你優(yōu)化業(yè)務(wù)程序提供幫助；
2、業(yè)務(wù)系統(tǒng)中應(yīng)該對(duì)鎖等待超時(shí)的情況做合適的邏輯判斷。
6、小結(jié)
本文通過(guò)幾個(gè)簡(jiǎn)單的示例介紹了我們常用的幾種MySQL并發(fā)問(wèn)題，并嘗試得出針對(duì)這些問(wèn)題我們排查的思路。文中涉及事務(wù)、表鎖、元數(shù)據(jù)鎖、行鎖，但引起并發(fā)問(wèn)題的遠(yuǎn)遠(yuǎn)不止這些，例如還有事務(wù)隔離級(jí)別、GAP鎖等。真實(shí)的并發(fā)問(wèn)題可能多而復(fù)雜，但排查思路和方法卻是可以復(fù)用，在本文中我們使用了show processlist;show engine innodb status;以及查詢(xún)?cè)獢?shù)據(jù)表的方法來(lái)排查發(fā)現(xiàn)問(wèn)題，如果問(wèn)題涉及到了復(fù)制，還需要借助master/slave監(jiān)控來(lái)協(xié)助。
您可能感興趣的文章:- MySQL并發(fā)更新數(shù)據(jù)時(shí)的處理方法
- Tomcat+Mysql高并發(fā)配置優(yōu)化講解
- PHP利用Mysql鎖解決高并發(fā)的方法
- PHP+MySQL高并發(fā)加鎖事務(wù)處理問(wèn)題解決方法
- Yii+MYSQL鎖表防止并發(fā)情況下重復(fù)數(shù)據(jù)的方法
- MySQL中實(shí)現(xiàn)高性能高并發(fā)計(jì)數(shù)器方案（例如文章點(diǎn)擊數(shù)）
- MySQL中SELECT+UPDATE處理并發(fā)更新問(wèn)題解決方案分享
- 利用mysql事務(wù)特性實(shí)現(xiàn)并發(fā)安全的自增ID示例
- 深入mysql并發(fā)插入優(yōu)化詳解
- Mysql事務(wù)并發(fā)問(wèn)題解決方案
