0

I have a table for booking system 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. affectedRowsCount will always be 1 on concurrent requests which indicates a race condition. 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
Mouneer
  • 12,827
  • 2
  • 35
  • 45
  • 2
    Atomic doesn't mean serializable. It means that if the operation fails, all changes are rolled back – Panagiotis Kanavos Jun 29 '22 at 15:22
  • 1
    What database are you using? PostgreSQL and SQLite have *completely* different behavior when it comes to concurrency, which is your actual question. What's your *real* code too? It matters *a lot*. Instead of trying to update one row at a time, you can write an UPDATE or INSERT that updates multiple rows based on the results of a SELECT. – Panagiotis Kanavos Jun 29 '22 at 15:23
  • 1
    And why `(SELECT COUNT(*) FROM "Reservation" WHERE ...) < some integer` ? Are you trying to implement a queue? Or a reservation limit? Update the first N rows? There are ways to do that without cursors and Row-By-Agonizing-Row updates – Panagiotis Kanavos Jun 29 '22 at 15:26
  • Creating one reservation at my case is mainly depending on number of already existing reservations. So yes it's a limit. So the idea is that the row itself is not updated concurrently. It is the Reservations table that may have a new row inserted concurrently which should be considered during COUNT. If one transaction added new row, other running transaction should be familiar with that row to be able to account for it during the COUNT – Mouneer Jun 29 '22 at 15:30
  • I mainly interested in PostgreSQL – Mouneer Jun 29 '22 at 15:40
  • 1
    It **is** atomic - but it's subject to race conditions when run from concurrent transactions. –  Jun 29 '22 at 15:46
  • 1
    Instead of using multiple queries use a single `UPDATE ... FROM` with a `GROUP BY` and `HAVING` clause to stop it from working if there are fewer queries than needed. You many need to use a CTE or nested query for this. Post your tables, the query you tried and the logic, not pseudocode or how you think the logic can be implemented. It's very hard to write complex queries based on vague indirect descriptions – Panagiotis Kanavos Jun 30 '22 at 07:05
  • 1
    I'll repeat this: post the business logic, not how you think it can be implemented. Only low-traffic systems perform a full count and check. This simply doesn't scale because it has to scan/lock the entire table. When you book a flight the airlines don't run `SELECT COUNT()` to see how many seats are available in a flight. Not when there are *millions* of searches per day. The search page displays cached results and leaves it to the reservation page to actually check if these seats are still available and *reserve, not book them*. Once you pay, the flight is booked. – Panagiotis Kanavos Jun 30 '22 at 07:14
  • 1
    Instead of counting remaining seats, airlines split them into multiple buckets from the start. This way, instead of counting how many seats are left before updating, you can do a simple `UPDATE ... WHERE Bucket=X and IsAvailable=True RETURNING id` to book in a limited-availability bucket. Instead of checking for N remaining seats, but these N seats into a different bucket – Panagiotis Kanavos Jun 30 '22 at 07:20

2 Answers2

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    From the comments it seems the OP wants to create/update a reservation only if there are enough rooms left. That could be a single UPDATE with GROUP BY and HAVING – Panagiotis Kanavos Jun 30 '22 at 07:08
  • 1
    Or it may require a completely different design. Airlines don't count available seats when people search for flights. – Panagiotis Kanavos Jun 30 '22 at 07:16
  • 1
    For example, splitting rooms into Normal and Overflow would avoid counting. `UPDATE ... WHERE Bucket='Normal' and IsAvailable=True RETURNING id` – Panagiotis Kanavos Jun 30 '22 at 07:23
  • 1
    You are right, the question is quite unclear, and I will vote to close. I was talking about the general case, where you want to enforce a condition on the table as a whole with a query. – Laurenz Albe Jun 30 '22 at 09:11
  • 1
    The real question behind this though is very interesting. And affected by concurrency behavior like MVCC. The actual business requirements may allow using a better design – Panagiotis Kanavos Jun 30 '22 at 09:12
1

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.

dcp
  • 54,410
  • 22
  • 144
  • 164
  • The idea is that the row itself is not updated concurrently. It is the Reservations table that may have a new row inserted concurrently which should be considered during COUNT. If one transaction added new row, other running transaction should be familiar with that row to be able to account for it during the COUNT because creating one reservation at my case is mainly depending on number of already existing reservations – Mouneer Jun 29 '22 at 15:27
  • 1
    "If one transaction added new row, other running transaction should be familiar with that row" That can't happen until first transaction is committed unless you want to get into stuff like dirty reads (which is a really bad idea in my opinion). According to this answer (https://stackoverflow.com/questions/33646012/postgresql-transaction-isolation-read-uncommitted), dirty reads aren't supported in postgresql anyway. – dcp Jun 29 '22 at 15:32
  • 1
    But to solve your particular problem, why not just have some sort of resource table that represents the entity (hotel, etc) that you are trying to book a reservation for. Then, just do SELECT FOR UPDATE against that resource before you begin your logic. You can then prevent other processes from trying to book against that resource until you commit the transaction. – dcp Jun 29 '22 at 15:36