db mysql / mysql cluster 5.7.19 / deadlock

s

问题

2020-08-06  15:23:31.278 [MSC service thread 1-3] ERROR com.lindows.task.admin.job.SystemInfoReportJob-生成SystemInfo统计报告数据
org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Lock wait timeout exceeded; try restarting transaction
    at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:157) ~[quartz-2.2.1.jar:na]
    at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113) ~[quartz-2.2.1.jar:na]
    at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:238) ~[quartz-2.2.1.jar:na]
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.pauseTrigger(JobStoreSupport.java:2159) ~[quartz-2.2.1.jar:na]
    at org.quartz.core.QuartzScheduler.pauseTrigger(QuartzScheduler.java:1231) ~[quartz-2.2.1.jar:na]
    at org.quartz.impl.StdScheduler.pauseTrigger(StdScheduler.java:351) ~[quartz-2.2.1.jar:na]
    at com.iteye.lindows.task.admin.job.SystemInfoReportJob.runJob(SystemInfoReportJob.java:53) ~[classes:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_92-LINDOWSJVM]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_92-LINDOWSJVM]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_92-LINDOWSJVM]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_92-LINDOWSJVM]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1706) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1645) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:772) [spring-beans-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:839) [spring-context-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538) [spring-context-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:444) [spring-web-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:326) [spring-web-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107) [spring-web-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at io.undertow.servlet.core.ApplicationListeners.contextInitialized(ApplicationListeners.java:173) [undertow-servlet-1.0.15.Final.jar!/:1.0.15.Final]
    at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:190) [undertow-servlet-1.0.15.Final.jar!/:1.0.15.Final]
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:87) [wildfly-undertow-8.1.0.Final.jar!/:8.1.0.Final]
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.start(UndertowDeploymentService.java:72) [wildfly-undertow-8.1.0.Final.jar!/:8.1.0.Final]
    at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1948) [jboss-msc-1.2.2.Final.jar!/:1.2.2.Final]
    at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1881) [jboss-msc-1.2.2.Final.jar!/:1.2.2.Final]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_92-LINDOWSJVM]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_92-LINDOWSJVM]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_92-LINDOWSJVM]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_92-LINDOWSJVM]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_92-LINDOWSJVM]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_92-LINDOWSJVM]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_92-LINDOWSJVM]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) ~[na:na]
    at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96) ~[quartz-2.2.1.jar:na]
    ... 34 common frames omitted

解决方案如下：

Mysql 查看死锁，解除死锁方式

https://www.cnblogs.com/zery/p/11801168.html

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1.查看下在锁的事务 

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

例子：

查出死锁进程：SHOW PROCESSLIST
杀掉进程          KILL 420821;

其它关于查看死锁的命令：

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

项目中遇到的死锁问题： Lock wait timeout exceeded; try restarting transaction

https://www.cnblogs.com/anxbb/p/8671472.html

最近项目中频繁出现  Lock wait timeout exceeded; try restarting transaction这个错误，把我们弄得痛苦不堪啊，为了解决问题，上网上找好多资料，终于把问题复现了。具体操作步骤如下（我使用的mysql工具是 navicat）：

第一步：开启sql命令页面

第二步：输入start transaction;开启一个事务

第三步：输入update语句  UPDATE TABLENAME set time=null where id=29163;（切记不要 提交（commit；））

第四步：书写测试类

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
35
36
public class StTest implements Runnable{
    private  static ApplicationContext applicationContext=null;
    private static AtomicInteger a=new AtomicInteger(0);
    static {
         applicationContext=new ClassPathXmlApplicationContext("classpath:application.xml");
    }
    public static void main(String[] args) throws Exception {
        ExecutorService executorService = Executors.newFixedThreadPool(10);
        for (int i = 0; i <10 ; i++) {
            executorService.execute(new aaaa());
        }
        if(!executorService.isTerminated()){
            executorService.shutdown();
        }
    }
 
    @Override
    public void run() {
        XXXService bean = applicationContext.getBean(XXXService.class);
        XXXBeand xx= null;
        try {
            xx= bean.byId(254213);
        } catch (Exception e) {
            e.printStackTrace();
        }
        long aaa=System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            try {
                bean.update(xx);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        System.out.println(System.currentTimeMillis()-aaa);
    }
}

　　

运行程序并且运行下面3个sql查看结果

1
2
3
SELECT * FROM information_schema.INNODB_TRX;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_locks;

　　得到以下运行结果。我们发现有其他几个的 trx_state 状态是LOCK WAIT，得到我们要复现的结果

 

第五步：等待错误出现，在我们等待一段时间之后会发现程序报错

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
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
    at sun.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)
    at com.sun.proxy.$Proxy17.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
    at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
    at com.sun.proxy.$Proxy15.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
    at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
    ... 20 more

　　

从上面得到的结果，可以知道这个错误是在一个事务没有提交的时候，其他事务也操作相同对象导致的，那么找到了问题的原因，我们就可以针对这种情况进行修改了。

这是本次我遇到的错误，分享给大家一下，以避免程序中遇到这样的情况。

end

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lindows/p/13447038.html