Hibernate：尝试获取锁定时发现死锁

I am using hibernate in my project and I am getting random Apparent Deadlocks for very simple database operations.

我在我的项目中使用hibernate,我得到随机的表观死锁,用于非常简单的数据库操作。

There is one of the Stack Traces: https://gist.github.com/knyttl/8999006 – What confuses me, that the first Exception is RollbackException and then there are LockAquisition Exceptions.

有一个堆栈跟踪:https://gist.github.com/knyttl/8999006 - 让我感到困惑的是,第一个Exception是RollbackException,然后是LockAquisition Exceptions。

The problem happens often on similar clauses:

问题经常出现在类似的条款中:

@Transactional
public void setLastActivity() {
    User user = em.findById(...);
    user.setLastActivity(new Date());
    em.merge(user);
    em.flush();
}

I am quite stuck as I don't know whether it is problem of Hibernate, MySQL or C3P0.

我很困惑,因为我不知道它是Hibernate,MySQL还是C3P0的问题。

My Hibernate configuration:

我的Hibernate配置:

            <prop key="hibernate.dialect">${database.dialect}</prop>
            <prop key="hibernate.hbm2ddl.auto">${database.structure}</prop>
            <prop key="hibernate.connection.url">${database.connection}</prop>
            <prop key="hibernate.connection.username">${database.username}</prop>
            <prop key="hibernate.connection.password">${database.password}</prop>
            <prop key="hibernate.connection.driver_class">${database.driver}</prop>
            <prop key="hibernate.connection.shutdown">true</prop>
            <prop key="hibernate.connection.writedelay">0</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.show_sql">${database.show_sql}</prop>
            <prop key="hibernate.format_sql">false</prop>
            <prop key="hibernate.ejb.metamodel.generation">disabled</prop>
            <!-- Use the C3P0 connection pool provider -->
            <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
            <prop key="hibernate.c3p0.min_size">0</prop>
            <prop key="hibernate.c3p0.max_size">50</prop>
            <prop key="hibernate.c3p0.timeout">120</prop>
            <prop key="hibernate.c3p0.max_statements">0</prop>
            <prop key="hibernate.c3p0.max_statementsPerConnection">0</prop>
            <prop key="hibernate.c3p0.maxStatementsPerConnection">0</prop>
            <prop key="hibernate.c3p0.idle_test_period">120</prop>
            <prop key="hibernate.c3p0.acquire_increment">1</prop>
            <prop key="hibernate.c3p0.numHelperThreads">8</prop>

EDIT1:

I wrote above there were Apparent Deadlocks happening - that was wrong, only "Deadlock found when trying to obtain lock" happen.

我上面写的是发生了表观死锁 - 这是错误的,只有“试图获取锁定时发现死锁”发生。

EDIT2:

This happens also on these methods - those NEEDS to be annotated with @Transactional:

这也发生在这些方法上 - 那些需要用@Transactional注释的需求:

@Transactional
public void setLastActivity() {
    em.insertNative("table")
           .values(...)
           .execute();
}

2 个解决方案

#1

Because the deadlocks happen so frequently, it looks like some of the threads of the application are holding locks for an extended period of time.

因为死锁经常发生,所以看起来应用程序的某些线程长时间持有锁。

Each thread in the application will use it's own database connection/connections while accessing the database, so from the point of view of the database two threads are two distinct clients that compete for database locks.

应用程序中的每个线程在访问数据库时都将使用它自己的数据库连接/连接,因此从数据库的角度来看,两个线程是两个不同的客户端,它们争用数据库锁。

If a thread holds locks for an extended period of time and acquires them in a certain order, and a second thread comes along acquiring the same locks but on a different order, deadlock is bound to occur (see here for details on this frequent deadlock cause).

如果一个线程长时间持有锁并以某种顺序获取它们,并且第二个线程以不同的顺序获取相同的锁,则必然会发生死锁(有关此频繁死锁原因的详细信息,请参见此处) )。

Also deadlocks are occurring in read operations, which means that some threads are acquiring read locks as well. This happens if the threads are running transactions in REPEATABLE_READ isolation level or SERIALIZABLE.

在读取操作中也会发生死锁,这意味着某些线程也在获取读锁定。如果线程在REPEATABLE_READ隔离级别或SERIALIZABLE中运行事务,则会发生这种情况。

To solve this, try searching for usages of Isolation.REPEATABLE_READ and Isolation.SERIALIZABLEin the project, to see if this is being used.

要解决此问题,请尝试在项目中搜索Isolation.REPEATABLE_READ和Isolation.SERIALIZABLE的用法,以查看是否正在使用它。

As an alternative, use the default READ_COMMITTED isolation level and annotate the entities with @Version, to handle concurrency using optimistic locking instead.

作为替代方法,使用默认的READ_COMMITTED隔离级别并使用@Version注释实体,以使用乐观锁定来处理并发。

