MySQL数据库下,在对同一个数据表进行大批量更新时遇到该异常:
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock;
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 17
通过错误可以看出是出现了死锁。在大批量对同一个表进行更新操作时,如果操作不当,很容易出现死锁问题。我们可以通过以下方式来解决该问题:
- 更新时where后面的字段加索引,这样MySQL会使用行级锁,减少操作冲突,如果不加会导致数据库使用表级锁。但是即使加了索引,在高并发情况下也会遇到死锁问题,所以最好是通过主键来定位数据。
- 改为批量提交,比如说每20条更新语句,算作一次提交。