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里面執(zhí)行.

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

現(xiàn)在kill_thread_id.sql的內(nèi)容像這個樣子

kill ? 66402982 ;
kill ? 66402983 ;
kill ? 66402986 ;
kill ? 66402991 ;
.....

好了, 我們在mysql的shell中執(zhí)行, 就可以把所有鎖表的進程殺死了.

mysql > source ? kill_thread_id . sql

當(dāng)然了, 也可以一行搞定

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
? ?mysqladmin kill ${id}
done
;

mysql死鎖異常分析及解決

其實在這次之前也發(fā)生過死鎖異常，但當(dāng)時并沒當(dāng)回事，也沒分析數(shù)據(jù)，簡單的認為是偶發(fā)現(xiàn)象，并且是修改同一用戶產(chǎn)生，于是就在最初添加了select for update，直到這兩天發(fā)生了好幾筆，分析數(shù)據(jù)發(fā)現(xiàn)并不是之前猜測的那樣

問題產(chǎn)生

這兩天老接到運維通知，說某客戶又發(fā)生了卡單現(xiàn)象，讓我分析下問題，然后找來日志，異常信息如下：

### 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

異常復(fù)現(xiàn)

分析日志發(fā)現(xiàn)，出現(xiàn)的幾次都是在并發(fā)時發(fā)生，并且商戶都有上級代理，本地并發(fā)測試，成功復(fù)現(xiàn)

原因分析

數(shù)據(jù)庫相關(guān)鎖的概念在此不贅述，具體請自行度娘

數(shù)據(jù)庫運行命令：

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)

原來是并發(fā)時在業(yè)務(wù)中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上級代理的數(shù)據(jù)，從而產(chǎn)生了死鎖

解決方案

1、mysql默認級別repeatable read導(dǎo)致的死鎖問題，所以根據(jù)業(yè)務(wù)，決定修改隔離級別為 read committed，從而解決死鎖問題

@Transactional(isolation = Isolation.READ_COMMITTED)
public int updateOrder(BigDecimal je, String status, String orderNo, String zfsj) {
?? ?// xxxxx相關(guān)業(yè)務(wù)
}

2、可以在死鎖業(yè)務(wù)外面添加循環(huán)，捕獲死鎖異常，發(fā)生死鎖異常時循環(huán)執(zhí)行，正常執(zhí)行時結(jié)束循環(huán)

3、添加全局異常捕獲器，在捕獲器中獲取本次請求的方法類型(GET/POST…)，方法路徑，方法參數(shù)，contentType等等，重新請求一次

@ControllerAdvice
public class GlobalExceptionHandler {
?? ?private Logger logger = LoggerFactory.getLogger(getClass());

?? ?// 這是異常鎖表，lock wait timeout導(dǎo)致業(yè)務(wù)失敗時的處理，也是比較常見的問題
?? ?@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\"}";
?? ?}

?? ?// 筆者沒有實現(xiàn)這種方式，只是返回了對應(yīng)異常信息
?? ?@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\"}";
?? ?}
}

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論