QUESTION POSED ON: 26 July 2006
I'm having a concurrency problem in SQL Server 2005. There are a number of free seats on the bus that I sell tickets to. Before inserting a sold ticket I need to check whether there are any free seats left. My stored procedure does something like this:
CREATE PROCEDURE add_ticket
-- parameters
DECLARE free_seats int
BEGIN TRANSACTION
SELECT free_seats = COUNT(*) FROM tickets WHERE seat_is_not_taken IF free_seats <> 0 INSERT INTO tickets VALUES(...)
-- some other statements
END TRANSACTION
The problem is that two processes can read the amount of free tickets concurrently and both save a ticket, even if there are no free seats left.
I need a way to block processes from reading the amount of free tickets while other processes running the add_ticket procedure have not yet inserted a new ticket.
SET TRANSACTION ISOLATION LEVEL does not help in this situation, am I right?
|
