如何查看MySQL锁等待的原因
这篇文章给大家分享的是有关如何查看MySQL锁等待的原因的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
--sys库的介绍
mysql 5.6也可以有sys库(基于performance_schema的视图)。sys库是一个开源项目,在githup上早就有,是一个DBA的开源工具,后来mysql感觉好,就放在了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库了,不过mysql 5.6只有88张表,而mysql 5.7有101张,这是因为Mysql 5.7的performace_schema库里面又多了几张表。
sys库是performance_schema的视图。
--MySQL锁等待
当Mysql发生锁等待情况时,可以通过如下语句来在线查看:
mysql>select*fromsys.innodb_lock_waits\G;***************************1.row***************************wait_started:2018-07-1616:25:17//锁等待开始的时间,16:25开始等待wait_age:00:10:08//发现问题时已经等待了10分钟了wait_age_secs:608//608秒,也就是等10分钟了locked_table:`iws`.`busi_reconciliationgbgsinfo_inputdetails`//被锁住的表名locked_index:PRIMARY//被锁住的索引locked_type:RECORD//锁的类型为行锁waiting_trx_id:13666265//waitingtransactionid,正在等待事务的id号waiting_trx_started:2018-07-1616:24:54//这个事务是从16:24开始等待waiting_trx_age:00:10:31//等了10分钟了waiting_trx_rows_locked:1//正在等待的这个事务锁住了1行记录waiting_trx_rows_modified:0//正在等待的这个事务修改了0行记录waiting_pid:441805//这个等待事务的线程id是多少,通过showprocesslist命令可以查到它,结果看到是一个sleep的线程,没有执行具体sql语句,见下waiting_query:updatebusi_reconciliationgbgs...whereid=4510//等待锁释放的语句waiting_lock_id:13666265:2924:21:94//正在等待的锁idwaiting_lock_mode:X//等待锁的类型是排它锁blocking_trx_id:13666259//这个事务id阻塞了waitinglockblocking_pid:441803阻塞事务的pidblocking_query:NULL//阻塞事务的sql语句blocking_lock_id:13666259:2924:21:94blocking_lock_mode:Xblocking_trx_started:2018-07-1616:24:51blocking_trx_age:00:10:34blocking_trx_rows_locked:1blocking_trx_rows_modified:1sql_kill_blocking_query:KILLQUERY441803sql_kill_blocking_connection:KILL4418031rowinset(0.00sec)ERROR:Noqueryspecified
上面看到输出了很多的东西,看的我都蒙圈了。后来查看mysql官方文档,慢慢的才发现,其实只关注上面的waiting_pid、waiting_query和blocking_pid、blocking_query四个参数即可;其中waiting_pid和blocking_pid两个参数就是通过执行show processlist命令里面输出的线程id号,如下:
mysql>showfullprocesslist\G;***************************8.row***************************Id:441803User:iwsHost:172.16.21.7:46121db:iwsCommand:SleepTime:655State:Info:NULL***************************9.row***************************Id:441805User:iwsHost:172.16.21.7:46122db:iwsCommand:QueryTime:652State:updatingInfo:updatebusi_reconciliationgbgsinfo_inputdetailssetbgs_id=1622,date='2018-06-2400: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-1616:25:17',confirmation_note='.',createtime='2018-06-2420:00:07',createrid='9862ebdbaf3249a88bcaa8f01bde0471'whereid=4510
通过上面两个的输出结果,我们明白了,是441803线程锁住了表,造成线程441805的等待。
我们看到发生等待的线程441805对应的sql语句是:update busi_reconciliationgbgs ... where id = 4510,但是锁表的线程441803对应的sql语句竟然是Null。这就更让人迷惑了。
于是我默默的翻开了ysql官方文档,原来里面已经对这个null专门做了说明。
官方文档说,要想找到这个null值对应的阻塞语句,可以通过下面几个步骤寻找:
a)、根据锁表的processlist id 441803,运用如下sql,找到null对应的sql语句,如下:
SELECTSQL_TEXTFROMperformance_schema.events_statements_currentWHERETHREAD_IDin(SELECTTHREAD_IDFROMperformance_schema.threadsWHEREPROCESSLIST_ID=441803)
b)、如果上面找到的sql语句,你还是不能分析出为什么他们会锁表,一直拿着锁不释放,那么你可以查看 performance_schema.events_statements_history表里面最近执行过的10条sql(假设上面查到的thread_id=28):
SELECTEVENT_ID,CURRENT_SCHEMA,SQL_TEXTFROMperformance_schema.events_statements_historyWHERETHREAD_IDin(SELECTTHREAD_IDFROMperformance_schema.threadsWHEREPROCESSLIST_ID=441803)orderbyevent_id
其他:
上面查询锁的sql可以只关注已下几个列,如下:
SELECTwait_started,wait_age,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query,blocking_lock_mode,sql_kill_blocking_queryFROMsys.innodb_lock_waits
~~~~~~~~~~~~分割线~~~~~~~~~~~~~~~~~
最近我用python 2.6写了个自动杀锁的脚本,只要发现sys.innodb_lock_waits表里面有锁表的内容,就杀死相应的sql线程,并输出杀死sql的内容到当前目录下:
#!/usr/bin/envpython#-*-coding:utf8-*-#下载rpm包安装,下载地址:https://dev.mysql.com/downloads/connector/python/,注意mysql-connector-python版本需要是1.1.17的,2.x的版本运行会有问题from__future__importprint_functionimportmysql.connectorasmdbimportos#全局变量username='root'password=''hostname='localhost'database='sys'#配置信息config={'user':username,'password':password,'host':hostname,'database':database}#定义函数,查看锁表的行数defGet_sys_lock():show_locked_num="selectcount(*)fromsys.innodb_lock_waits"cursor.execute(show_locked_num)foriincursor:locked_sql_num=i[0]returnlocked_sql_num#定义函数,如果有锁表,就重定向到locked_sql.txt文件里面defshow_locked_sql():count=0count1=0#如果日志文件存在就删除ifos.path.isfile('locked_sql.txt'):os.remove('locked_sql.txt')ifos.path.isfile('null_sql.txt'):os.remove('null_sql.txt')ifos.path.isfile('last_10_null_sql.txt'):os.remove('last_10_null_sql.txt')#引用函数locked_sql_num=Get_sys_lock()print("锁表的行数是:{0}".format(locked_sql_num))iflocked_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)foriincursor: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]ifnotstr(blocking_query).strip():#如果blocking_query字符串为Null#importpdb;pdb.set_trace()show_null_sql="SELECTSQL_TEXTFROMperformance_schema.events_statements_currentWHERETHREAD_IDin(SELECTTHREAD_IDFROMperformance_schema.threadsWHEREPROCESSLIST_ID=%s)"%blocking_pidconn=mdb.connect(**config)cursor1=conn.cursor()cursor1.execute(show_null_sql)#print(cursor1.fetchall())forjincursor1:SQL_TEXT=j[0]print(SQL_TEXT)cursor1.closetry:count1+=1f=open('null_sql.txt','a')#a表示追加f.write('##########'+'The'+str(count1)+'rows'+'Blockingnullquery对应的具体sql为##########\n'+'blocking_pid:'+str(blocking_pid)+'\n''sql_text:'+str(SQL_TEXT)+'\n\n')exceptOSErrorasreason:print('出错了:'+str(reason))finally:f.close#再查看null对应的最后10条sqlshow_last_10_null_sql="SELECTEVENT_ID,CURRENT_SCHEMA,SQL_TEXTFROMperformance_schema.events_statements_historyWHERETHREAD_IDin(SELECTTHREAD_IDFROMperformance_schema.threadsWHEREPROCESSLIST_ID=%s)orderbyevent_id"%blocking_pidcursor2=conn.cursor()cursor2.execute(show_last_10_null_sql)cursor2.close#print(cursor1.fetchall())count2=0forjincursor2:EVENT_ID=j[0]CURRENT_SCHEMA=j[1]SQL_TEXT=j[2]try:count2+=1f=open('last_10_null_sql.txt','a')#a表示追加f.write('##########'+'The'+str(count2)+'rows'+'lasterblockingnullquery对应的具体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')exceptOSErrorasreason:print('出错了:'+str(reason))finally:f.close#把锁表的情况重定向到一个locked_sql.txt文件里面try:count+=1f=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('##########'+'Blockingnullquery对应的具体sql为##########\n'+'blocking_pid:'+str(blocking_pid)+'sql_text:'+str(SQL_TEXT))'''exceptOSErrorasreason:print('出错了:'+str(reason))finally:f.close#定义函数,列出当前所有执行的sql线程defshow_processlist():count=0#如果日志文件存在就删除ifos.path.isfile('show_processlist.txt'):os.remove('show_processlist.txt')#引用函数locked_sql_num=Get_sys_lock()#print("锁表的行数是:{0}".format(locked_sql_num))iflocked_sql_num>0:#如果有锁表show_processlist="select\id,\user,\host,\db,\time,\state,\info\frominformation_schema.`PROCESSLIST`orderbytimedesc\"cursor.execute(show_processlist)foriincursor:id=i[0]user=i[1]host=i[2]db=i[3]time=i[4]state=i[5]info=i[6]#把锁表的情况重定向到一个show_processlist.txt文件里面try:count+=1f=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')exceptOSErrorasreason:print('出错了:'+str(reason))finally:f.close#定义函数,如果有锁表,就杀死defkill_locked_sql():#引用函数locked_sql_num=Get_sys_lock()#print("锁表的行数是:{0}".format(locked_sql_num))iflocked_sql_num>0:#如果有锁表execute_locked_sql="SELECT\sql_kill_blocking_query\FROM\sys.innodb_lock_waits\"cursor.execute(execute_locked_sql)foriincursor:sql_kill_blocking_query=i[0]conn=mdb.connect(**config)cursor1=conn.cursor()try:cursor1.execute(sql_kill_blocking_query)except:print('出错了')cursor1.close#主程序conn=mdb.connect(**config)cursor=conn.cursor()show_locked_sql()show_processlist()kill_locked_sql()cursor.closeconn.close
感谢各位的阅读!关于“如何查看MySQL锁等待的原因”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。