1

(Please answer as generally as possible. But I am working in MS SQL Server and MySql, so if there is no general answer, go ahead and answer for one or both of those.)

Consider a reservation system implemented in a SQL database. I want to make sure that among many multiple users, only one user gets a reservation and no other user "thinks" they got it. It's a classic concurrency problem in DB work, but I'm not sure what the best answer is.

SPECIFICS:
Suppose each user has a UserID. We could imagine a few users are currently trying to make a reservation with UserID values of 1004, 1005, 1009, and 1011.

Suppose the resource and reservations are stored in a table, SEATS . We could imagine at one point the SEATS table contains:

    ----- SEATS -----------------------------
    SeatID   UserID   ResvTime
       1      1017    2014.07.15 04:17:18.000
       2      NULL    NULL
       3      NULL    NULL
       4      1012    2014.07.15 04:19:35.000
       5      1003    2014.07.15 04:20:46.000
    -----------------------------------------

Now suppose that "at the same time", users 1004 and 1005 try to grab SeatID 3. I'm wanting to know what SQL will properly make sure that only one of them gets the seat and the other gets a refusal. The simplest version of the code I can think of, in T-SQL, would be:

  PROC GRABSEAT @seatid INT, @userid INT, @obtained BIT OUTPUT
  BEGIN
     DECLARE @avail INT
     SET @avail = (SELECT UserID FROM SEATS WHERE (SeatID = @seatid))
     IF (@avail IS NULL)
        BEGIN
           UPDATE SEATS SET UserID = @userid, ResvTime = GETDATE() WHERE (SeatID = @seatid)
           SET @obtained = 1
        END
     ELSE
        SET @obtained = 0
  END

But the question is how to prevent this from allowing multiple concurrent users, all executing this PROC, getting a TRUE return on the same seat (say SeatID = 3).

For example, if both users 1004 and 1005 execute this PROC nearly simultaneously, they could both do the SELECT and get @avail = NULL before either of them tries to set the UserID column. Then both of them would run the UPDATE statement. Assuming nothing even worse could result, then one of them would overwrite the other's set, both would think they got the seat, but actually only the one who ran the UPDATE statement last would have their data stored in the SEATS table. The other one would have their data overwritten. This is referred to as a "lost input" problem. But what is the way in a SQL database to prevent it? I have been assuming that each single SQL statement is executed as a TRANSACTION. A TRANSACTION has the four so-called "ACID" properties. These properties are what I need. So, I think the answer, in a SQL database, is:

  BEGIN TRANSACTION
  EXCEUTE GRABSEAT @seatid= <id1>, @userid = <id2>, @obtained
  COMMIT

By doing so, the main property I need (isolation), will guarantee that the interleaved execution I'm worried about will not occur.

But I've seen articles that say it's not that simple at all. I think the big problem various articles point to is that not every TRANSACTION really runs in total atomicity and isolation. So, perhaps the above wrapping in a TRANSACTION will not achieve the desired result. If not, then what is needed?

Mark Goldfain
  • 731
  • 2
  • 8
  • 24

2 Answers2

3

A transaction is atomic by definition. But when a transaction's changes become visible to other users / connections / transactions depends on the isolation level. The default isolation in SQL Server is READ COMMITTED - see this question's answer for more info and links on how to change it.

For this type of scenario, you probably want SERIALIZABLE. The good news is that you can change the isolation level for a transaction with a SET TRANSACTION ISOLATION LEVEL statement in your stored proc. The bad news is that you have to be 100% sure that this is the only place in your code that ever updates the SEAT table.

Fundamentally, the issue you have is that there is a race condition. Just because you are in a transaction does not mean that two transactions can't both call the stored proc at the same time, then run the SELECT. Now both tx think it's ok to to do the UPDATE. Setting the isolation level to SERIALIZABLE locks the table for the tx that hits the SELECT first.

Community
  • 1
  • 1
AngerClown
  • 6,149
  • 1
  • 25
  • 28
  • I forgot about `SELECT .. FOR UPDATE`. That could possibly work too depending on if the DB supports row level locking. Here's some info for Postgres - http://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/ – AngerClown Jul 30 '14 at 20:33
1

Instead of the SELECT statement, why don't you just do the update, with an extra filter on NULL so it can't replace if the value is null, and then return whether the query had any effect or not. That way, the transaction is atomic, since it's just one query.

PROC GRABSEAT @seatid INT, @userid INT, @obtained BIT OUTPUT
BEGIN
    UPDATE SEATS SET UserID = @userid, ResvTime = GETDATE() 
    WHERE (SeatID = @seatid) AND UserID IS NULL
    SET @obtained = @@ROWCOUNT
END

Due to rowlocking, two updates can't happen simultaneously, so one will work (return @@ROWCOUNT = 1, and the other will fail @@ROWCOUNT = 0.

serakfalcon
  • 3,501
  • 1
  • 22
  • 33
  • This is an excellent suggestion. However, it /removes/ the situation I was trying to create with my contrived example. (Sorry, it is impossible to read the mind of the question-asker.) How to fully isolate a TRANSACTION (it turns out), is what I was seeking to find out. Now that I see the answer AngerClown gave, and how simple it is, I think I could have saved a lot of breath in asking my question. – Mark Goldfain Jul 21 '14 at 22:47
  • I guess my point is, if you're using a `SELECT` to get information to be used for an `UPDATE`/`INSERT` etc. you should consider re-writing the query so you don't need the `SELECT` in the first place, saving the need for a transaction. However, in a complicated use-case (say the user is only added to the DB if there is a seat for him) then AngerClown's answer makes more sense. – serakfalcon Jul 22 '14 at 06:38