store procedure ==> race condition?

[xusword]

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?

[hspc]

ًWhat you described is a valid scenario
you are afraid of dirty or fantom reads.

you can use this at the top of your procedure:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

as BOL describes:
SERIALIZABLE Specifies that:


Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Which is what you need. But note that this comes with a performance cost.

[KrisSimonis]

Another way would be to just use BEGIN TRANSACTION and COMMIT TRANSACTION in your procedure at the critical INSERT command.
Like this:

proc

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

perform some logic, and for each applicable pairs, do
BEGIN TRANSACTION
IF (SELECT COUNT(*) FROM Student_Teacher WHERE StudentID = sid AND TeacherId = tid ) = 0
insert into student_teacher (studentId, teacherId) values (sid, tid)
COMMIT TRANSACTION

end proc

[hspc]

Another way would be to just use BEGIN TRANSACTION and COMMIT TRANSACTION in your procedure at the critical INSERT command.

Actually both are required
BEGIN TRANSACTION and COMMIT TRANSACTION define transaction boundaries. while SET TRANSACTION ISOLATION LEVEL define the isolation level for this transaction.