昨晚我正在床上睡得著著的，突然來了一條短信。
什么？線上的訂單無法取消！
我趕緊登錄線上系統(tǒng)，查看業(yè)務(wù)日志。
發(fā)現(xiàn)有 MySQL鎖超時(shí) 的錯(cuò)誤日志。
不用想，肯定有另一個(gè)事務(wù)正在修改這條訂單，持有這條訂單的鎖。
導(dǎo)致當(dāng)前事務(wù)獲取不到鎖，一直等待，直到超過鎖超時(shí)時(shí)間，然后報(bào)錯(cuò)。
既然問題已經(jīng)清楚了，接下來就輪到怎么排查一下到底是哪個(gè)事務(wù)正在持有這條訂單的鎖。
好在MySQL提供了豐富的工具，幫助我們排查鎖競爭問題。
現(xiàn)場復(fù)現(xiàn)一個(gè)這個(gè)問題：
創(chuàng)建一張用戶表，造點(diǎn)數(shù)據(jù)：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
復(fù)制代碼</pre>
事務(wù)1，更新id=1的用戶姓名，不提交事務(wù)：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">begin;
update user set name='一燈' where id=1;
復(fù)制代碼</pre>
事務(wù)2，刪除id=1的數(shù)據(jù)，這時(shí)候會(huì)產(chǎn)生鎖等待：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">begin;
delete from user where id=1;
復(fù)制代碼</pre>
接下來，我們就通過MySQL提供的鎖競爭統(tǒng)計(jì)表，排查一下鎖等待問題：
先查一下鎖等待情況：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">select * from information_schema.innodb_lock_waits;
復(fù)制代碼</pre>
可以看到有一個(gè)鎖等待的事務(wù)。
然后再查一下正在競爭的鎖有哪些？
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">select * from information_schema.innodb_locks;
復(fù)制代碼</pre>
可以看到，MySQL統(tǒng)計(jì)的非常詳細(xì)：
<pre class="prettyprint hljs nginx" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">lock_trx_id 表示事務(wù)ID
lock_mode 表示排它鎖還是共享鎖
lock_type 表示鎖定的記錄，還是范圍
lock_table 鎖的表名
lock_index 鎖定的是主鍵索引
</pre>
再查一下正在執(zhí)行的事務(wù)有哪些？
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">select * from information_schema.innodb_trx;
復(fù)制代碼</pre>
可以清楚的看到正在執(zhí)行的事務(wù)有兩個(gè)，一個(gè)狀態(tài)是鎖等待（ LOCK WAIT ），正在執(zhí)行的SQL也打印出來了：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">delete from user where id=1;
復(fù)制代碼</pre>
正是事務(wù)2的刪除語句。
不用問，第二條，顯示正在運(yùn)行狀態(tài)（RUNNING）的事務(wù)就是正在持有鎖的事務(wù)1，MySQL線程id（ trx_mysql_thread_id ）是193。
我們用MySQL線程id查一下事務(wù)線程id：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">select * from performance_schema.threads where processlist_id=193;
復(fù)制代碼</pre>
找到對應(yīng)的事務(wù)線程id是218，然后再找一下這個(gè)線程正在執(zhí)行的SQL語句：
<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">select THREAD_ID,CURRENT_SCHEMA,SQL_TEXT
from performance_schema.events_statements_current
where thread_id=218;
復(fù)制代碼</pre>
可以清楚的看到這個(gè)線程正在執(zhí)行的SQL語句就是事務(wù)1的update語句。
以上是基于MySQL5.7版本，在MySQL8.0版本中有些命令已經(jīng)刪除了，替換成了其他命令，下篇文章再講一下MySQL8.0怎么定位 MySQL鎖超時(shí) 問題。
