前言

MySQL 阻塞是指在并發(fā)訪問(wèn) MySQL 數(shù)據(jù)庫(kù)時(shí)，某個(gè)事務(wù)占用了資源并且長(zhǎng)時(shí)間不釋放，導(dǎo)致其他事務(wù)無(wú)法執(zhí)行或執(zhí)行緩慢的情況。

MySQL 阻塞可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)性能下降，甚至出現(xiàn)死鎖等問(wèn)題，需要馬上進(jìn)行處理。

在 MySQL中，線程阻塞可能是由于以下原因?qū)е拢?/p>

1、鎖沖突：如果兩個(gè)或者多個(gè)線程同時(shí)請(qǐng)求同一個(gè)資源(栗如：同一行或者同一個(gè)表)，其中一個(gè)將被阻塞，直到其他線程釋放鎖；

2、長(zhǎng)事務(wù)：如果一個(gè)事務(wù)占用鎖的時(shí)間過(guò)長(zhǎng)，可能會(huì)導(dǎo)致其它事務(wù)長(zhǎng)時(shí)間等待甚至是超時(shí)；

3、死鎖：如果兩個(gè)線程或者更多的線程相互等待對(duì)方的資源，將會(huì)發(fā)生死鎖(Deadlock)，進(jìn)而導(dǎo)致語(yǔ)句的執(zhí)行阻塞。

如何排查和定位阻塞語(yǔ)句呢，下面來(lái)分析下？

MySQL

面對(duì)阻塞的語(yǔ)句如何查看呢？

首先我們來(lái)模擬2個(gè)阻塞的場(chǎng)景，然后使用命令來(lái)排查定位。

準(zhǔn)備數(shù)據(jù)

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t_user values(1,  "小明",12);
insert into t_user values(2, "小紅",20);
insert into t_user values(3, "小白",19);
insert into t_user values(4, "張三",24);
insert into t_user values(5, "李四",25);
insert into t_user values(6, "王五",26);

模擬長(zhǎng)事務(wù)的場(chǎng)景

事務(wù) 1

SET autocommit = 0;
begin;
UPDATE t_user SET age=12  WHERE id=1;
select SLEEP(12000);
commit;

事務(wù) 2

SET autocommit = 0;
begin;
UPDATE t_user SET age=13  WHERE id=1;
commit;

兩個(gè)事務(wù)，第一個(gè)事務(wù)更新語(yǔ)句對(duì) id=1 這一行加了行鎖，同時(shí)這個(gè)事務(wù) sleep 了 120 秒。事務(wù)2同樣更新 id=1 這一行數(shù)據(jù)，也會(huì)加一把行鎖，因?yàn)槭聞?wù) 1 的 sleep，導(dǎo)致事務(wù) 1 的行鎖沒(méi)有釋放，事務(wù) 2 就處于阻塞中了。

下面來(lái)看下如何排查

1、使用 show processlist 查詢正在運(yùn)行的進(jìn)程

show processlist 就是查看當(dāng)前 mysql正 在執(zhí)行的進(jìn)程,主要有兩個(gè)作用：

1、查看慢查詢的sql是哪個(gè)；

2、查看出現(xiàn)鎖的sql是哪個(gè)。

show processlist 顯示的信息都是來(lái)自 MySQL 系統(tǒng)庫(kù) information_schema 中的 processlist 表。也可以直接查詢這個(gè)。

Select * from information_schema.processlist；   

$ show processlist;
+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+
| Id | User | Host             | db                 | Command | Time | State      | Info                                 |
+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+
|  7 | root | 172.21.0.1:56974 | test               | Query   |    3 | updating   | UPDATE t_user SET age=13  WHERE id=1 |
|  8 | root | 172.21.0.1:56976 | information_schema | Query   |    0 | starting   | show processlist                     |
|  9 | root | 172.21.0.1:56978 | test               | Query   | 2120 | User sleep | select SLEEP(12000)                  |
+----+------+------------------+--------------------+---------+------+------------+--------------------------------------+

來(lái)看下 show processlist 中幾個(gè)參數(shù)的含義：

