您现在的位置是：网站首页> 编程资料编程资料
mysql kill process解决死锁问题_Mysql_
2023-05-26
346人已围观
简介 mysql kill process解决死锁问题_Mysql_
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()
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()
at io.renren.modules.others.service.PlayerService$$FastClassBySpringCGLIB$$cd68b7fd.invoke()
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()
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:7
