Hibernate: Increment a integer field

Question

I want to increment a field in a database table by 1 using Hibernate.

I can do this by simply loading an object, incrementing the value and updating the object however if another thread comes along and updates the field in between the load and the update then the value will become corrupted.

So instead I have called an update directly on the database:

Query updateHits = createQuery(
    "UPDATE Job SET hitCount = hitCount + 1 WHERE id = :jobId" );
updateHits.setInteger( "jobId", job.getId() );
updateHits.executeUpdate();

However my understanding is that this bypasses optimistic locking in the database and I am currently experiencing an issue with deadlocks in SQL server and I believe this is the culprit.

Is there a way to increment a field without calling the update directly and while maintaining data integrity?

might want to check to see how your locks are created. If you're getting a table lock for that update something's wrong. If you're getting a row lock I don't see how it's going to cause a deadlock. — BlackICE
– BlackICE, Commented Mar 25, 2011 at 11:51
I have actually managed to resolve the deadlock issue just by ensuring this update is the last statement to run within the transaction. However i'm still interested to know of better solutions. — 3urdoch
– 3urdoch, Commented Mar 25, 2011 at 12:17
your "increment-by-query" solution is ten times better than dealing with pessimistic locks , especially if you are working multi-thread and long running transactions — kommradHomer
– kommradHomer, Commented Oct 28, 2014 at 16:10

Lucas de Oliveira · Accepted Answer · 2011-03-25 12:03:21Z

I can do this by simply loading an object, incrementing the value and updating the object however if another thread comes along and updates the field in between the load and the update then the value will become corrupted.

If the second thread is also using hibernate and you're using hibernate versioning (@Version) the one thread that commits first and changes the object will set the new version on the object.

When the second thread commit comes in, hibernate will check for optimistic lock fail and will throw an exception - StaleObjectException if I'm not mistaken. Will a catch block around your concurrent code you'll have the chance to load the object again and try your update if it makes sense. If you're using JPA an OptimisticLockException will be thrown.

There are loads of info at the Hibernate Optimistic Concurrency Control Section.

Cheers!

Collectives™ on Stack Overflow

Hibernate: Increment a integer field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related