Hi guys,
I have an entity, let's say it's Coupon, each time the coupon is used I need to increase the useCount field, when it reaches 100, the coupon cannot be used or updated anymore. See the mapping config below
Code:
<class name="Coupon" table="COUPON">
.......
<property name="useCount" type="long" column="USE_COUNT"></property>
.......
</class>
In native SQL, I can easily use the SQL below with database underlying lock
Code:
update coupon set use_count=use_count+1 where id=? and use_count < 100
If two transactions tries to update the same row, the latter one will be blocked until the first one commits or rolls back. Let's say the use_count is 0 initially, if T1 and T2 tries to update the same row, when T1 updates the row, T2 will be blocked, if T1 commits, T2 sees use_count as 1, and will update it to 2. If T1 rolls back, T2 sees use_count as 0 and will update it to 1.
However, in Hibernate, you need to load the row into memory as a POJO and increase the useCount property by Java code
Code:
coupon.setUseCount(coupon.getUseCount()+1);
session.flush();//helps a little, but not solve totally
.....
session.update(coupon);
This will not use the database underlying lock to lock the row so T1 and T2 probably sees the same snapshot and updates the use_count from 0 to 1 twice.
How can we avoid this kind of data race with database lock? I am running a cluster of application servers so synchronization in java does not help. I need it at the database layer.
Thanks,
Daniel
Login
FAQ
Search