Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure concurrency problems in SQL Server 2005

Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the stored procedure or even employing SQL Server Broker.

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?

You are correct; a higher isolation level would not help ensure that multiple readers did not read the same rows simultaneously. However, there are several ways you could make this work. For instance, you could assign each seat a unique identifier (meaning, a unique key – not necessarily a GUID) and create a table for seats that have already been taken. Put a UNIQUE constraint on the table and you will be guaranteed that no seat is inserted twice.

That said, I think a more interesting option might be to employ SQL Service Broker. You could set up a conversation for each bus, and store the conversation handles in a table that can be referenced by readers before doing the RECEIVE. That way, the readers can filter appropriately. Drop a message into the queue for each seat on the bus. The readers can then simply RECEIVE the messages as needed (in the process, reserving seats on the bus). Service Broker will ensure that no message is received twice, meaning that you will no longer have any concurrency problems.

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.