Hibernate - Fetch a sequential number from database, preventing duplicated keys during concurrency

问题: I have a situation maintaining a legacy project, using JSF / Primefaces / Hibernate, the database is DB2, the original code was migrated from Delphi to Java, but keeping th...

问题:

I have a situation maintaining a legacy project, using JSF / Primefaces / Hibernate, the database is DB2, the original code was migrated from Delphi to Java, but keeping the database structure since it came from a vendor (we can't change it). There are some tables used to fetch a sequential id (SELECT MAX and UPDATE after that).

The table structure has a composite key (year and number), the issue today is: we select the max number based on the year from a param table (which holds the "next sequential" value). Sometimes users using concurrently get the same number, causing errors when trying to persist duplicated keys.

I tried to implement a Hibernate Interceptor to fetch and set the value during the onSave method, but I was unable to make it avoid the duplicated keys issue (Tried using it as SessionFactory-scoped). Also I tried to make the methods syncronized, but it didn't work also.

Is there a way to prevent this duplicated key issue (programmatically, without the need of changing the database) using Hibernate features?

Thanks in advance!


回答1:

Did you check: optimistic locking and pessimistic locking.

Optimistic

When using optimistic locking, you map a special attribute (a number, a timestamp) as a version (so you actually have a column for it). This version is read when you retrieve an entity and included in the where clause during an update and incremented by Hibernate.

To illustrate how this works, let's imagine you load a Person entity by id=1 and with a current version=1. After a save, Hibernate will perform something like this:

 update PERSON set ID=1, NAME='NAME 1', VERSION=2 where ID=1 and VERSION=1;

So, now, imagine you have two concurrent transactions running, each of them loading the same entity (same version number) and changing the name.

Let's say transaction #1 is committed first, the following query is performed:

update PERSON set ID=1, NAME='NAME 1', VERSION=2 where ID=1 and VERSION=1;

It succeeds and the version gets incremented.

Then transaction #2 is committed, the following query is performed:

update PERSON set ID=1, NAME='NAME 2', VERSION=2 where ID=1 and VERSION=1; This one won't update anything because the where clause won't match any record. This is where you'll get an optimistic concurrency exception.

This strategy is appropriate when you don't maintain the connection, when concurrent accesses are not frequent, and scales really well. And everything is of course handled transparently by Hibernate for you, as long as you map a version attribute.

Pessimistic

When using pessimistic locking, Hibernate locks a record for your exclusive use until you have finished with it (typically using a SELECT ... FOR UPDATE). Any other concurrent transaction trying to access the same record will get suspended until the lock is removed. This strategy gives better predictability, at the price of performance and doesn't scale indefinitely.

  • 发表于 2019-01-06 16:41
  • 阅读 ( 253 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除