建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
這篇文章給大家分享的是有關(guān)如何查看MySQL鎖等待的原因的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。
10余年的金林網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。營(yíng)銷(xiāo)型網(wǎng)站建設(shè)的優(yōu)勢(shì)是能夠根據(jù)用戶(hù)設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整金林建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)從事“金林網(wǎng)站設(shè)計(jì)”,“金林網(wǎng)站推廣”以來(lái)，每個(gè)客戶(hù)項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。
--sys庫(kù)的介紹
mysql 5.6也可以有sys庫(kù)（基于performance_schema的視圖）。sys庫(kù)是一個(gè)開(kāi)源項(xiàng)目，在githup上早就有，是一個(gè)DBA的開(kāi)源工具，后來(lái)mysql感覺(jué)好，就放在了mysql5.7上。
下載地址：https://github.com/mysql/mysql-sys
[tms@m-db3 ~]$ cd mysql-sys-master
[tms@m-db3 ~]$ mysql < sys_56.sql
這樣，就可以在mysql5.6里面加入sys庫(kù)了，不過(guò)mysql 5.6只有88張表，而mysql 5.7有101張，這是因?yàn)镸ysql 5.7的performace_schema庫(kù)里面又多了幾張表。
sys庫(kù)是performance_schema的視圖。
--MySQL鎖等待
當(dāng)Mysql發(fā)生鎖等待情況時(shí)，可以通過(guò)如下語(yǔ)句來(lái)在線(xiàn)查看：
mysql> select * from sys.innodb_lock_waits \G; *************************** 1. row *************************** wait_started: 2018-07-16 16:25:17 //鎖等待開(kāi)始的時(shí)間,16:25開(kāi)始等待 wait_age: 00:10:08 //發(fā)現(xiàn)問(wèn)題時(shí)已經(jīng)等待了10分鐘了 wait_age_secs: 608 //608秒，也就是等10分鐘了 locked_table: `iws`.`busi_reconciliationgbgsinfo_inputdetails` //被鎖住的表名 locked_index: PRIMARY //被鎖住的索引 locked_type: RECORD //鎖的類(lèi)型為行鎖 waiting_trx_id: 13666265 //waiting transaction id，正在等待事務(wù)的id號(hào) waiting_trx_started: 2018-07-16 16:24:54 //這個(gè)事務(wù)是從16:24開(kāi)始等待 waiting_trx_age: 00:10:31 //等了10分鐘了 waiting_trx_rows_locked: 1 //正在等待的這個(gè)事務(wù)鎖住了1行記錄 waiting_trx_rows_modified: 0 //正在等待的這個(gè)事務(wù)修改了0行記錄 waiting_pid: 441805 //這個(gè)等待事務(wù)的線(xiàn)程id是多少，通過(guò)show processlist 命令可以查到它，結(jié)果看到是一個(gè)sleep的線(xiàn)程，沒(méi)有執(zhí)行具體sql語(yǔ)句，見(jiàn)下 waiting_query: update busi_reconciliationgbgs ... where id = 4510 //等待鎖釋放的語(yǔ)句 waiting_lock_id: 13666265:2924:21:94 //正在等待的鎖id waiting_lock_mode: X //等待鎖的類(lèi)型是排它鎖 blocking_trx_id: 13666259 //這個(gè)事務(wù)id阻塞了waiting lock blocking_pid: 441803 阻塞事務(wù)的pid blocking_query: NULL //阻塞事務(wù)的sql語(yǔ)句 blocking_lock_id: 13666259:2924:21:94 blocking_lock_mode: X blocking_trx_started: 2018-07-16 16:24:51 blocking_trx_age: 00:10:34 blocking_trx_rows_locked: 1 blocking_trx_rows_modified: 1 sql_kill_blocking_query: KILL QUERY 441803 sql_kill_blocking_connection: KILL 441803 1 row in set (0.00 sec) ERROR: No query specified
上面看到輸出了很多的東西，看的我都蒙圈了。后來(lái)查看mysql官方文檔，慢慢的才發(fā)現(xiàn)，其實(shí)只關(guān)注上面的waiting_pid、waiting_query和blocking_pid、blocking_query四個(gè)參數(shù)即可；其中waiting_pid和blocking_pid兩個(gè)參數(shù)就是通過(guò)執(zhí)行show processlist命令里面輸出的線(xiàn)程id號(hào)，如下：
mysql> show full processlist \G; *************************** 8. row *************************** Id: 441803 User: iws Host: 172.16.21.7:46121 db: iws Command: Sleep Time: 655 State: Info: NULL *************************** 9. row *************************** Id: 441805 User: iws Host: 172.16.21.7:46122 db: iws Command: Query Time: 652 State: updating Info: update busi_reconciliationgbgsinfo_inputdetails set bgs_id = 1622 , date = '2018-06-24 00:00:00' , awbnumber = '006-85516771' , incidental = 15.00 , entry_exit = 23.00 , warehousing_fee = 0.00 , loading_unloading = 0.00 , other = 0.00 , total = 38.00 , state = 20 , comparison_resultsid = 30 , confirmation_method = '人工' , confirmationid = 'root' , confirmationtime = '2018-07-16 16:25:17' , confirmation_note = '.' , createtime = '2018-06-24 20:00:07' , createrid = '9862ebdbaf3249a88bcaa8f01bde0471' where id = 4510
通過(guò)上面兩個(gè)的輸出結(jié)果，我們明白了，是441803線(xiàn)程鎖住了表，造成線(xiàn)程441805的等待。
我們看到發(fā)生等待的線(xiàn)程441805對(duì)應(yīng)的sql語(yǔ)句是：update busi_reconciliationgbgs ... where id = 4510，但是鎖表的線(xiàn)程441803對(duì)應(yīng)的sql語(yǔ)句竟然是Null。這就更讓人迷惑了。
于是我默默的翻開(kāi)了ysql官方文檔，原來(lái)里面已經(jīng)對(duì)這個(gè)null專(zhuān)門(mén)做了說(shuō)明。
官方文檔說(shuō)，要想找到這個(gè)null值對(duì)應(yīng)的阻塞語(yǔ)句，可以通過(guò)下面幾個(gè)步驟尋找：
a)、根據(jù)鎖表的processlist id 441803，運(yùn)用如下sql，找到null對(duì)應(yīng)的sql語(yǔ)句，如下：
SELECT SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID in (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803)
b)、如果上面找到的sql語(yǔ)句，你還是不能分析出為什么他們會(huì)鎖表，一直拿著鎖不釋放，那么你可以查看 performance_schema.events_statements_history表里面最近執(zhí)行過(guò)的10條sql（假設(shè)上面查到的thread_id=28）:
SELECT EVENT_ID,CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID in (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803) order by event_id
其他：
上面查詢(xún)鎖的sql可以只關(guān)注已下幾個(gè)列，如下：
SELECT wait_started, wait_age, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query, blocking_lock_mode, sql_kill_blocking_query FROM sys.innodb_lock_waits
~~~~~~~~~~~~分割線(xiàn)~~~~~~~~~~~~~~~~~
最近我用python 2.6寫(xiě)了個(gè)自動(dòng)殺鎖的腳本，只要發(fā)現(xiàn)sys.innodb_lock_waits表里面有鎖表的內(nèi)容，就殺死相應(yīng)的sql線(xiàn)程，并輸出殺死sql的內(nèi)容到當(dāng)前目錄下：
#!/usr/bin/env python
#-*-coding:utf8-*-
#下載rpm包安裝，下載地址：https://dev.mysql.com/downloads/connector/python/,注意mysql-connector-python版本需要是1.1.17的，2.x的版本運(yùn)行會(huì)有問(wèn)題
from __future__ import print_function
import mysql.connector as mdb
import os
#全局變量
username = 'root'
password = ''
hostname = 'localhost'
database = 'sys'
#配置信息
config = {
'user': username,
'password': password,
'host': hostname,
'database': database
}
#定義函數(shù),查看鎖表的行數(shù)
def Get_sys_lock():
show_locked_num = "select count(*) from sys.innodb_lock_waits"
cursor.execute(show_locked_num)
for i in cursor:
locked_sql_num = i[0]
return locked_sql_num
#定義函數(shù)，如果有鎖表，就重定向到locked_sql.txt文件里面
def show_locked_sql():
count = 0
count1 = 0
#如果日志文件存在就刪除
if os.path.isfile('locked_sql.txt'):
os.remove('locked_sql.txt')
if os.path.isfile('null_sql.txt'):
os.remove('null_sql.txt')
if os.path.isfile('last_10_null_sql.txt'):
os.remove('last_10_null_sql.txt')
#引用函數(shù)
locked_sql_num = Get_sys_lock()
print("鎖表的行數(shù)是：{0}".format(locked_sql_num))
if locked_sql_num > 0: #如果有鎖表
show_locked_sql = " SELECT \
wait_started, \
wait_age, \
waiting_pid, \
waiting_query, \
blocking_trx_id, \
blocking_pid, \
blocking_query, \
blocking_lock_mode, \
sql_kill_blocking_query \
FROM \
sys.innodb_lock_waits \
"
cursor.execute(show_locked_sql)
for i in cursor:
wait_started = i[0]
wait_age = i[1]
waiting_pid = i[2]
waiting_query = i[3]
blocking_trx_id = i[4]
blocking_pid = i[5]
blocking_query = i[6]
blocking_lock_mode = i[7]
sql_kill_blocking_query = i[8]
if not str(blocking_query).strip(): #如果blocking_query字符串為Null
#import pdb;pdb.set_trace()
show_null_sql = "SELECT SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID in (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=%s)" % blocking_pid
conn = mdb.connect(**config)
cursor1 = conn.cursor()
cursor1.execute(show_null_sql)
#print(cursor1.fetchall())
for j in cursor1:
SQL_TEXT = j[0]
print(SQL_TEXT)
cursor1.close
try:
count1 += 1
f = open('null_sql.txt','a') #a表示追加
f.write (
'##########' + 'The ' + str(count1) + ' rows ' + 'Blocking null query對(duì)應(yīng)的具體sql為##########\n' +
'blocking_pid: ' + str(blocking_pid) + '\n'
'sql_text: ' + str(SQL_TEXT) + '\n\n'
)
except OSError as reason:
print('出錯(cuò)了:' + str(reason))
finally:
f.close
#再查看null對(duì)應(yīng)的最后10條sql
show_last_10_null_sql = "SELECT EVENT_ID,CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID in (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=%s) order by event_id" % blocking_pid
cursor2 = conn.cursor()
cursor2.execute(show_last_10_null_sql)
cursor2.close
#print(cursor1.fetchall())
count2 = 0
for j in cursor2:
EVENT_ID = j[0]
CURRENT_SCHEMA = j[1]
SQL_TEXT = j[2]
try:
count2 += 1
f = open('last_10_null_sql.txt','a') #a表示追加
f.write (
'##########' + 'The ' + str(count2) + ' rows ' + 'laster blocking null query對(duì)應(yīng)的具體sql為##########\n' +
'blocking_pid: ' + str(blocking_pid) + '\n'
'EVENT_ID: ' + str(EVENT_ID) + '\n'
'CURRENT_SCHEMA: ' + str(CURRENT_SCHEMA) + '\n'
'SQL_TEXT: ' + str(SQL_TEXT) + '\n\n'
)
except OSError as reason:
print('出錯(cuò)了:' + str(reason))
finally:
f.close
#把鎖表的情況重定向到一個(gè)locked_sql.txt文件里面
try:
count += 1
f = open('locked_sql.txt','a') #a表示追加
f.write('##########' + 'The ' + str(count) + ' rows' + '###########\n')
f.write (
'wait_started: ' + str(wait_started) + '\n' +
'wait_age: ' + str(wait_age) + '\n' +
'waiting_pid: ' + str(waiting_pid ) + '\n' +
'waiting_query: ' + str(waiting_query) + '\n' +
'blocking_trx_id: ' + str(blocking_trx_id) + '\n' +
'blocking_pid: ' + str(blocking_pid) + '\n' +
'blocking_query: ' + str(blocking_query) + '\n' +
'blocking_lock_mode: ' + str(blocking_lock_mode) + '\n' +
'sql_kill_blocking_query: ' + str(sql_kill_blocking_query) + '\n\n'
)
'''
f.write (
'##########' + 'Blocking null query對(duì)應(yīng)的具體sql為##########\n' +
'blocking_pid:' + str(blocking_pid) +
'sql_text:' + str(SQL_TEXT)
)
'''
except OSError as reason:
print('出錯(cuò)了:' + str(reason))
finally:
f.close
#定義函數(shù)，列出當(dāng)前所有執(zhí)行的sql線(xiàn)程
def show_processlist():
count = 0
#如果日志文件存在就刪除
if os.path.isfile('show_processlist.txt'):
os.remove('show_processlist.txt')
#引用函數(shù)
locked_sql_num = Get_sys_lock()
#print("鎖表的行數(shù)是：{0}".format(locked_sql_num))
if locked_sql_num > 0: #如果有鎖表
show_processlist = "select \
id, \
user, \
host, \
db, \
time, \
state, \
info \
from information_schema.`PROCESSLIST` order by time desc \
"
cursor.execute(show_processlist)
for i in cursor:
id = i[0]
user = i[1]
host = i[2]
db = i[3]
time = i[4]
state = i[5]
info = i[6]
#把鎖表的情況重定向到一個(gè)show_processlist.txt文件里面
try:
count += 1
f = open('show_processlist.txt','a') #a表示追加
f.write('##########' + 'The ' + str(count) + ' rows' + '###########\n')
f.write (
'id: ' + str(id) + '\n' +
'user: ' + str(user) + '\n' +
'host: ' + str(host) + '\n' +
'db: ' + str(db) + '\n' +
'time: ' + str(time) + '\n' +
'state: ' + str(state) + '\n' +
'info: ' + str(info) + '\n\n'
)
except OSError as reason:
print('出錯(cuò)了:' + str(reason))
finally:
f.close
#定義函數(shù)，如果有鎖表，就殺死
def kill_locked_sql():
#引用函數(shù)
locked_sql_num = Get_sys_lock()
#print("鎖表的行數(shù)是：{0}".format(locked_sql_num))
if locked_sql_num > 0: #如果有鎖表
execute_locked_sql = " SELECT \
sql_kill_blocking_query \
FROM \
sys.innodb_lock_waits \
"
cursor.execute(execute_locked_sql)
for i in cursor:
sql_kill_blocking_query = i[0]
conn = mdb.connect(**config)
cursor1 = conn.cursor()
try:
cursor1.execute(sql_kill_blocking_query)
except:
print('出錯(cuò)了')
cursor1.close
#主程序
conn = mdb.connect(**config)
cursor = conn.cursor()
show_locked_sql()
show_processlist()
kill_locked_sql()
cursor.close
conn.close感謝各位的閱讀！關(guān)于“如何查看MySQL鎖等待的原因”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
本文標(biāo)題：如何查看MySQL鎖等待的原因
URL網(wǎng)址：http://www.dlmjj.cn/article/iejiop.html
