詳解MySQL中如何定位阻塞語(yǔ)句
前言
MySQL 阻塞是指在并發(fā)訪問 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)死鎖等問題,需要馬上進(jìn)行處理。
在 MySQL中,線程阻塞可能是由于以下原因?qū)е拢?/p>
1、鎖沖突:如果兩個(gè)或者多個(gè)線程同時(shí)請(qǐng)求同一個(gè)資源(栗如:同一行或者同一個(gè)表),其中一個(gè)將被阻塞,直到其他線程釋放鎖;
2、長(zhǎng)事務(wù):如果一個(gè)事務(wù)占用鎖的時(shí)間過長(zhǎng),可能會(huì)導(dǎo)致其它事務(wù)長(zhǎng)時(shí)間等待甚至是超時(shí);
3、死鎖:如果兩個(gè)線程或者更多的線程相互等待對(duì)方的資源,將會(huì)發(fā)生死鎖(Deadlock),進(jìn)而導(dǎo)致語(yǔ)句的執(zhí)行阻塞。
如何排查和定位阻塞語(yǔ)句呢,下面來分析下?
MySQL
面對(duì)阻塞的語(yǔ)句如何查看呢?
首先我們來模擬2個(gè)阻塞的場(chǎng)景,然后使用命令來排查定位。
準(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 的行鎖沒有釋放,事務(wù) 2 就處于阻塞中了。
下面來看下如何排查
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
顯示的信息都是來自 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) | +----+------+------------------+--------------------+---------+------+------------+--------------------------------------+
來看下 show processlist
中幾個(gè)參數(shù)的含義:
- Id:線程的標(biāo)識(shí),如果該線程幼有問題,可以直接通過
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í)行的命令,常見的有休眠(sleep),查詢(query),連接(connect)等,更多的可參見官方文檔Thread Command Values;
- Time:表示這個(gè)狀態(tài)持續(xù)的時(shí)間,單位是秒;
- State:表示當(dāng)前執(zhí)行 sql 語(yǔ)句的狀態(tài),例如 executing 表示開始執(zhí)行語(yǔ)句,
Rolling back
表示線程正在回滾事務(wù)。更多的可參見官方文檔 General Thread States; - Info:顯示的是正在執(zhí)行的 sql 語(yǔ)句,不過這個(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í)開始以及事務(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ì)的信息可參見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)的開銷。
3、使用 INNODB_LOCKS 來查詢當(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 | +--------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
來看下每個(gè)字段的含義,更詳細(xì)的可參加The data_locks Table
- lock_id:鎖 id;
- lock_trx_id:擁有鎖的事務(wù)id, 可以和 INNODB_TRX 表 JOIN 得到事務(wù)的詳細(xì)信息;
- lock_mode:鎖的模式,如下鎖的類型,行級(jí)鎖包括:
S、X、IS、IX
,分別代表:共享鎖、排它鎖、意向共享鎖、意向排它鎖。表級(jí)鎖包括:S_GAP、X_GAP、IS_GAP、IX_GAP
和 AUTO_INC,分別代表共享間隙鎖、排它間隙鎖、意向共享間隙鎖、意向排它間隙鎖和自動(dòng)遞增鎖; - lock_type:鎖的類型,RECORD 代表行級(jí)鎖,TABLE 代表表級(jí)鎖;
- lock_table:被鎖定的或者包含鎖記錄的表名稱;
- lock_index:當(dāng)
LOCK_TYPE=’RECORD’
時(shí),表示索引的名稱;否則為 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 來查詢當(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 | +-------------------+-------------------+-----------------+------------------+
來看下每個(gè)字段的含義,跟詳細(xì)的參數(shù)可參見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ǔ)句,通過 show processlist 命令或者 performance_schema 表來查看當(dāng)前正在執(zhí)行的 SQL 語(yǔ)句,就能簡(jiǎn)單的分析出執(zhí)行較長(zhǎng)的 sql 語(yǔ)句,以及正在等待的鎖和事務(wù)信息,找到阻塞的原因;
不過需要看更加詳細(xì)的信息,就需要借助于下面的信息來分析定位。
1、使用 INNODB_TRX 查詢當(dāng)前運(yùn)行的事務(wù);
2、使用 INNODB_LOCKS 來查詢當(dāng)前出現(xiàn)的鎖;
3、使用 INNODB_LOCK_WAITS 來查詢當(dāng)前鎖等待的關(guān)系;
如果某個(gè)事務(wù)已經(jīng)卡住了,可以使用 MySQL的 kill 命令來強(qiáng)制結(jié)束該事務(wù),以釋放資源。
當(dāng)前要徹底結(jié)局問題還是要分析原因,優(yōu)化查詢語(yǔ)句或者業(yè)務(wù)中對(duì) sql 的使用。
到此這篇關(guān)于詳解MySQL中如何定位阻塞語(yǔ)句的文章就介紹到這了,更多相關(guān)MySQL定位阻塞語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
監(jiān)控MySQL主從狀態(tài)的shell腳本
這篇文章主要分享了監(jiān)控MySQL主從狀態(tài)的shell腳本,幫助大家更好的監(jiān)控mysql數(shù)據(jù)庫(kù),保持穩(wěn)定性,感興趣的朋友可以了解下2020-12-12Mysql 本地計(jì)算機(jī)無(wú)法啟動(dòng) mysql 服務(wù) 錯(cuò)誤 1067:進(jìn)程意外終止。
初學(xué)php接觸mysql,遇到一些問題,卸載重裝后,無(wú)法啟動(dòng)mysql服務(wù),網(wǎng)絡(luò)上有很多種說法,我這里將我解決這個(gè)問題的辦法提出2009-12-12MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn)
本文主要介紹了MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn),詳細(xì)的介紹了為什么IN這么慢以及如何優(yōu)化,具有一定的參考價(jià)值,感興趣的可以了解一下2021-07-07使用mysqldump導(dǎo)入數(shù)據(jù)和mysqldump增量備份(mysqldump使用方法)
mysqldump常用于MySQL數(shù)據(jù)庫(kù)邏輯備份,下面看實(shí)例吧2013-12-12Finished with error:Navicat運(yùn)行SQL文件報(bào)錯(cuò)的解決
這篇文章主要介紹了Finished with error:Navicat運(yùn)行SQL文件報(bào)錯(cuò)的問題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-04-04mysql學(xué)習(xí)筆記之基礎(chǔ)知識(shí)
本文是MySQL學(xué)習(xí)筆記系列文章的第一篇,給大家簡(jiǎn)單講解下MySQL的一些檢查操作命令,希望大家能夠喜歡2017-02-02mysql死鎖(dead lock)與鎖等待(lock wait)的出現(xiàn)解決
死鎖和鎖等待是數(shù)據(jù)庫(kù)運(yùn)維中常見的問題,區(qū)別在于死鎖會(huì)自動(dòng)解除,而鎖等待需要手動(dòng)處理,本文就來介紹一下mysql死鎖(dead lock)與鎖等待(lock wait),感興趣的可以了解一下2024-09-09MySQL 中 DATE_FORMAT() 函數(shù)的使用及應(yīng)用場(chǎng)景
DATE_FORMAT() 是 MySQL 中的一個(gè)內(nèi)置函數(shù),用于格式化日期和時(shí)間數(shù)據(jù),它可以根據(jù)指定的格式字符串來展示日期和時(shí)間,使得數(shù)據(jù)更容易閱讀和理解,本文檔將詳細(xì)介紹 DATE_FORMAT() 函數(shù)的使用方法及其常見應(yīng)用場(chǎng)景,感興趣的朋友一起看看吧2024-12-12