服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - mysql kill process解决死锁问题

mysql kill process解决死锁问题

2022-08-24 18:20xiaolyuh123 Mysql

这篇文章主要介绍了使用mysql kill process解决死锁问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

mysql kill process解决死锁

1、查看进程列表， 找到ID

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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里面执行.

?
1
2
3
4
5
6
7
8
9
10
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的内容像这个样子

?
1
2
3
4
5
kill   66402982 ;
kill   66402983 ;
kill   66402986 ;
kill   66402991 ;
.....

好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.

?
1
mysql > source   kill_thread_id . sql

当然了, 也可以一行搞定

?
1
2
3
4
5
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 transaction

org.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

异常复现

分析日志发现，出现的几次都是在并发时发生，并且商户都有上级代理，本地并发测试，成功复现

原因分析

数据库相关锁的概念在此不赘述，具体请自行度娘

数据库运行命令：

?
1
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，从而解决死锁问题

?
1
2
3
4
@Transactional(isolation = Isolation.READ_COMMITTED)
public int updateOrder(BigDecimal je, String status, String orderNo, String zfsj) {
    // xxxxx相关业务
}

2、可以在死锁业务外面添加循环，捕获死锁异常，发生死锁异常时循环执行，正常执行时结束循环

3、添加全局异常捕获器，在捕获器中获取本次请求的方法类型(GET/POST…)，方法路径，方法参数，contentType等等，重新请求一次

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@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\"}";
    }
}

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://xiaolyuh.blog.csdn.net/article/details/62044794

  • mysql
  • 死锁
  • kill
  • process

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 10048 　 1175 　 2003 　 1366 　 28000 　 1577 　 1135 　 1786 　 1093 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图