关于批处理和索引之间的趣事

最近项目上出现了很奇怪的一个问题，通过excel模板上传数据时，导入经常卡死在最后保存数据的时候，过了会儿显示保存失败。通过日志里面可以发现报的异常如下，很明显是锁表了。

 1 java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
 2     at com.mysql.jdbc.StatementImpl.handleExceptionForBatch(StatementImpl.java:1448)
 3     at com.mysql.jdbc.PreparedStatement.executePreparedBatchAsMultiStatement(PreparedStatement.java:1585)
 4     at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1463)
 5     at com.epoint.dao.util.PreStatementBatch.executeFinal(PreStatementBatch.java:177)
 6     at com.epoint.datacenter.controller.resource.datadomain.zjgResourceImportHandler.saveSheet(zjgResourceImportHandler.java:558)
 7     at com.epoint.datacenter.controller.resource.datadomain.zjgResourceImportHandler$3.saveRow(zjgResourceImportHandler.java:108)
 8     at com.epoint.basic.faces.dataimport.excel.g.run(mo:74)
 9     at java.lang.Thread.run(Thread.java:745)
10 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
11     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
12     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
13     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
14     at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
15     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
16     at com.mysql.jdbc.Util.getInstance(Util.java:386)
17     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1066)
18     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
19     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
20     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
21     at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2399)
22     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2789)
23     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
24     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
25     at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
26     at com.mysql.jdbc.PreparedStatement.executePreparedBatchAsMultiStatement(PreparedStatement.java:1583)
27     ... 6 more

然后尝试对不通的库进行复现，发现有的导入能成功，有的导入就报死锁，这就奇怪了，数据库都是mysql，采用的是集群部署，都在同一个服务器上，按理要有问题应该都有问题才对。然后看了下sql的执行计划，发现死锁报错出现在delete语句上。于是看了下源码，框架里面导入是采用了批处理，测试了下，批处理报错事务并没有回滚。本以为把最后要执行的delete语句注释了，能成功解决问题，但是事实证明，并没有解决问题。这次再看SQL执行计划，发现死锁出现在update语句上，这次我便确认并不是删除语句的问题。

排查了一遍excel中数据问题，发现导入的数据也没有对同一条数据进行更新和删除操作的，更加不会锁表了。为了解决问题，无奈把线程数从5000提升至10000，这时候再次导入的确是成功了，但是这太消耗服务器性能了，一共才不到10000的数据量，竟然开了一万个线程去执行，太低效了。最后去咨询了下DBA，通过监控数据库操作发现，居然是删改操作没有走索引导致的。这里又学到了，没索引会从第一行开始扫描直到找到数据位置，扫描过程会加锁，发现数据不是目标会再释放锁，delete和update是必须走索引的。

这边由于历史遗留问题，jar包中的sql语句写死了主键为RowGuid，而我在实际操作过程中已经将数据库的主键进行了变更，所以上传按原sql去执行的时候发现RowGuid不是主键了而进行了锁表扫描。之前导入能成功是因为只执行了insert操作，所以没有涉及到锁表问题。这次问题成功解决也多亏了咨询了DBA，还是对数据库不熟悉啊。

转载于:https://www.cnblogs.com/timePasser-leoli/p/10937511.html