How acquiring shared and exclusive locks works in Hibernate

Question

I try to understand the way READ COMMITED and READ UNCOMMITED isolation levels works in Hibernate and need some explanation.

There are 2 threads THR1 and THR2 both executing the same transactional method (Spring Transactional annotation with isolation level set to READ COMMITED). Name transactions created be these threads TRA1 and TRA2 accordingly. The transactional method looks as follows:

public void updateOrSavePreference(String name, String value) {
  Preference preferenceToUpdate = findPreferenceUsingNamedQuery(name); // line 1. shared read lock acquired (uses HibernateTemplate.findByNamedQueryAndNamedParam(...))
  if (preferenceToUpdate != null) { // line 2.
    preferenceToUpdate.setValue(value); // line 3. exclusive write lock acquired (actually I use the HibernateTemplate.merge(...) method
                                        // instead a setter because the entity type is immutable, but it seems irrelevant for this example)
  } else { // line 4.
    HibernateTemplate.save(preferenceToUpdate); // line 5. exclusive write lock acquired
  }
}

The Preference class is annotated with Entity(optimisticLock = OptimisticLockType.NONE) to enforce 2PL model for this entity (am I wrong?). I use Oracle database.

Consider following scenarios:

1. Let's assume that thread THR1 steps to line 1 and queries an object. If I understand correctly, the transaction TRA1 created by this thread acquires a shared read lock for the queried entiry. Then, if the THR2 thread steps to line 3 trying to acquire an exclusive write lock for this entity shouldn't THR2 be blocked until TRA1 releases the read lock?

2. Let's assume that thread THR1 steps to line 3 and acquires an exclusive write lock for an entity (exclusive lock is held until the TRA1 transaction completes). Then, the THR2 thread steps to line 1 and tries to query this entity. Shouldn't THR2 be blocked because the TRA2 transaction tries to acquire a read lock while other transaction TRA1 holds an exclusive write lock for this entity?

3. If I reproduce the scenario from point 2 for the READ UNCOMMITED isolation level, THR2 executing the TRA2 transaction doesn't see changes made by THR1 in the TRA1 transaction even after refreshing or querying the entity again ("evaluate expression" under debug). Why?

Answer 1

Technically read committed can be achieved by setting read locks. But not necessarily. If your DBMS supports MVCC you always read committed data (except what was changed in your own transaction) without setting a lock.

So I suspect you do your tests using oracle, mysql (INNODB) or postgres? All these DBMS support MVCC as default, so they never set shared read locks.

Since you are using Oracle "the MVCC" Database there will not be implemented 2PL-protocol even if you configure that at your entity. If you want to find out what really is done on your DBMS in native statements just activate the outputting of the native-statements as you can do in persistence.xml:

<property name="hibernate.show_sql" value="true" />

Perhaps you should also have a look at transaction-isolation-levels-relation-with-locks-on-table or first at: locks and oracle