• Id：線程的標(biāo)識(shí)，如果該線程幼有問(wèn)題，可以直接通過(guò) kill <Id>，殺死該線程；
• User：?jiǎn)?dòng)這個(gè)線程的用戶；
• Host: 客戶端 IP 和端口號(hào)。結(jié)合 ss -n | grep :<port> 命令，可以定位到是哪個(gè)進(jìn)程發(fā)送的請(qǐng)求；
• db：當(dāng)前執(zhí)行的命令是在哪個(gè)數(shù)據(jù)庫(kù)；
• Command：顯示正在執(zhí)行的命令，常見(jiàn)的有休眠（sleep），查詢（query），連接（connect）等，更多的可參見(jiàn)官方文檔Thread Command Values;
• Time：表示這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒；
• State：表示當(dāng)前執(zhí)行 sql 語(yǔ)句的狀態(tài),例如 executing 表示開(kāi)始執(zhí)行語(yǔ)句，Rolling back 表示線程正在回滾事務(wù)。更多的可參見(jiàn)官方文檔 General Thread States；
• Info：顯示的是正在執(zhí)行的 sql 語(yǔ)句，不過(guò)這個(gè)只能顯示前100個(gè)字符，要看全部的執(zhí)行 sql，可使用 show full processlist 。

下面列舉幾個(gè)常用的查詢分析栗子

按客戶端 IP 分組，看哪個(gè)客戶端的鏈接數(shù)最多

select
  client_ip, count(client_ip) as client_num
from
	(select 
	substring_index(host, ':', 1) as client_ip
  from 
	information_schema.processlist) as connect_info
group by client_ip order by client_num desc;
+------------+------------+
| client_ip  | client_num |
+------------+------------+
| 172.21.0.1 |          1 |
+------------+------------+

查詢正在執(zhí)行的 sql,根據(jù)時(shí)間倒敘查詢，查詢執(zhí)行時(shí)間較長(zhǎng)的 sql

select
   * 
from 
   information_schema.processlist 
where 
   Command != 'Sleep' 
	 order by Time desc;

2、使用 INNODB_TRX 查詢當(dāng)前運(yùn)行的事務(wù)

INNODB_TRX 表提供了當(dāng)前在 InnoDB 內(nèi)部執(zhí)行的所有事務(wù)信息，包含事務(wù)是否在等待鎖，事務(wù)何時(shí)開(kāi)始以及事務(wù)正在執(zhí)行的 SQL 語(yǔ)句(如果有的話，sql語(yǔ)句阻塞就可以顯示)。

select * from information_schema.innodb_trx where trx_state="LOCK WAIT"\G;
*************************** 1. row ***************************
                    trx_id: 5800
                 trx_state: LOCK WAIT
               trx_started: 2023-07-14 01:34:06
     trx_requested_lock_id: 5800:630:3:8
          trx_wait_started: 2023-07-14 01:34:06
                trx_weight: 2
       trx_mysql_thread_id: 16
                 trx_query: UPDATE t_user SET age=13  WHERE id=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

看幾個(gè)主要的參數(shù)，更詳細(xì)的信息可參見(jiàn)The INFORMATION_SCHEMA INNODB_TRX Table

• trx_id：InnoDB 內(nèi)部的唯一事務(wù) ID，不會(huì)為只讀且非鎖定事務(wù)創(chuàng)建 ID；
• trx_state：事務(wù)的執(zhí)行狀態(tài)。值為 RUNNING(運(yùn)行), LOCK WAIT(等待鎖), ROLLING BACK(正在回滾), 和 COMMITTING(正在提交)；
• trx_query：事務(wù)正在執(zhí)行的 sql;
• trx_isolation_level：事務(wù)的隔離級(jí)別；
• trx_autocommit_non_locking：無(wú)鎖自動(dòng)提交標(biāo)識(shí)。值1表示該事務(wù)是一個(gè) SELECT 語(yǔ)句，不使用 FOR UPDATE 或 LOCK IN SHARED MODE 子句，并且在啟用自動(dòng)提交的情況下執(zhí)行，因此該事務(wù)只包含這一條語(yǔ)句。當(dāng)這一列和 TRX_IS_READ_ONLY 都為 1 時(shí)，InnoDB會(huì)優(yōu)化事務(wù)，以減少與更改表數(shù)據(jù)的事務(wù)相關(guān)的開(kāi)銷(xiāo)。

