I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.
I have 3 tables (simplified for this question).
Table 1- CourseSize table- this determines the maximum number of participants able to book into the session.
Table 2- Session- the course to be provided, the date/time and a reference to CourseSize record.
Table 3- Booking table- the person who made the booking with reference to the relevant Session record.
The SQL is as follows-
CREATE TABLE [dbo].[CourseSize] ( [ID] [int] IDENTITY(1,1) NOT NULL, [CourseSizeMax] [tinyint] NOT NULL, CONSTRAINT [PK_CourseSize] PRIMARY KEY CLUSTERED ([ID] ASC) ); CREATE TABLE [dbo].[Session] ( [ID] [int] IDENTITY(1,1) NOT NULL, [CourseSizeID] [int] NOT NULL, CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED ([ID] ASC) ); GO ALTER TABLE [dbo].[Session] WITH CHECK ADD CONSTRAINT [FK_Session_CourseSize] FOREIGN KEY([CourseSizeID]) REFERENCES [dbo].[CourseSize] ([ID]); GO ALTER TABLE [dbo].[Session] CHECK CONSTRAINT [FK_Session_CourseSize]; GO CREATE TABLE [dbo].[Booking] ( [ID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NOT NULL, CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([ID] ASC) ); GO ALTER TABLE [dbo].[Booking] WITH CHECK ADD CONSTRAINT [FK_Booking_Session] FOREIGN KEY([SessionID]) REFERENCES [dbo].[Session] ([ID]); GO ALTER TABLE [dbo].[Booking] CHECK CONSTRAINT [FK_Booking_Session]; GO
Problem- I need to ensure that inserts into the Booking table are stopped if the number of bookings against a session has reached the maximum course size. In the past I have used something like this:
INSERT INTO Booking SELECT 1 AS SessionID WHERE ( SELECT COUNT(*) FROM Booking WHERE SessionID = 1 ) <= ( SELECT CourseSizeMax FROM CourseSize INNER JOIN Session ON CourseSize.ID = Session.CourseSizeID WHERE Session.ID = 1 );
This fails when multiple users make bookings at the same time.
I tested the above query by setting the CourseSizeMax to 2, and using
WAITFOR time. I scheduled the query to run 3 times; 2 at the same time and the last 1 ms before the other two. They all were able to insert a row, thereby exceeding the session size limit.
I would really like to avoid using a trigger to handle this; my preference is to use constraints. I have total control of the design of the database so there is no problem with changing the structure if need be.
How do I prevent simultaneous updates from breaking the course size limitation?
transactionwith exclusive lock is another possible solution.