mysql kill process解决死锁
1、查看进程列表， 找到id
show processlist; +--------+--------+--------------------+----------+---------+------+----------+------------------+ | id | user | host | db | command | time | state | info | +--------+--------+--------------------+----------+---------+------+----------+------------------+ | 8 | remote | 10.16.30.96:36592 | activity | sleep | 239 | | null | | 396749 | remote | 172.17.1.122:53028 | activity | sleep | 1966 | | null | | 397104 | remote | 172.17.3.211:52666 | activity | sleep | 7 | | null | | 397177 | remote | 172.17.3.211:52819 | activity | sleep | 7366 | | null | | 397267 | remote | 172.17.1.122:55728 | activity | sleep | 5741 | | null | | 397268 | remote | 172.17.1.122:55730 | activity | sleep | 7849 | | null | | 397287 | remote | 172.17.3.150:57151 | activity | sleep | 7128 | | null | | 397294 | remote | 172.17.3.68:53158 | activity | sleep | 2882 | | null | | 397296 | remote | 172.17.3.68:53160 | null | sleep | 6492 | | null |
2、 查询语句把表锁住了, 赶紧找出第一个locked的thread_id, 在mysql的shell里面执行.
kill id #!/bin/bash mysql - u root - e " show processlist " | grep - i " locked " >> locked_log . txt for line in ` cat locked_log.txt | awk '{print $1 }' ` do echo " kill $line ; " >> kill_thread_id . sql done
现在kill_thread_id.sql的内容像这个样子
kill 66402982 ; kill 66402983 ; kill 66402986 ; kill 66402991 ; .....
好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.
mysql > source kill_thread_id . sql
当然了, 也可以一行搞定
for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'` do mysqladmin kill ${id} done ;
mysql死锁异常分析及解决
其实在这次之前也发生过死锁异常，但当时并没当回事，也没分析数据，简单的认为是偶发现象，并且是修改同一用户产生，于是就在最初添加了select for update，直到这两天发生了好几笔，分析数据发现并不是之前猜测的那样
问题产生
这两天老接到运维通知，说某客户又发生了卡单现象，让我分析下问题，然后找来日志，异常信息如下：
### error updating database. cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
### the error may involve io.renren.modules.others.dao.playerdao.savezhsz-inline
### the error occurred while setting parameters
### sql: insert into shpt_zhgl_zhsz(rq, jysr, ztzhye, zhye, shid) select ?, ?, zhye, zhye + ?, user_id from sys_user where user_id = ? on duplicate key update jysr = jysr + values(jysr), zhye = values(zhye)
### cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
; deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transactionorg.springframework.dao.deadlockloserdataaccessexception:
### error updating database. cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
### the error may involve io.renren.modules.others.dao.playerdao.savezhsz-inline
### the error occurred while setting parameters
### sql: insert into shpt_zhgl_zhsz(rq, jysr, ztzhye, zhye, shid) select ?, ?, zhye, zhye + ?, user_id from sys_user where user_id = ? on duplicate key update jysr = jysr + values(jysr), zhye = values(zhye)
### cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
; deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.sqlerrorcodesqlexceptiontranslator.dotranslate(sqlerrorcodesqlexceptiontranslator.java:267)
at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:72)
at org.mybatis.spring.mybatisexceptiontranslator.translateexceptionifpossible(mybatisexceptiontranslator.java:73)
at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:446)
at com.sun.proxy.$proxy84.update(unknown source)
at org.mybatis.spring.sqlsessiontemplate.update(sqlsessiontemplate.java:294)
at org.apache.ibatis.binding.mappermethod.execute(mappermethod.java:62)
at org.apache.ibatis.binding.mapperproxy.invoke(mapperproxy.java:59)
at com.sun.proxy.$proxy115.savezhsz(unknown source)
at io.renren.modules.others.service.playerservice.updateorder(playerservice.java:195)
at io.renren.modules.others.service.playerservice$$fastclassbyspringcglib$$cd68b7fd.invoke(<generated>)
at org.springframework.cglib.proxy.methodproxy.invoke(methodproxy.java:218)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.invokejoinpoint(cglibaopproxy.java:769)
at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:163)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.proceed(cglibaopproxy.java:747)
at org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:366)
at org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:99)
at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:186)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.proceed(cglibaopproxy.java:747)
at org.springframework.aop.framework.cglibaopproxy$dynamicadvisedinterceptor.intercept(cglibaopproxy.java:689)
at io.renren.modules.others.service.playerservice$$enhancerbyspringcglib$$5688f237.updateorder(<generated>)
at io.renren.modules.others.service.playerservice$$fastclassbyspringcglib$$cd68b7fd.invoke(<generated>)
at org.springframework.cglib.proxy.methodproxy.invoke(methodproxy.java:218)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.invokejoinpoint(cglibaopproxy.java:769)
at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:163)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.proceed(cglibaopproxy.java:747)
at org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:366)
at org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:99)
at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:186)
at org.springframework.aop.framework.cglibaopproxy$cglibmethodinvocation.proceed(cglibaopproxy.java:747)
at org.springframework.aop.framework.cglibaopproxy$dynamicadvisedinterceptor.intercept(cglibaopproxy.java:689)
at io.renren.modules.others.service.playerservice$$enhancerbyspringcglib$$2c59a973.updateorder(<generated>)
at io.renren.modules.others.controller.playercontroller.a(playercontroller.java:967)
at sun.reflect.nativemethodaccessorimpl.invoke0(native method)
at sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)
at sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)
at java.lang.reflect.method.invoke(method.java:498)
at org.springframework.web.method.support.invocablehandlermethod.doinvoke(invocablehandlermethod.java:190)
at org.springframework.web.method.support.invocablehandlermethod.invokeforrequest(invocablehandlermethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.servletinvocablehandlermethod.invokeandhandle(servletinvocablehandlermethod.java:106)
at org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.invokehandlermethod(requestmappinghandleradapter.java:888)
at org.springframework.web.servlet.mvc.method.annotation.requestmappinghandleradapter.handleinternal(requestmappinghandleradapter.java:793)
at org.springframework.web.servlet.mvc.method.abstracthandlermethodadapter.handle(abstracthandlermethodadapter.java:87)
at org.springframework.web.servlet.dispatcherservlet.dodispatch(dispatcherservlet.java:1040)
at org.springframework.web.servlet.dispatcherservlet.doservice(dispatcherservlet.java:943)
at org.springframework.web.servlet.frameworkservlet.processrequest(frameworkservlet.java:1006)
at org.springframework.web.servlet.frameworkservlet.doget(frameworkservlet.java:898)
at javax.servlet.http.httpservlet.service(httpservlet.java:634)
at org.springframework.web.servlet.frameworkservlet.service(frameworkservlet.java:883)
at javax.servlet.http.httpservlet.service(httpservlet.java:741)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:231)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:53)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.apache.shiro.web.servlet.onceperrequestfilter.dofilter(onceperrequestfilter.java:112)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at io.renren.common.xss.xssfilter.dofilter(xssfilter.java:23)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.apache.shiro.web.servlet.proxiedfilterchain.dofilter(proxiedfilterchain.java:61)
at org.apache.shiro.web.servlet.advicefilter.executechain(advicefilter.java:108)
at org.apache.shiro.web.servlet.advicefilter.dofilterinternal(advicefilter.java:137)
at org.apache.shiro.web.servlet.onceperrequestfilter.dofilter(onceperrequestfilter.java:125)
at org.apache.shiro.web.servlet.proxiedfilterchain.dofilter(proxiedfilterchain.java:66)
at org.apache.shiro.web.servlet.abstractshirofilter.executechain(abstractshirofilter.java:449)
at org.apache.shiro.web.servlet.abstractshirofilter$1.call(abstractshirofilter.java:365)
at org.apache.shiro.subject.support.subjectcallable.docall(subjectcallable.java:90)
at org.apache.shiro.subject.support.subjectcallable.call(subjectcallable.java:83)
at org.apache.shiro.subject.support.delegatingsubject.execute(delegatingsubject.java:387)
at org.apache.shiro.web.servlet.abstractshirofilter.dofilterinternal(abstractshirofilter.java:362)
at org.apache.shiro.web.servlet.onceperrequestfilter.dofilter(onceperrequestfilter.java:125)
at org.springframework.web.filter.delegatingfilterproxy.invokedelegate(delegatingfilterproxy.java:358)
at org.springframework.web.filter.delegatingfilterproxy.dofilter(delegatingfilterproxy.java:271)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at io.renren.config.licensefilter.dofilter(licensefilter.java:136)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at io.renren.config.filterconfig$1.dofilterinternal(filterconfig.java:82)
at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:119)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.springframework.web.filter.requestcontextfilter.dofilterinternal(requestcontextfilter.java:100)
at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:119)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.springframework.web.filter.formcontentfilter.dofilterinternal(formcontentfilter.java:93)
at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:119)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.springframework.web.filter.characterencodingfilter.dofilterinternal(characterencodingfilter.java:201)
at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:119)
at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:193)
at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:166)
at org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:202)
at org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:96)
at org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:541)
at org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:139)
at org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:92)
at org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:74)
at org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:343)
at org.apache.coyote.http11.http11processor.service(http11processor.java:367)
at org.apache.coyote.abstractprocessorlight.process(abstractprocessorlight.java:65)
at org.apache.coyote.abstractprotocol$connectionhandler.process(abstractprotocol.java:860)
at org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun(nioendpoint.java:1598)
at org.apache.tomcat.util.net.socketprocessorbase.run(socketprocessorbase.java:49)
at java.util.concurrent.threadpoolexecutor.runworker(threadpoolexecutor.java:1142)
at java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:617)
at org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61)
at java.lang.thread.run(thread.java:748)
caused by: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
at com.mysql.cj.jdbc.exceptions.sqlerror.createsqlexception(sqlerror.java:123)
at com.mysql.cj.jdbc.exceptions.sqlerror.createsqlexception(sqlerror.java:97)
at com.mysql.cj.jdbc.exceptions.sqlexceptionsmapping.translateexception(sqlexceptionsmapping.java:122)
at com.mysql.cj.jdbc.clientpreparedstatement.executeinternal(clientpreparedstatement.java:955)
at com.mysql.cj.jdbc.clientpreparedstatement.execute(clientpreparedstatement.java:372)
at com.alibaba.druid.pool.druidpooledpreparedstatement.execute(druidpooledpreparedstatement.java:497)
at sun.reflect.generatedmethodaccessor53.invoke(unknown source)
at sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)
at java.lang.reflect.method.invoke(method.java:498)
at org.apache.ibatis.logging.jdbc.preparedstatementlogger.invoke(preparedstatementlogger.java:59)
at com.sun.proxy.$proxy119.execute(unknown source)
at org.apache.ibatis.executor.statement.preparedstatementhandler.update(preparedstatementhandler.java:46)
at org.apache.ibatis.executor.statement.routingstatementhandler.update(routingstatementhandler.java:74)
at org.apache.ibatis.executor.simpleexecutor.doupdate(simpleexecutor.java:50)
at org.apache.ibatis.executor.baseexecutor.update(baseexecutor.java:117)
at org.apache.ibatis.executor.cachingexecutor.update(cachingexecutor.java:76)
at org.apache.ibatis.session.defaults.defaultsqlsession.update(defaultsqlsession.java:198)
at sun.reflect.nativemethodaccessorimpl.invoke0(native method)
at sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)
at sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)
at java.lang.reflect.method.invoke(method.java:498)
at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:433)
… 108 common frames omitted
异常复现
分析日志发现，出现的几次都是在并发时发生，并且商户都有上级代理，本地并发测试，成功复现
原因分析
数据库相关锁的概念在此不赘述，具体请自行度娘
数据库运行命令：
show engine innodb status
得到日志，主要分析如下片段，是最后一次死锁信息:
————————
latest detected deadlock
————————
2021-03-16 09:56:00 0x3444
*** (1) transaction:
transaction 5245990, active 0 sec inserting
mysql tables in use 2, locked 2
lock wait 5 lock struct(s), heap size 1136, 2 row lock(s)
mysql thread id 4949, os thread handle 14000, query id 980983 180.126.46.39 root sending data
insert into shpt_zhgl_zhsz(rq, jysr, ztzhye, zhye, shid) select '2021-03-13', 8.03, zhye, zhye + 8.03, user_id from sys_user where user_id = 32 on duplicate key update jysr = jysr + values(jysr), zhye = values(zhye)
*** (1) waiting for this lock to be granted:
record locks space id 233 page no 3 n bits 168 index primary of table `zfpt`.`shpt_zhgl_zhsz` trx id 5245990 lock_mode x insert intention waiting
record lock, heap no 1 physical record: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) transaction:
transaction 5245989, active 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
9 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
mysql thread id 4950, os thread handle 13380, query id 980987 180.126.46.39 root sending data
insert into shpt_zhgl_zhsz(rq, dlsr, ztzhye, zhye, shid) select '2021-03-13', 1.00, zhye, zhye + 1.00, user_id from sys_user where user_id = 1 on duplicate key update dlsr = dlsr + values(dlsr), zhye = values(zhye)
*** (2) holds the lock(s):
record locks space id 233 page no 3 n bits 168 index primary of table `zfpt`.`shpt_zhgl_zhsz` trx id 5245989 lock_mode x
record lock, heap no 1 physical record: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;*** (2) waiting for this lock to be granted:
table lock table `zfpt`.`shpt_zhgl_zhsz` trx id 5245989 lock mode auto-inc waiting
*** we roll back transaction (1)
原来是并发时在业务中insert into shpt_zhgl_zhsz on duplicate key update这个商户时，有其他商户也在运行insert into shpt_zhgl_zhsz on duplicate key update，而又由于商户拥有上级代理，也会nsert into shpt_zhgl_zhsz on duplicate key update上级代理的数据，从而产生了死锁
解决方案
1、mysql默认级别repeatable read导致的死锁问题，所以根据业务，决定修改隔离级别为 read committed，从而解决死锁问题
@transactional(isolation = isolation.read_committed) public int updateorder(bigdecimal je, string status, string orderno, string zfsj) { // xxxxx相关业务 }
2、可以在死锁业务外面添加循环，捕获死锁异常，发生死锁异常时循环执行，正常执行时结束循环
3、添加全局异常捕获器，在捕获器中获取本次请求的方法类型(get/post…)，方法路径，方法参数，contenttype等等，重新请求一次
@controlleradvice public class globalexceptionhandler { private logger logger = loggerfactory.getlogger(getclass()); // 这是异常锁表，lock wait timeout导致业务失败时的处理，也是比较常见的问题 @responsebody @exceptionhandler(cannotacquirelockexception.class) public string cannotacquirelockexception(cannotacquirelockexception e) { druiddatasource dds = springcontextutils.getbean("firstdatasource", druiddatasource.class); try (druidpooledconnection conn = dds.getconnection(); statement stmt = conn.createstatement(); resultset rs = stmt.executequery("select trx_mysql_thread_id from information_schema.innodb_trx" + " where trx_state = 'running' and trx_started < adddate(now(), interval -60 second)"); preparedstatement ps = conn.preparestatement("kill ?")) { while (rs.next()) { ps.setlong(1, rs.getlong(1)); ps.addbatch(); } ps.executebatch(); } catch (sqlexception ex) { logger.error(ex.getmessage(), ex); } logger.error(e.getmessage(), e); return "{"code": 500, "msg": "acquire lock failure, already kill the lock thread, try again"}"; } // 笔者没有实现这种方式，只是返回了对应异常信息 @responsebody @exceptionhandler(deadlockloserdataaccessexception.class) public string deadlockexception(deadlockloserdataaccessexception e) { logger.error(e.getmessage(), e); return "{"code": 500, "msg": "deadlock found when trying to get lock, try again"}"; } }
以上为个人经验，希望能给大家一个参考，也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:mysql kill process解决死锁问题，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/1209851.html