Hi

I have encountered a problem and it's assumed that there is a race condition happened.

Someone proposed, while my method is running, someone else executed some query and altered the table my stored procedure is supposed to modifiy and causes some trouble.

My questions: Is it even possible that the stored procedure be interrupt in the middle of an execution? If so, how would I solve this problem?

My code goes something like this

proc

look up student id, sid
look up teacher id, tid

perform some logic, and for each applicable pairs, do
insert into student_teacher (studentId, teacherId) values (sid, tid)

end proc

but, say someone else can also insert into student_teacher table through, UI for example. There is a chance they can insert a (sid, tid) pair my procedure will eventually get to, but not yet gone over. The key constraint will be violated when my table get to this (sid, tid) but I dont want that to happen

I want to do a select statement to check if this (sid, tid) pair exists right before inserting, but this doesnt really solve the race condition isn't it.

People can still insert the data right between my checking and inserting

How can I solve this?