I have a table called Reservations and I want to apply a certain constraint that should be atomic. Simply, I just want to conditionally insert a row into that table. I don't want to read-prepare-write because it will cause race conditions. I decided to insert an initial row then update it with a sub-query condition and check affected rows count. When I do some concurrency tests, I find that affectedRowsCount is always 1 which indicates for me that the logic is still subjected to race conditions. I know that isolation level of Serializable and lock mechanisms will help but I want to discuss other less strict ways

Pseudo Code

Start transaction
Insert single row at table Reservations (Lets call Row)
affectedRowsCount = Update Reservations where ID = "Row".id AND (SELECT COUNT(*) FROM "Reservation" WHERE ...) < some integer
if (affectedRowsCount === 0) throw Already Reserved Error
Commit transaction

CodePudding user response：

In general, the way to prevent other queries from having access to a row(s) for locking purposes is to use SELECT FOR UPDATE. I'm not sure if you're using postgresql or sqlite, but you can read about the postgresql functionality here. Sqlite does not support it.

The idea is that you can lock the row you for which are interested, and then do whatever operations you need to without worrying about other queries updating that row, and then commit your transaction.

A common scenario for this would be when you're trying to book a reservation, as it looks like your example may be doing something along those lines. We would do a SELECT FOR UPDATE on the row containing the resource we want to book, then check the available dates the user is wanting to book, and once we have ensured that the dates are available for that resource, go ahead and book it. The SELECT FOR UPDATE prevents the possibility of other people trying to book the same resource at the same time we are.

CodePudding user response：

There is no way to do this except

1. using SERIALIZABLE transaction isolation

2. locking everything in sight to serialize operations

It is unclear what exactly you are trying to do, but perhaps an exclusion constraint on timestamp ranges can help.