3、使用 INNODB_LOCKS 來(lái)查詢當(dāng)前出現(xiàn)的鎖

SELECT * FROM information_schema.INNODB_LOCKS; 
+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 5812:630:3:8 | 5812        | X         | RECORD    | `test`.`t_user` | PRIMARY    |        630 |         3 |        8 | 1         |
| 5811:630:3:8 | 5811        | X         | RECORD    | `test`.`t_user` | PRIMARY    |        630 |         3 |        8 | 1         |
+--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+

來(lái)看下每個(gè)字段的含義，更詳細(xì)的可參加The data_locks Table

• lock_id：鎖 id;
• lock_trx_id：擁有鎖的事務(wù)id, 可以和 INNODB_TRX 表 JOIN 得到事務(wù)的詳細(xì)信息；
• lock_mode：鎖的模式，如下鎖的類(lèi)型，行級(jí)鎖包括：S、X、IS、IX，分別代表：共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級(jí)鎖包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分別代表共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動(dòng)遞增鎖；
• lock_type：鎖的類(lèi)型，RECORD 代表行級(jí)鎖，TABLE 代表表級(jí)鎖；
• lock_table：被鎖定的或者包含鎖記錄的表名稱(chēng)；
• lock_index：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示索引的名稱(chēng)；否則為 NULL；
• lock_space：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的表空間 ID；否則為 NULL。
• lock_page：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的頁(yè)號(hào)；否則為 NULL。
• lock_rec：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示一堆頁(yè)面中鎖定行的數(shù)量，亦即被鎖定的記錄號(hào)；否則為 NULL。
• lock_data：當(dāng) LOCK_TYPE=’RECORD’ 時(shí)，表示鎖定行的主鍵；否則為NULL。

4、使用 INNODB_LOCK_WAITS 來(lái)查詢當(dāng)前鎖等待的關(guān)系

SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5812              | 5812:630:3:8      | 5811            | 5811:630:3:8     |
+-------------------+-------------------+-----------------+------------------+

來(lái)看下每個(gè)字段的含義，跟詳細(xì)的參數(shù)可參見(jiàn)The data_lock_waits Table

• requesting_trx_id：請(qǐng)求事務(wù)的 ID ;
• requested_lock_id：事務(wù)所等待的鎖定的 ID。可以和 INNODB_LOCKS 表 JOIN;
• blocking_trx_id：阻塞事務(wù)的 ID;
• blocking_lock_id：某一事務(wù)的鎖的 ID，該事務(wù)阻塞了另一事務(wù)的運(yùn)行?？梢院?INNODB_LOCKS 表 JOIN。

總結(jié)

如果發(fā)現(xiàn)數(shù)據(jù)庫(kù)響應(yīng)變慢，排查阻塞語(yǔ)句，通過(guò) show processlist 命令或者 performance_schema 表來(lái)查看當(dāng)前正在執(zhí)行的 SQL 語(yǔ)句，就能簡(jiǎn)單的分析出執(zhí)行較長(zhǎng)的 sql 語(yǔ)句，以及正在等待的鎖和事務(wù)信息，找到阻塞的原因；

不過(guò)需要看更加詳細(xì)的信息，就需要借助于下面的信息來(lái)分析定位。

1、使用 INNODB_TRX 查詢當(dāng)前運(yùn)行的事務(wù)；

2、使用 INNODB_LOCKS 來(lái)查詢當(dāng)前出現(xiàn)的鎖；

3、使用 INNODB_LOCK_WAITS 來(lái)查詢當(dāng)前鎖等待的關(guān)系；

如果某個(gè)事務(wù)已經(jīng)卡住了，可以使用 MySQL的 kill 命令來(lái)強(qiáng)制結(jié)束該事務(wù)，以釋放資源。

當(dāng)前要徹底結(jié)局問(wèn)題還是要分析原因，優(yōu)化查詢語(yǔ)句或者業(yè)務(wù)中對(duì) sql 的使用。

到此這篇關(guān)于詳解MySQL中如何定位阻塞語(yǔ)句的文章就介紹到這了,更多相關(guān)MySQL定位阻塞語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論