RichardHowells posted on June 29, 2026 13:46
This solves the situation of a link table (Bookings) that links CourseEvent and Student.
Problem - There are a limited number of places available so we need to prevent inserting more than '@MaxBookingsAllowed' rows into the bookings table for a specific IdCourseEvent
In SQL Server I suggest locking hints. Other databases have similar functionality. My scenario is booking students onto course events...
begin transaction
declare @currentBookings int = (select count(*) from bookings with (serializable, updlock) where IdCourseEvent = @IdCourseEvent)
if @currentBookings < @MaxBookingsAllowed
begin
insert bookings(idStudent, IdCourseEvent) values(@IdStudent, @IdCourseEvent)
set @RowsAffected = @@rowcount
end
commit transaction
The updlock hint instructs SQL Server to take locks as if updating the rows. That blocks other transactions from reading the same rows as this one. Without it two of these can deadlock against each other. T1 reads and discovers it's safe to insert. T2 also reads and finds it safe to insert. T1 (say) now tries to insert, but can't because of the locks T2 holds. T2 dsicovers the same
The serializable hint instructs SQL Server to take range locks that prevent another transaction inserting rows that would qualify for the where clause. It also causes any read locks to extend to the end of the transaction. (I suspect that serializable might not be strictly required here, but it gives me confidence!)