My application use multi-tenancy (with ehcache) and pessimistic & optimistic locking feature from Hibernate. For the pesimistic locking, the isolation level is set to 2, and optimistic locking is using "opt_lock" table column as version field.

When I execute a certain web request, the process should create multiple DB session and should work as the following steps:
01. - Start Session 1
02. – SELECT Data by ID
03. – Start Session 2
04. — SELECT FOR UPDATE Data by ID
05. — UPDATE Data by ID
06. – Close Session 2
07. – Start Session 3
08. — SELECT FOR UPDATE Data by ID
09. — UPDATE Data by ID
10. – Close Session 3
11. - Close Session 1

The Data by ID is referring to the same table record. The problem is, I always encounter a deadlock at Step 08. It seems the database lock at Step 04 does not released after updating the data (Step 05).

Below are the application logs for the described problem above:

Step 01:
-------
[2012-11-29 11:50:52,696] INFO HibernateSession:53 - Start Session: d81cda
[2012-11-29 11:50:52,774]DEBUG HibernateSession:161 - Begin Trx: 2e749c

Step 02:
-------
Hibernate:
select
....
from
data this_
inner join
users user2_
on this_.owner_id=user2_.id
where
this_.id=?
order by
this_.create_date asc limit ?

Step 03:
-------
[2012-11-29 11:50:53,024] INFO HibernateSession:53 - Start Session: 163f47e
[2012-11-29 11:50:53,024]DEBUG HibernateSession:161 - Begin Trx: 19546ee

Step 04:
-------
Hibernate:
select
id
from
data
where
id =?
and opt_lock =? for update

Step 05:
-------
Hibernate:
update
data
set
...
where
id=?
and opt_lock=?

Step 06:
-------
[2012-11-29 11:50:53,040]DEBUG HibernateSession:93 - Commit Trx: 19546ee - 16ms
[2012-11-29 11:50:53,040]DEBUG HibernateSession:101 - Close Session: 163f47e

Step 07:
-------
[2012-11-29 11:50:59,383] INFO HibernateSession:53 - Start Session: 112731f
[2012-11-29 11:50:59,383]DEBUG HibernateSession:161 - Begin Trx: 1aec462

Step 08:
-------
Hibernate:
select
id
from
data
where
id =?
and opt_lock =? for update

DEADLOCK HAPPENS HERE!!!!! (Application hangs, the record is also cannot be retrieved via pgAdmin)

Below are the logs description info:
"Start Session" -> Call openSession() from SessionFactory, the hex is hashCode for the created session object
"Close Session" -> Call close() in session object, the hex is hashCode for the created session object
"Begin Trx" -> Call beginTransaction() in session object, the hex is hascCode for the created transaction object
"Commit Trx" -> Call commit() in transaction object, the hex is hascCode for the created transaction object

This is my environment:

• Hibernate 4.1.8 Final
• Spring 3.1.1
• Struts 2.3.7
• PostgresSQL 9.1

Can anyone help me to solve this problem?

Thank you.