How does Hibernate handle LockMode.PESSIMISTIC_WRITE? Does it handle it in the same way as SELECT FOR UPDATE in native queries?
I run 2 experimental transactions (T1 and T2) that execute the same native SQL that selects an entity using SELECT FOR UPDATE and then updates it. I run them in 2 threads to ensure that the timing is like below:
1) T1 SELECT ... FOR UPDATE (based on a condition that becomes false after update)
2) T2 SELECT ... FOR UPDATE (based on a condition that becomes false after update)
3) T2 updates the entity
4) T1 updates the entity
This always leads to the same flow and result that I understand and expect:
1) T1 reads the single entity based on a condition
2) T2 doesn't select anything
3) T1 updates the entity so that it cannot be selected based on the condition anymore
4) T2 doesn't update anything
The entity is of type FIELD that has 4 attributes: ID, NAME, TYPE, DESCRIPTION. Only DESCRIPTION is updated.
Here is the code to run such a transaction:
private void selectForUpdateNativeQueryTransaction(String name, int delayBeforeRead, int delayBeforeCommit) {
Transaction tx = null;
Session session = null;
try {
session = factory.openSession();
tx = session.beginTransaction();
System.out.println(name + " BEGIN");
try {
TimeUnit.SECONDS.sleep(delayBeforeRead);
} catch (InterruptedException e) {
e.printStackTrace();
}
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM FIELD WHERE DESCRIPTION=?1 FOR UPDATE");
sqlQuery.setParameter("1", DESC);
List results = sqlQuery.list();
System.out.println(name + " SELECT EXECUTED");
results.forEach(r -> {
Object[] row = (Object[]) r;
for (int i = 0; i < row.length; i++) {
System.out.println(name + " : FIELD READ [" + row[i] + "]");
}
});
try {
TimeUnit.SECONDS.sleep(delayBeforeCommit);
} catch (InterruptedException e) {
e.printStackTrace();
}
Query query = session.createSQLQuery("UPDATE FIELD SET DESCRIPTION=?1 WHERE DESCRIPTION=?2");
query.setParameter("1", name);
query.setParameter("2", DESC);
int result = query.executeUpdate();
System.out.println(name + " UPDATED ROWS: " + result);
tx.commit();
} catch (Exception e) {
fail();
if (tx != null) {
tx.rollback();
}
} finally {
session.close();
}
System.out.println(name + " COMMITTED");
}
Below is the code to run both transactions in different threads:
@Test
public void firstReadsTheOtherRejected() {
ExecutorService es = Executors.newFixedThreadPool(3);
Runnable selectForUpdateNativeQueryTransaction1 = () -> {
selectForUpdateNativeQueryTransaction("T1", 1, 8);
};
Runnable selectForUpdateNativeQueryTransaction2 = () -> {
selectForUpdateNativeQueryTransaction("T2", 3, 2);
};
Runnable selectForUpdateHqlQueryTransaction1 = () -> {
selectForUpdateHqlQueryTransaction("T1", 1, 8);
};
Runnable selectForUpdateHqlQueryTransaction2 = () -> {
selectForUpdateHqlQueryTransaction("T2", 3, 2);
};
// es.execute(selectForUpdateHqlQueryTransaction1);
// es.execute(selectForUpdateHqlQueryTransaction2);
es.execute(selectForUpdateNativeQueryTransaction1);
es.execute(selectForUpdateNativeQueryTransaction2);
es.shutdown();
try {
es.awaitTermination(1, TimeUnit.MINUTES);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
And here is the output:
T2 BEGIN
T1 BEGIN
Hibernate: SELECT * FROM FIELD WHERE DESCRIPTION=? FOR UPDATE
T1 SELECT EXECUTED
T1 : FIELD READ [16]
T1 : FIELD READ [Test field]
T1 : FIELD READ [Test type]
T1 : FIELD READ [This is a field for testing]
Hibernate: SELECT * FROM FIELD WHERE DESCRIPTION=? FOR UPDATE
Hibernate: UPDATE FIELD SET DESCRIPTION=? WHERE DESCRIPTION=?
T1 UPDATED ROWS: 1
T1 COMMITTED
T2 SELECT EXECUTED
Hibernate: UPDATE FIELD SET DESCRIPTION=? WHERE DESCRIPTION=?
T2 UPDATED ROWS: 0
T2 COMMITTED
Note: T2 UPDATED ROWS: 0
However if I try to achieve the same using Hql it gives me some strange output. Here is the code for a transaction:
private void selectForUpdateHqlQueryTransaction(String name, int delayBeforeRead, int delayBeforeCommit) {
Transaction tx = null;
Session session = null;
try {
session = factory.withOptions().interceptor(new HibernateInterceptor()).openSession();
tx = session.beginTransaction();
System.out.println(name + " BEGIN");
try {
TimeUnit.SECONDS.sleep(delayBeforeRead);
} catch (InterruptedException e) {
e.printStackTrace();
}
Query query = session.createQuery("SELECT f FROM Field f WHERE f.description=?1").setLockMode("this", lockMode);
query.setString("1", DESC);
List fields = query.list();
System.out.println(name + " SELECT EXECUTED");
fields.forEach(obj -> {
Field field = (Field) obj;
System.out.println(name + " : FIELD READ [" + field.getId() + "]");
System.out.println(name + " : FIELD READ [" + field.getName() + "]");
System.out.println(name + " : FIELD READ [" + field.getType() + "]");
System.out.println(name + " : FIELD READ [" + field.getDescription() + "]");
});
try {
TimeUnit.SECONDS.sleep(delayBeforeCommit);
} catch (InterruptedException e) {
e.printStackTrace();
}
Session finalSession = session;
fields.forEach(obj -> {
Field field = (Field) obj;
field.setDescription(name);
finalSession.update(field);
});
System.out.println(name + " UPDATED ROWS: " + fields.size());
tx.commit();
} catch (Exception e) {
fail();
if (tx != null) {
tx.rollback();
}
} finally {
session.close();
}
System.out.println(name + " COMMITTED");
}
The output:
T2 BEGIN
T1 BEGIN
апр 22, 2019 4:29:37 PM org.hibernate.loader.Loader determineFollowOnLockMode
WARN: HHH000445: Alias-specific lock modes requested, which is not currently supported with follow-on locking; all acquired locks will be [PESSIMISTIC_WRITE]
апр 22, 2019 4:29:37 PM org.hibernate.loader.Loader shouldUseFollowOnLocking
WARN: HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
Hibernate: select field0_.ID as ID1_9_, field0_.DESCRIPTION as DESCRIPTION2_9_, field0_.NAME as NAME3_9_, field0_.TYPE as TYPE4_9_ from FIELD field0_ where field0_.DESCRIPTION=?
Hibernate: select ID from FIELD where ID =? for update
T1 SELECT EXECUTED
T1 : FIELD READ [16]
T1 : FIELD READ [Test field]
T1 : FIELD READ [Test type]
T1 : FIELD READ [This is a field for testing]
апр 22, 2019 4:29:39 PM org.hibernate.loader.Loader determineFollowOnLockMode
WARN: HHH000445: Alias-specific lock modes requested, which is not currently supported with follow-on locking; all acquired locks will be [PESSIMISTIC_WRITE]
апр 22, 2019 4:29:39 PM org.hibernate.loader.Loader shouldUseFollowOnLocking
WARN: HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
Hibernate: select field0_.ID as ID1_9_, field0_.DESCRIPTION as DESCRIPTION2_9_, field0_.NAME as NAME3_9_, field0_.TYPE as TYPE4_9_ from FIELD field0_ where field0_.DESCRIPTION=?
Hibernate: select ID from FIELD where ID =? for update
T1 UPDATED ROWS: 1
previousState in onFlushDirty(): []
previousState in onFlushDirty(): This is a field for testing
previousState in onFlushDirty(): Test field
previousState in onFlushDirty(): []
previousState in onFlushDirty(): Test type
currentState in onFlushDirty(): []
currentState in onFlushDirty(): T1
currentState in onFlushDirty(): Test field
currentState in onFlushDirty(): []
currentState in onFlushDirty(): Test type
Hibernate: update FIELD set DESCRIPTION=?, NAME=?, TYPE=? where ID=?
T2 SELECT EXECUTED
T1 COMMITTED
T2 : FIELD READ [16]
T2 : FIELD READ [Test field]
T2 : FIELD READ [Test type]
T2 : FIELD READ [This is a field for testing]
T2 UPDATED ROWS: 1
previousState in onFlushDirty(): []
previousState in onFlushDirty(): This is a field for testing
previousState in onFlushDirty(): Test field
previousState in onFlushDirty(): []
previousState in onFlushDirty(): Test type
currentState in onFlushDirty(): []
currentState in onFlushDirty(): T2
currentState in onFlushDirty(): Test field
currentState in onFlushDirty(): []
currentState in onFlushDirty(): Test type
Hibernate: update FIELD set DESCRIPTION=?, NAME=?, TYPE=? where ID=?
T2 COMMITTED
As you may have noticed I use Hibernate Interceptor to intercept the SQL operations. Interceptor.onFlushDirty() is called before SQL UPDATE. So it's not clear to me why I see in output both transactions updating the value:
...
currentState in onFlushDirty(): T1
...
currentState in onFlushDirty(): T2
...
First question was: Is there really only 1 update or do both transactions update the value? There must be only 1 update as I changed the code responsible for UPDATE so that it didn't overwrite the old value but appended transaction name to it. After the both transactions had completed the column didn't contain something like "...T1:T2" at the end. It's good.
But next question was: Why does HQL version select an old entity although native SQL version doesn't? Is it because of some caching? I want to know exactly which transaction "succeded" and which didn't.
