Database deadlock example

    Some time ago I wrote about deadlock in Java language. However the problem of a deadlock can also appear in a database. The transactions are just the analogues of threads. Is a database able to recover from a deadlock? Let's check this out!

I have prepared a small piece of code:

@Path("/dbdeadlocktry")
@Stateless
public class DBDeadlockTry {
 private static final Logger log = Logger.getLogger(DBDeadlockTry.class);
 
 @PersistenceContext(unitName = "jboss-maven")
 private EntityManager entityManager;
 
 @EJB
 private DBDeadlockTry dbDeadlockTry;
 
 @GET
 @Path("acquireHumanAndAnimal")
 @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
 public void acquireHumanAndAnimal() throws InterruptedException {
  entityManager.find(Human.class, 1L, LockModeType.PESSIMISTIC_WRITE);
  log.info("Human locked in EJB " + this);

  try {
   dbDeadlockTry.acquireAnimalAndHuman();   
  } catch (Exception ex) {
   log.error(ex);
  }

  TimeUnit.SECONDS.sleep(1);
  
  entityManager.find(Animal.class, 2L, LockModeType.PESSIMISTIC_WRITE);
  log.info("Animal locked in EJB " + this);
 } 
 
 @Asynchronous
 @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
 public void acquireAnimalAndHuman() {
  entityManager.find(Animal.class, 2L, LockModeType.PESSIMISTIC_WRITE);
  log.info("Animal locked in EJB " + this);

  entityManager.find(Human.class, 1L, LockModeType.PESSIMISTIC_WRITE);
  log.info("Human locked in EJB " + this);
 } 
}

Hitting 'acquireHumanAndAnimal()' method gives the result:

(http--0.0.0.0-8080-1) Hibernate: 
(http--0.0.0.0-8080-1)     select
(http--0.0.0.0-8080-1)         human0_.human_id as human1_105_0_,
(http--0.0.0.0-8080-1)         human0_.human_name as human2_105_0_ 
(http--0.0.0.0-8080-1)     from
(http--0.0.0.0-8080-1)         human human0_ 
(http--0.0.0.0-8080-1)     where
(http--0.0.0.0-8080-1)         human0_.human_id=? for update
(http--0.0.0.0-8080-1)             

(http--0.0.0.0-8080-1) Human locked in EJB control.DBDeadlockTry@38d3904c

(EJB default - 2) Hibernate: 
(EJB default - 2)     select
(EJB default - 2)         animal0_.animal_id as animal1_104_0_,
(EJB default - 2)         animal0_.animal_name as animal2_104_0_ 
(EJB default - 2)     from
(EJB default - 2)         animal animal0_ 
(EJB default - 2)     where
(EJB default - 2)         animal0_.animal_id=? for update
(EJB default - 2)             

(EJB default - 2) Animal locked in EJB control.DBDeadlockTry@34634491

(EJB default - 2) Hibernate: 
(EJB default - 2)     select
(EJB default - 2)         human0_.human_id as human1_105_0_,
(EJB default - 2)         human0_.human_name as human2_105_0_ 
(EJB default - 2)     from
(EJB default - 2)         human human0_ 
(EJB default - 2)     where
(EJB default - 2)         human0_.human_id=? for update
(EJB default - 2)             

(http--0.0.0.0-8080-1) Hibernate: 
(http--0.0.0.0-8080-1)     select
(http--0.0.0.0-8080-1)         animal0_.animal_id as animal1_104_0_,
(http--0.0.0.0-8080-1)         animal0_.animal_name as animal2_104_0_ 
(http--0.0.0.0-8080-1)     from
(http--0.0.0.0-8080-1)         animal animal0_ 
(http--0.0.0.0-8080-1)     where
(http--0.0.0.0-8080-1)         animal0_.animal_id=? for update
(http--0.0.0.0-8080-1)   

...
Caused by: org.hibernate.exception.LockAcquisitionException: Deadlock detected. 

The current transaction was rolled back.
...
Caused by: org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #4 (user: SA) is waiting to lock PUBLIC.ANIMAL while locking PUBLIC.HUMAN (exclusive).
Session #5 (user: SA) is waiting to lock PUBLIC.HUMAN while locking PUBLIC.ANIMAL (exclusive)."

...

(http--0.0.0.0-8080-1) Animal locked in EJB control.DBDeadlockTry@38d3904c

Database is able to recover from a deadlock. It does not wait infinitely. It does not even wait until lock acquisition time-out comes into play. The exception is thrown immediately. One of the transaction is a victim of the deadlock - it is rolled back, whereas the second transaction can be committed. The lock of  the 'Animal' entity was finally acquired.

Hibernate 4.1.9 with H2 database as well as JBoss 7.1.1 with stateless EJB exposed as RESTful web service were used to prepare the example.

-----------------------------------------------------------------------------------------------------------------------------------
UPDATE (one of my blog Readers asked me about providing a deadlock example in Java SE and Hibernate 3):

public class Hibernate3DbDeadlock {

 public static void main(String[] args) throws Exception {
  final SessionFactory sessionFactory = new Configuration()
  .addAnnotatedClass(Human.class)
  .addAnnotatedClass(Animal.class)
  .setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect")
  .setProperty("hibernate.connection.url", "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=10000")
  .setProperty("hibernate.current_session_context_class", "thread")
  .setProperty("hibernate.show_sql", "true")
  .setProperty("hibernate.hbm2ddl.auto", "create")
  .buildSessionFactory();
  
  Session savingSession = sessionFactory.getCurrentSession();
  savingSession.beginTransaction();
  savingSession.save(new Human());
  savingSession.save(new Animal());
  savingSession.get(Human.class, 1L);
  savingSession.getTransaction().commit();

  Session locking1Session = sessionFactory.getCurrentSession();
  locking1Session.beginTransaction();
  locking1Session.get(Human.class, 1L, LockOptions.UPGRADE);
  
  new Thread(new Runnable() {
   public void run(){
    Session locking2Session = sessionFactory.getCurrentSession();
    locking2Session.beginTransaction();
    locking2Session.get(Animal.class, 1L, LockOptions.UPGRADE);
    locking2Session.get(Human.class, 1L, LockOptions.UPGRADE);
   }
  }).start();
  
  TimeUnit.SECONDS.sleep(3);
  
  locking1Session.get(Animal.class, 1L, LockOptions.UPGRADE);
 }
}

and my dependencies from pom.xml:

<dependencies>

 <dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>3.6.10.Final</version>
 </dependency>
 <dependency>
  <groupId>javassist</groupId>
  <artifactId>javassist</artifactId>
  <version>3.12.1.GA</version>
 </dependency>
 <dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.3.173</version>
 </dependency>

</dependencies>