Hibernate: Deadlock found when trying to obtain lock

c3p0, hibernate, java, mysql

Solution

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`.

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

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

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.

This a log4j configuration to log the creation/deletion of entity managers and transactions 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?

Update queries are possible via native queries or 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

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.

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

Problem

I am using hibernate in my project and I am getting random Apparent Deadlocks for very simple database operations. 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. 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. My Hibernate configuration: ``` <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 public void setLastActivity() { em.insertNative("table") .values(...) .execute(); } ```

Original source

Related problems