Safe way to SELECT and UPDATE a row from multiple threads

Question

Table Auction : AuctionType (Integer), Auctioned (Json/Text).

I need a way to update Auction.Auctioned from Multiple Threads.

Example:

Machine1:

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+";");

Machine2:

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

The issue is that if: Thread1 fetchs Sql Column. Thread2 updates Sql Column. Thread1 uses out-dated fetched result to update Sql Column, as a result Thread2 update is overrided.

so I want to find a safe way to prevent data loss, for example such as:

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

I want the checksum check to be done in the sql engine side rather than in the process.

But for this, I will need a way to allow the Sql Update Query to cancel by itself in the database engine and return the result if checksum conditions are not met.

Answer 1

There is a general RDBMS mechanism to meet this requirement, which is called SELECT ... FOR UPDATE.

The principle is that, while selecting the row, you indicate your RDBMS that you will soon update it, and that it should lock it. If another SQL session tries to access (read, update) the data before the lock is released, it is put on wait.

Most RDBMS implement this functionnality. The usual constraint is that you need to use database transactions for this to work properly (ie disabling autocommit). The lock is released when the owning transaction is committed (or rolled back).

---

With 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;

---

In Oracle : the 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 is a bit different, you need to use 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;

Answer 2

I want the checksum check to be done in the sql engine side rather than in the process.

You can use pessimistic locking, for example, by using Select ... For Update. Already explained by @GMB earlier.

but i don't want to use the built-in checksum of it due to the possible need to change database engine in the future

You can use optimistic locking, but it'll requires you to add one field to schema. I use this way by adding new integer field named version.

create temp table temp_x(name varchar, id int, version int);
insert into temp_x values('foo', 1, 1);
update temp_x set name = 'bar', version = version + 1 where id = 1 and version = 1;
update temp_x set name = 'foobar', version = version + 1 where id = 1 and version = 1;

the second update will be failed, because version is not match.

In you case, may be change to

update("UPDATE Auction SET Auctioned = <value>, version=version+1 WHERE AuctionID=<value2> AND version=<version_value>");