Short story, we have an issue on MySQL where we use a pessimistic lock (select for update) on a particular row. If we spawn multiple concurrent transactions, I can see them all queue up waiting for their lock and release in turn, but none of the subsequent threads can see the changes committed in the first thread.
Our applications is a RESTeasy application using session per request. Testing is done by firing up several requests simultaneously.
Our locking method in question, with some debug code:
Code:
public Pool lockAndLoad(Pool pool) {
log.info("##### Getting lock for pool: " + pool.getId());
currentSession().buildLockRequest(LockOptions.UPGRADE).setTimeOut(LockOptions.WAIT_FOREVER).lock(pool);
try {
Thread.sleep(5000); // purely for testing, doesn't change anything except make sure the log statements aren't coming in out of order
}
catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
currentSession().refresh(pool);
// I have also tried this instead of refresh, but it made no difference:
//currentSession().clear();
//pool = find(pool.getId()); // this is just a load wrapper
log.info("##### Got lock: entitlements = " +
pool.getEntitlements().size());
return pool;
}
In this case each transaction is creating a new entry in the entitlement collection. The debug logging output looks like the following:
Sep 13 11:34:49 [http-bio-8443-exec-3] INFO org.candlepin.model.PoolCurator - ##### Getting lock for pool: 402881984117bdb3014117bed7220051 initial version = 0
Sep 13 11:34:49 [http-bio-8443-exec-6] INFO org.candlepin.model.PoolCurator - ##### Getting lock for pool: 402881984117bdb3014117bed7220051 initial version = 0
Sep 13 11:34:49 [http-bio-8443-exec-8] INFO org.candlepin.model.PoolCurator - ##### Getting lock for pool: 402881984117bdb3014117bed7220051 initial version = 0
Sep 13 11:34:49 [http-bio-8443-exec-2] INFO org.candlepin.model.PoolCurator - ##### Getting lock for pool: 402881984117bdb3014117bed7220051 initial version = 0
Sep 13 11:34:54 [http-bio-8443-exec-3] INFO org.candlepin.model.PoolCurator - ##### Got lock, version = 0, entitlements = 0
Sep 13 11:34:54 [http-bio-8443-exec-3] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:34:54 [http-bio-8443-exec-7] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:34:59 [http-bio-8443-exec-6] INFO org.candlepin.model.PoolCurator - ##### Got lock, version = 0, entitlements = 0
Sep 13 11:34:59 [http-bio-8443-exec-6] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:34:59 [http-bio-8443-exec-1] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:35:04 [http-bio-8443-exec-8] INFO org.candlepin.model.PoolCurator - ##### Got lock, version = 0, entitlements = 0
Sep 13 11:35:04 [http-bio-8443-exec-8] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:35:04 [http-bio-8443-exec-3] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:35:09 [http-bio-8443-exec-2] INFO org.candlepin.model.PoolCurator - ##### Got lock, version = 0, entitlements = 0
Sep 13 11:35:09 [http-bio-8443-exec-2] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
Sep 13 11:35:09 [http-bio-8443-exec-6] INFO org.candlepin.servlet.filter.CandlepinPersistFilter - ##### about to commit
This shows four concurrent requests, each requesting a lock and receiving it sequentially which looks like the locking is working correctly. However once each thread acquires it's lock, it cannot see any of the changes from the other threads which have already committed. This is currently using a session clear and load, but it was originally a simple session.refresh(pool). Clearing the cache has no effect, each thread still cannot see the changes from the others if the transaction was started before the first one commits.
Mysql is using the default transaction isolation level of REPEATABLE READ. (using MySQL 5.5.32)
We are also using Hibernate 4.2.5. I started out on Hibernate 3.2 where the behaviour was a little different but still very broken. On 3.2 I would get an exception such as: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [org.candlepin.model.Pool#402881984103f332014104119b7114ae]
The code works fine on PostgreSQL so it appears to be either MySQL specific, or we're doing something wrong. Has anyone encountered similar problems on MySQL? This thread looks quite similar but unfortunately no responses: https://forum.hibernate.org/viewtopic.php?f=1&t=1017948
Thanks for your time.
Login
FAQ
Search