java - 从多个线程中选择和更新一行的安全方法

Question

表拍卖:拍卖类型(整数)，拍卖(Json/文本)。

我需要一种从多线程更新 Auction.Auctioned 的方法。

例子:

机器 1:

int id = 1;
Object obj = parse("SELECT Auctioned FROM Auction WHERE AuctionId = "+id+";");
obj = edit(obj);

update("UPDATE Auction SET Auctioned = "+obj.toString()+" WHERE AuctionId="+id+";");

机器 2:

update("UPDATE Auction SET Auctioned = <value> WHERE AuctionID=<value2>");

问题是如果:Thread1 获取 Sql 列。Thread2 更新 Sql Column。Thread1 使用过时的提取结果更新 Sql Column，结果 Thread2 更新被覆盖。

所以我想找到一种安全的方法来防止数据丢失，例如:

Thread1 fetchs Sql Column & stores checksum.
Thread2 updates Sql Column.
Thread1 updates Sql Column if checksum equals storedChecksum.

我希望在 sql 引擎端而不是在进程中完成校验和检查。

但为此，我需要一种方法允许 Sql 更新查询在数据库引擎中自行取消，并在不满足校验和条件时返回结果。

Answer 1

有一种通用的 RDBMS 机制可以满足这一要求，称为 SELECT ... FOR UPDATE。

原则是，在选择行时，您向 RDBMS 表明您将很快更新它，并且它应该锁定它。如果另一个 SQL session 试图在锁被释放之前访问(读取、更新)数据，它将被置于等待状态。

大多数 RDBMS 都实现了这个功能。通常的约束是您需要使用数据库事务 才能正常工作(即禁用autocommit)。当拥有的事务被提交(或回滚)时，锁被释放。

---

与 MySQL InnoDB :

SELECT ... FOR UPDATE : For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels.

SELECT Auctioned FROM Auction WHERE AuctionId = ? FOR UPDATE;

---

在 Oracle 中:FOR UPDATE clause .

The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction.

SELECT Auctioned FROM Auction WHERE AuctionId = ? FOR UPDATE OF Auctioned;

---

SQL Server有点不一样，需要用到the UPDLOCK hint :

Specifies that update locks are to be taken and held until the transaction completes.

SELECT Auctioned FROM Auction WITH (UPDLOCK) WHERE AuctionId = ?;

---

Postgres : explicit row-level locking

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. [...] That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE [...] of these rows will be blocked until the current transaction ends.

SELECT Auctioned FROM Auction WHERE AuctionId = ? FOR UPDATE;