What stored procedure concurrency problems arise in SQL Server 2005?
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 simultan
Related Questions
- When I create or edit a stored procedure or trigger through SQL Server Enterprise Manager, pressing the TAB key changes the context to each button. How can I indent my Transact-SQL statements?
- How do I get the return value from a stored procedure in a Microsoft SQL Server database?
- What stored procedure concurrency problems arise in SQL Server 2005?
