Hi, I'm wondering if this insert statement is safe in terms of multiple clients running the same query at the same time. The query inserts a random "PIN" number which, along with the ConsultantID, needs to be unique (there is also a unique index which covers both columns).
IF NOT EXISTS ( SELECT * FROM Appointments WHERE ConsultantID = @ConsultantID AND ApptPIN = @RandomApptPIN ) BEGIN INSERT INTO Appointments (ConsultantID, ApptPIN, ... etc) OUTPUT INSERTED.AppointmentID VALUES (@ConsultantID, @RandomApptPIN, ... etc) END ELSE SELECT 0 AS AppointmentID;
The query checks for the existence of a matching pair first, before doing the insert if a match isn't found. It will return zero (0) if the ConsultantID + PIN already exists. If it returns 0, then I generate a new random PIN and run it again, until it returns the successfully inserted AppointmentID.
My question is about thread safety. If several clients run this query simultaneously, could one thread sneak in the same values as another thread, in between the IF NOT EXISTS clause and the INSERT clause? Or does the query "block" other threads until both the IF NOT EXISTS and the INSERT are both evaluated?
ed: fixed typo in query