英文:
Hibernate: select for update after select entity
问题
Situation: 数据库存储URL和它们的点击次数信息。相应地，如果有大量负载（例如，同时有100个请求），存在竞态条件的可能性很大，实际上会记录一个不准确的“点击”数量。
解决方案如下：悲观锁定：
// 实体
@NamedQuery(
name = "Link.findByIdForIncressClicks",
query = "SELECT l FROM Link l WHERE l.id = :id",
lockMode = LockModeType.PESSIMISTIC_WRITE
)
// Spring Data JPA 存储库
@Query(name = "Link.findByIdForIncressClicks")
Optional<Link> findByIdForIncressClicks(@Param("id") Long id);
// 服务层
@Override
@Transactional
public Link getLinkByShortCutURL(String shortUrl) {
Long id = encoder.decode(shortUrl);
// 在这里，我尝试使用本地的 entitymanager...
// Link link = em.find(Link.class, id, PESSIMISTIC_WRITE);
Link link = linkRepository.findByIdForIncressClicks(id).get();
link.incressClicks();
return linkRepository.save(link);
}
一切似乎都正常。READ_COMMITTED级别。但是错误的值仍然被写入数据库（通过JMeter进行的测试：40次点击，35次保存）。
Hibernate控制台中的查询：
Hibernate:
select
l1_0.id,
l1_0.clicks_count,
l1_0.created,
l1_0.owner_id,
l1_0.url
from
link_info.link l1_0
where
l1_0.id=?
Hibernate:
select
id
from
link_info.link
where
id=? for update
Hibernate:
update
link_info.link
set
clicks_count=?,
created=?,
owner_id=?
where
id=?
不知道，但Hibernate首先执行了“select entity”而不是在“id”上执行“select for update”。
如何修复这个问题？也许我在JPA中使用了错误的悲观锁？
更新：一些详细信息：Spring Boot v3.1（Hibernate - 6.2.1.Final）
英文:
Situation: the database stores URLs, and information on how many times they were clicked. Correspondingly, if there is a large load (for example, 100 requests at the same time), there is a great chance for a race condition, and we will, in fact, record a non-actual number of "clicks".
The solution was the following: pessimistic blocking:
// entity
@NamedQuery(
name = "Link.findByIdForIncressClicks",
query = "SELECT l FROM Link l WHERE l.id = :id",
lockMode = LockModeType.PESSIMISTIC_WRITE
)
// spring data jpa repository
@Query(name = "Link.findByIdForIncressClicks")
Optional<Link> findByIdForIncressClicks(@Param("id") Long id);
// service layer
@Override
@Transactional
public Link getLinkByShortCutURL(String shortUrl) {
Long id = encoder.decode(shortUrl);
// here i tried use native entitymanager...
// Link link = em.find(Link.class, id, PESSIMISTIC_WRITE);
Link link = linkRepository.findByIdForIncressClicks(id).get();
link.incressClicks();
return linkRepository.save(link);
}
Everything seems to be ok. READ_COMMITTED level. But the wrong value is still being written to the database (tests via JMeter: 40 clicks, 35 saved)
The select of hibernate in console:
Hibernate:
select
l1_0.id,
l1_0.clicks_count,
l1_0.created,
l1_0.owner_id,
l1_0.url
from
link_info.link l1_0
where
l1_0.id=?
Hibernate:
select
id
from
link_info.link
where
id=? for update
Hibernate:
update
link_info.link
set
clicks_count=?,
created=?,
owner_id=?
where
id=?
Idk, but hibernate doing first select entity,
instead of select for update on id.
How i can fix that? Maybe i have wrong using pessimistic locks in JPA?
UPD. some details: spring-boot v3.1 (hibernate - 6.2.1.Final)
答案1
得分: 1
通过更改 Hibernate 的版本来修复。我之前使用的是 6.2.1 版本，迁移到了 6.2.3 版本。
现在我有以下查询：
select
l1_0.id,
l1_0.clicks_count,
l1_0.created,
l1_0.owner_id,
l1_0.url
from
link_info.link l1_0
where
l1_0.id=? for no key update
英文:
Fixed by changing version of hibernate. I had 6.2.1, migrated to 6.2.3.
And now i have query:
select
l1_0.id,
l1_0.clicks_count,
l1_0.created,
l1_0.owner_id,
l1_0.url
from
link_info.link l1_0
where
l1_0.id=? for no key update
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论