CODEWITHSUNDEEP
javamysqlhibernatejpalocking
lreeder
lreeder

Reputation: 12206

Hibernate JPA - PESSIMISTIC.WRITE not working

I'm using the Hibernate implementation of JPA 2.0 to create a counter in a table row. I'm using MySQL 5.5 with the InnoDB engine. I am trying to lock the counter row so that no processes outside the JVM can view that counter until my code has incremented it. My code looks like this:

  //inside a Transaction....

  //key is an enum
  final PropertyKey key = PropertyKey.DEPLOY_COUNTER;
  final Query query =
     entityManager.createQuery("FROM Property s where propertyKey = :key").setParameter("key", key);
  query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
  LOG.debug("Blocking (maybe) while waiting to update deploy counter");
  final Property counterAsProperty = (Property) query.getSingleResult();

  try
  {
     Thread.sleep(15000);
     //while sleeping I use MySQL cli to check value of property in database
  }
  catch (InterruptedException e)
  {
     e.printStackTrace();
  }

  //in java, increment counter by one and then save in db
  //...

I use Thread.sleep() to pause the code in the middle of the transaction. While the thread is sleeping, I use the MySQL CLI client to log into the database and check the value of the property. That session looks something like this:

user@mypc [user]> begin work;
Query OK, 0 rows affected (0.00 sec)

user@mypc [user]> select * from property where property_key = 'DEPLOY_COUNTER';
+----+---------+-----------+--------------------+----------------+
| id | version | encrypted | property_key       | property_value |
+----+---------+-----------+--------------------+----------------+
| 10 |       0 |         0 | DEPLOY_COUNTER     | 66             |   
+----+---------+-----------+--------------------+----------------+

Notice how the query returns immediately(0.00 sec) when I expected it to block until the thread had quit sleeping and the transaction completed. My understanding of LockModeType.PESSIMISTIC_WRITE is that it should put the retrieved row in an exclusive lock, unavailable for reading or writing by another transaction.

NOTE: updates to this row do block while the thread is sleeping.

Why can another DB connection view the data when the other transaction is running if I've set PESSIMISTIC_WRITE lock mode?

Upvotes: 0

Views: 4764

Answers (2)

Vipul Sharma
Vipul Sharma

Reputation: 11

For select queries this doesn't works

It will work if you query for 'select for update' then your query will wait till the time your application doesn't releases the lock

select * from property where property_key = 'DEPLOY_COUNTER' for update;

Above query will wait until the lock is released.

Upvotes: 0

lreeder
lreeder

Reputation: 12206

The default InnoDB isolation level is REPEATABLE READ, which allows transactions without the exclusive lock to read (but not update) the locked record. This allowed other transactions to do non-dirty reads on the record. SERIALIZABLE appears to prevent any reads on a locked record. You can set the isolation level when setting up the hibernate connection properties with hibernate.connection.isolation, although this would affect all connections, not a cost I was willing to pay. This SO post indicates a way to do it per connection, but the approach requires deprecated methods: JPA and MySQL transaction isolation level

I wound up using one of the

UPDATE counter SET value = LAST_INSERT_ID(value + 1);
SELECT LAST_INSERT_ID();

approaches cited here: http://tedyoung.me/2011/04/14/jpa-counters-and-sequences/, which didn't require any locking.

Upvotes: 1

Related Questions