enq: TX – row lock contention. Deadlock – When parent and child transaction try to update the same row.

In any system, careful attention should be paid to transaction boundaries, also the transaction isolation and transaction scope. Otherwise we may shoot ourselves in foot.

We have service which update a row, this service calls another service, which in turn calls another service and so on. There is long chain of service call. Somewhere in the chain one of service starts new transaction, and tries to update the same records, its cause the 2 transaction to create row lock contention.

This simplified version of real life example.

Service Class

@Override
@Transactional (readOnly = false, propagation=Propagation.REQUIRES_NEW)
public void parentUpdatePassword (String password) throws BackingStoreException {
                          updatePassword(“shri”, password+1);
                          userDAO.getSessionFactory().getCurrentSession().flush(); // this cause update to go to database, but it does not commit.
                          userService1.updatePassword(“shri”, password+2); // this method is in service 1 class which is below
}

Service 1 Class

@Override
@Transactional (propagation=Propagation.REQUIRES_NEW)
public void updatePassword (String login, String password) {
                        User user = loadUserByLogin(login);
                         user.setPassword(password);
                         updateUser(user);
}

In above case the transaction T1 starts when parentUpdatePassword() is called. It updates the password, and as soon as flush is called it send this update to database, which locks this row in database. Since transaction T1 is not finished, it does not commit and does not release the lock on the row. This method calls userService1.updatePassword() which also starts in new transaction T2. T2 also tries to update the same record, but it has to wait for T1 to complete. But T1 cannot complete till T2 completes, so it sort of deadlock. Call to method parentUpdatePassword() never terminate. That particular row in user table is locked for ever. Unless we kill method execution or kill session from database. 

At this point, if try to see the session in oracle, select lockwait, event, program, type from v$Session

You will record like this – AFA025DC | enq: TX – row lock contention | JDBC Thin Client | USER

If we remove flush() in method parentUpdatePassword(), the row contention would not happen. First I do not recommend calling hibernate flush manually, hibernate will automatically flush before committing the transaction. (There are few rear scenarios where user of flush() is unavoidable because way hibernate orders insert, update, delete when transaction commit, will cover that in separate blog)

More fundamental problem is creating a new child transaction and trying to update the same record in the child transaction, we should not be doing that. Even if removing flush resolve the row contention, negative effect is the update that we intended to achieve in child transaction T2 will be over written when parent transaction T1 commits. So password will always be password + 1.

There are valid reasons for creating new transaction, but in this case analysis revealed that we did not require new transaction.

In conclusion, whenever creating new transaction be extra cautious. Keep in mind there is overhead associate with creating a new transaction. Always pay attention to transaction boundary.