postgresql 休眠：更新时锁定一行，因此用户不会从中检索计数器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43229548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate : Lock a row while update, so user's don't retrieve a counter from it
提问by We are Borg
I am working on a Spring-MVC project in which I am using Hibernate as the ORM, PostgreSQL as our DB and in one of our Objects(GroupCanvas), we have a number which is incremented everytime when user takes some action, and then the GroupCanvas object is updated in DB, and it should be unique.
我正在开发一个 Spring-MVC 项目，在该项目中我使用 Hibernate 作为 ORM，使用 PostgreSQL 作为我们的数据库，并且在我们的一个对象（GroupCanvas）中，我们有一个数字，每次用户采取某些操作时都会递增，然后GroupCanvas 对象在 DB 中更新，它应该是唯一的。
THe problem we have currently is, if multiple users take action in front-end, some of them are getting duplicate numbers. We are working on fixing this now, so later we can implement a sequence and are assured that the numbers are unique.
我们目前遇到的问题是，如果多个用户在前端采取行动，其中一些用户会得到重复的数字。我们现在正在努力解决这个问题，以便稍后我们可以实现一个序列并确保数字是唯一的。
How can I ensure that when I am updating the row, other users are waiting till the row is updated. I tried LockMode.Pessimistic_write, and a few others, none helped.
如何确保当我更新行时，其他用户正在等待该行更新。我尝试了 LockMode.Pessimistic_write 和其他一些，都没有帮助。
Code :
代码 ：
@Override
public void incrementNoteCounterForGroupCanvas(int canvasId) {
Session session = this.sessionFactory.getCurrentSession();
session.flush();
Query query = session.createQuery("update GroupCanvas as gc set gc.noteCount=gc.noteCount+1 where gc.mcanvasid=:canvasId");
query.setParameter("canvasId",canvasId);
query.executeUpdate();
session.flush();
}
@Override
public GroupCanvas getCanvasById(int mcanvasid) {
Session session = this.sessionFactory.getCurrentSession();
session.flush();
return (GroupCanvas) session.get(GroupCanvas.class, mcanvasid,LockMode.PESSIMISTIC_WRITE);
}
Both methods are in DAO, which has @Transactionalannotation, and annotation present in service layer as well.
这两种方法都在 DAO 中，它有@Transactional注解，注解也存在于服务层。
Thank you.
谢谢你。
回答by Ruben
Looking at the method you have posted the usage if the 'LOCKING' technique is not quite correct. In order for a lock to end up with the result you are looking for the sequence of actions should be similar to the ones below (in the nutshell it is similar to the Double-Checked Locking but implemented using DB locks - https://en.wikipedia.org/wiki/Double-checked_locking).
如果“锁定”技术不太正确，请查看您发布的使用方法。为了让锁最终得到您正在寻找的结果，操作序列应该类似于下面的操作序列（简而言之，它类似于双重检查锁定，但使用 DB 锁实现 - https://en .wikipedia.org/wiki/Double-checked_locking）。
- Start the transaction (eg
@Transactionalannotation on your service method) - Retrieve entity from database with the PESSIMISTIC_WRITE lock mode (make sure to indicate hibernate that fresh copy should be read instead of the one stored in session cache)
- If required check the current value of the target field if it meets your invariants
- Perform the change/update on the field (eg, increment the value of a field )
- Save the entity (and make sure to flush the value to the DB if you do not want to wait for the auto-flush)
- Commit the transaction (done automatically when using
@Transactional)
- 启动事务（例如
@Transactional在您的服务方法上注释） - 使用 PESSIMISTIC_WRITE 锁定模式从数据库中检索实体（确保指示休眠状态应该读取新副本而不是存储在会话缓存中的副本）
- 如果需要，请检查目标字段的当前值是否满足您的不变量
- 对字段执行更改/更新（例如，增加字段的值）
- 保存实体（如果不想等待自动刷新，请确保将值刷新到数据库）
- 提交事务（使用时自动完成
@Transactional）
The essential difference of this sequence when compared with the posted method is that the update of the property value is performed while your transaction holds a lock on the target entity/db row, hence preventing other transactions from reading it while your update is in progress.
与 posted 方法相比，此序列的本质区别在于，当您的事务在目标实体/数据库行上持有锁定时执行属性值的更新，从而防止其他事务在您的更新正在进行时读取它。
Hope this helps .
希望这可以帮助 。
UPDATE:I believe something like the code snippet bellow should work as expected :
更新：我相信像下面的代码片段应该按预期工作：
@Transactional
@Override
public void incrementNoteCounterForGroupCanvas(int canvasId) {
final Session session = this.sessionFactory.getCurrentSession();
final GroupCanvas groupCanvas = session.get(GroupCanvas.class, canvasId,LockMode.PESSIMISTIC_WRITE);
session.refresh(groupCanvas);
groupCanvas.setNoteCount(groupCanvas.getNoteCount()+1);
session.saveOrUpdate(groupCanvas);
session.flush();
}
