限制数据库中具有特定值的行

Question

我有一个用java编写的应用程序，它运行在多个jvm上，并写入同一个数据库。我有一个名为:request的实体它有这些列:id, userId, text

我想限制一个用户可以发布的请求数量为3

我不能写这样的代码:

line1  open transaction
line2  check if requests for that userId are less than 3
line3  if yes insert that record
line4  else abort
line5  close transaction

，因为jvm是2，有可能jvm1的一个线程正在执行line2，而jvm2的另一个线程正在执行同一行。因此，各自事务中的两个线程(tx1和tx2, jvm1和jvm2)认为记录少于3条，并将提交事务。

一种解决方案是在另一个表中写一个标志，并使用该标志锁定那部分代码，但我更喜欢另一种解决方案。

你有什么想法吗?:

我正在使用JPA，所以我正在寻找一种方法，我仍然可以使用它来实现这个结果。

Answer 1

您还将有一个users表，其PK由userId引用。

一种解决方案是在更新request表中的行之前对users表中的相应行进行锁。试图对同一用户的请求进行操作的并发事务必须等待获得相同的锁，直到前一个事务提交/回滚。如:

BEGIN;
SELECT FROM users
WHERE id = 123
FOR NO KEY UPDATE;  -- strong enough
INSERT INTO requests (...)
SELECT ... -- your constants here
FROM   request 
WHERE  userid = 123
HAVING count(*) < 3;  -- ①
DELETE ...
UPDATE ...
COMMIT;

①当SELECT列表只有常量时，没有GROUP BY的查询可以工作。如果找到至少3行，HAVING count(*) < 3将跳过INSERT，并且不会引发异常。

FOR NO KEY UPDATE比FOR UPDATE弱，因为它仍然允许并发写，只要关键列没有改变。对于你的任务来说已经足够好了。

您只需要确保对request的所有相关写访问都采用这种方法。