Sql-server – Maintaining referential integrity in a booking system

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?