Is (insert into ... select) statement safe about race conditions - SQL Server?

Question

i have an online hotel booking system which allows multiple users to make bookings almost simultaneously.

When a user requests for free rooms, the system performs a query to get the free rooms and then shows it in a some way ...

Now when user clicks on the "book" button, the free rooms are sent to the server to be booked, i am using a very similar query to this (this is simplified):

Insert into Bookings(room_id) select available_room_id from
dbo.GetAvailableRooms(@StartDate,@EndDate) where available_room_id = @ToBeBookedRoomID;

where " GetAvailableRooms " is a UDF.

what i am hoping to do by this query is to test for the latest time if room is available or not, but i am worried if this is exposed to race conditions ? i am thinking about the case that some user has booked that room in the time between the insert and select operations ... would "insert into .. select" guarantees no race conditions ?

Updated: This is the function sql query :

select AllDays.day_date,alloted_room_id as available_room_id,pl.room_number from HotelsContractsAllotments hl
inner join PlansRooms pl on pl.room_id = hl.alloted_room_id
inner join HotelsContracts hc on hc.contract_id = hl.allotment_contract_id
inner join Hotels h on h.hotel_id = hc.contract_hotel_id
inner join AllDays on day_date >= hc.contract_starting_date and day_date <= hc.contract_end_date
left join BookingsRooms br on day_date >= br.booking_starting_date and  day_date <= br.booking_end_date and br.booking_room_id = alloted_room_id
where br.booking_room_id is null and day_date >= @StartingDate and day_date <= @EndDate

Thanks.

Without seeing the code from `dbo.GetAvailableRooms`, I would expect this to be exposed to race conditions.. — Gordon Linoff, Nov 27 '15 at 20:02
Sorry, i have just updated it, could you see it please ? @GordonLinoff — AymAn AbuOmar, Nov 27 '15 at 20:16
I would suggest using a transaction. Also see this: http://stackoverflow.com/questions/1071286/does-sql-server-wrap-select-insert-queries-into-an-implicit-transaction — thepirat000, Nov 27 '15 at 20:46

score 2 · Accepted Answer · edited May 23 '17 at 12:15

Look at the locking on the table(s), and hints used. Based on the information provided I expect this to be open to race conditions (i.e. a lock is not maintained on the table). You can look at setting-up locks with SQL hits, or transactions. There are a few posts out there that discuss locking. An example of one that looks pertinent to your question is: SQL Server Table Lock during bulk insert

Without a continuous lock on the table(s) affected from the SELECT through the INSERT, yes you are open to a race condition.

NOTE - locking the table for extended periods will impact performance to varying degrees depending on the volume of traffic, and time locked (if a lot of users are trying to book at the same time, 1sec difference can become significant). Therefore, if you do lock the table, I recommend spending some time tuning your queries for performance. I wont say much on this as I do not have any metrics on your data, or know your indexes. Probably best to start by simply re-structuring your queries to hit the most restrictive table first. I assume this would be the table holding alloted_room_id (pass the ID so it can be used in the WHERE clause).

It might be worthwhile to look at the models used for things like online shopping carts and how they handle allocations of 'reserved' resources that expire if the user does nothing. — mikurski, Nov 28 '15 at 01:51

Is (insert into ... select) statement safe about race conditions - SQL Server?

1 Answers1