I have a database where the users booking classes.

There is a table Bookings where lets say we want to have ony 5 rows for 5 students.

When the student trying to book the class, i am checking first how many rows are in the table and if are less than 5, i do the Insert.

The problem is that when there are concurrent bookings in the same second of the time, i have more than 5 records in the table.

In every Insert i check first the number of the rows, but when are in the same time, the return number is the same and its not increasing.

How to avoid these concurrent inserts and keep the table rows to 5.

CodePudding user response：

This sounds like the job for a TRIGGER!

create trigger LimitTable
on YourTableToLimit
after insert
as
    declare @tableCount int
    select @tableCount = Count(*)
    from YourTableToLimit

    if @tableCount > 5
    begin
        rollback
    end
go

To be more clear, and you probably already know this... inserts are never concurrent. The concurrency happens from the calling code.

I might suggest that you have the wrong data structure if you need something like this though. I personally dislike relying on triggers like this.

Without knowing the full use case, it'd be hard to really offer a full solution though.

CodePudding user response：

1. you can use unique constraint on (student_id, class_id, seat_number) and set seat_number as 1,2,3,4,5 (result from count function). But on delete you must update seat numbers for all bookings in class.
2. you can use queue to prevent concurrent inserts