mysql - jpaRepository 出现死锁

Question

我们正在使用 JpaRepository 连接 mysql。我们的存储库如下所示:

public interface IRawEntityRepository extends JpaRepository<RawEntity, String> {

@Transactional
@Modifying
@Query("delete from RawEntity u where u.x < :x and u.y = :y")
void deleteXBeforeAndY(@Param("created")Date created, @Param("namespace")String namespace);

List<RawEntity> findByXAndY(boolean X, String Y);

Page<RawEntity> findByX(String X, Pageable pageable);

@Query("select max (u.x) from RawEntity u where u.y = :y")
Date findMaxXByY(@Param("y")String Y);

}

代码工作正常，但是我们在 mysql 中遇到死锁(同时使用大量并行删除、保存和选择):

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1066)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)

在 mysql 中我们看到:

LATEST DETECTED DEADLOCK
------------------------
2013-11-04 17:04:57 133b66000
*** (1) TRANSACTION:
TRANSACTION 1147504, ACTIVE 34 sec fetching rows
mysql tables in use 10, locked 10
LOCK WAIT 8 lock struct(s), heap size 1248, 16 row lock(s), undo log entries 14
MySQL thread id 240835, OS thread handle 0x134a82000, query id 13546692 localhost 127.0.0.1 salespredictsql updating
delete from rawentities where created<'2013-11-04 17:04:23.37' and namespace='namespace'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
**RECORD LOCKS space id 15471 page no 4 n bits 168 index `PRIMARY` of table `spschema`.`rawentities` /* Partition `p3` */ trx id 1147504 lock_mode X locks rec but not gap waiting**
Record lock, heap no 15 PHYSICAL RECORD: n_fields 10; compact format; info bits 32
 0: len 5; hex 3130303034; asc 10004;;
 1: len 4; hex caa3555b; asc   U[;;
 2: len 6; hex 000000118271; asc      q;;
 3: len 7; hex 2a000003a91d69; asc *     i;;
 4: len 5; hex 9902c22000; asc      ;;
 5: len 30; hex 616c6b646a66616c6b646a6620616c6b646a66206c616b73646a66616c6b; asc alkdjfalkdjf alkdjf laksdjfalk; (total 96 bytes);
 6: len 1; hex 81; asc  ;;
 7: SQL NULL;
 8: len 9; hex 6e616d657370616365; asc namespace;;
 9: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1147505, ACTIVE 32 sec starting index read
mysql tables in use 1, locked 1
2479 lock struct(s), heap size 244152, 10029 row lock(s), undo log entries 9999
MySQL thread id 240836, OS thread handle 0x133b66000, query id 13837637 localhost 127.0.0.1 salespredictsql updating
/* delete com.salespredict.entities.master.RawEntity */ delete from rawentities where entityId='100004' and partitionId=1252218203
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15471 page no 4 n bits 168 index `PRIMARY` of table `spschema`.`rawentities` /* Partition `p3` */ trx id 1147505 lock_mode X locks rec but not gap
Record lock, heap no 15 PHYSICAL RECORD: n_fields 10; compact format; info bits 32
 0: len 5; hex 3130303034; asc 10004;;
 1: len 4; hex caa3555b; asc   U[;;
 2: len 6; hex 000000118271; asc      q;;
 3: len 7; hex 2a000003a91d69; asc *     i;;
 4: len 5; hex 9902c22000; asc      ;;
 5: len 30; hex 616c6b646a66616c6b646a6620616c6b646a66206c616b73646a66616c6b; asc alkdjfalkdjf alkdjf laksdjfalk; (total 96 bytes);
 6: len 1; hex 81; asc  ;;
 7: SQL NULL;
 8: len 9; hex 6e616d657370616365; asc namespace;;
 9: len 4; hex 80000003; asc     ;;

我们该如何预防呢？我们尝试使用隔离和/或@Lock，但没有成功。谢谢，亚尼夫

Answer 1

为了防止死锁，您可以在表名称后面放置 WITH (NOLOCK) 关键字。例如:RawEntity u WITH (NOLOCK)。当你实现这个时，可能会没问题