Hibernate: Deadlock found when trying to obtain lock

Question

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();
}

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

To solve this, try searching for usages of Isolation.REPEATABLE_READ and Isolation.SERIALIZABLEin 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 >

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

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