Also try to identify long running transactions, this happens sometimes when the @Transactional is placed at the wrong place and wraps for example the processing of a whole file in the example of a batch processing, instead of doing transactions line by line.

还尝试识别长时间运行的事务,有时当@Transactional被放置在错误的位置并且例如在批处理的示例中处理整个文件而不是逐行执行事务时会发生这种情况。

This a log4j configuration to log the creation/deletion of entity managers and transactions begin/commit/rollback:

这是一个log4j配置,用于记录实体管理器和事务begin / commit / rollback的创建/删除:

   <!-- spring entity manager and transactions -->
<logger name="org.springframework.orm.jpa" additivity ="false">
    <level value="debug" />
    <appender-ref ref="ConsoleAppender" />
</logger >
<logger name="org.springframework.transaction" additivity ="false">
    <level value="debug" />
    <appender-ref ref="ConsoleAppender" />
</logger >

Can I somehow execute update query (either JPA/Native) without having to lock the table via @Transactional?

我可以以某种方式执行更新查询(JPA / Native)而无需通过@Transactional锁定表吗?

Update queries are possible via native queries or JPQL.

可以通过本机查询或JPQL进行更新查询。

Can I somehow get into session without using @Transactional? For instance, scheduled thread tries to read Lazy field on Entity yields to LazyInitializationException - no session, if the method is not annotated with @Transactional

我可以不使用@Transactional进入会话吗?例如,如果该方法未使用@Transactional注释,则调度线程会尝试将实体的Lazy字段读取为LazyInitializationException - 无会话

In methods without @Transactional, queries will be executed in it's own entity manager and return only detached entities, as thee session is closed immediatelly after the query is run.

在没有@Transactional的方法中,查询将在它自己的实体管理器中执行,并且只返回分离的实体,因为在运行查询后会立即关闭会话。

so the lazy initialization exceptions in methods without @Transactional is normal. You can set them to @Transactional(readOnly=true) as well.

所以没有@Transactional的方法中的延迟初始化异常是正常的。您也可以将它们设置为@Transactional(readOnly = true)。

#2

This is the error with MySQL.

这是MySQL的错误。

The most easy way to resolve & avoid deadlocks is to reorder the DB operations happening in the application.

解决和避免死锁的最简单方法是重新排序应用程序中发生的数据库操作。

Deadlock mostly occurs when more than one resource/connection try to acquire more than one lock at opposite orders, as below:

当多个资源/连接尝试以相反的顺序获取多个锁时,主要发生死锁,如下所示:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);

In the scenario when both the connections execute at the same time, connection 1 will acquire lock on key(1), and connection 2 on key(2). After that both the connections will wait for other to release the lock on the key. This results in deadlock.

在两个连接同时执行的情况下,连接1将获得密钥(1)上的锁定,以及密钥(2)上的连接2。之后,两个连接都将等待其他连接释放锁上的锁。这导致死锁。

But, a little tweak in the order of the transactions, then deadlocks can be avoided.

但是,按交易顺序稍微调整一下,就可以避免死锁。

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);

Above re-order is deadlock proof.

以上重新订购是死锁证明。

Other ways to avoid deadlocks is to have a transaction management mechanism. Transaction management by Spring is almost plug-n-play. Moreover, you can have a deadlock retry policy in place. An interesting deadlock retry via Spring AOP can be found here. This way you just need to add the annotation to the method you want to retry in case of deadlock.

避免死锁的其他方法是使用事务管理机制。 Spring的事务管理几乎是即插即用的。此外,您可以实施死锁重试策略。通过Spring AOP进行有趣的死锁重试可以在这里找到。这样,您只需要将注释添加到要在死锁情况下重试的方法。

For more debug logs on deadlock to find out which statements are suspicious, try running the "show engine innodb status" diagnostics. Also, you can have a look at How to Cope with Deadlocks.

有关死锁的更多调试日志以找出可疑的语句,请尝试运行“show engine innodb status”诊断程序。此外,您还可以查看如何应对死锁。

UPDATE: A scenario for deadlocks in transactional DB operations.

更新:事务DB操作中死锁的方案。

In a transactional database, a deadlock happens when two processes each within its own transaction updates two rows of information but in the opposite order. For example, process A updates row 1 then row 2 in the exact time-frame process B updates row 2 then row 1. Process A can't finish updating row 2 until process B is finished, but it cannot finish updating row 1 until process A finishes. No matter how much time is allowed to pass, this situation will never resolve itself and because of this database management systems will typically kill the transaction of the process that has done the least amount of work.

在事务数据库中,当两个进程在其自己的事务中更新两行信息但顺序相反时,就会发生死锁。例如,进程A在确切的时间帧进程B中更新第1行然后第2行更新第2行然后第1行。进程A无法完成更新第2行直到进程B完成,但它无法完成更新第1行直到进程完成。无论允许多长时间通过,这种情况永远不会自行解决,因为这种数据库管理系统通常会终止进行最少量工作的进程的事务。

Shishir

#1