CODEWITHSUNDEEP
sqlsql-serverstored-procedurestriggerstransactions
user3621633
user3621633

Reputation: 1721

Isolation level for Select statements in transactions for the same SQL Server trigger/stored procedure?

This question is related to Is a stored procedure call inside a SQL Server trigger implictly thread safe and atomic? so I don't know if I should re-post the same code or not. Be that as it may, here's the deal.

As it stands, the SQL Server trigger is an INSTEAD OF INSERT for the moment. It inserts data into a table called Foo. Then the trigger calls a stored procedure. One part of the stored procedure selects the last record inserted into Foo:

-- New transaction in stored procedure
BEGIN TRANSACTION

    ...

    DECLARE @FooID INT

    SELECT 
        TOP 1 @FooID = ID
    FROM 
        Foo
    ORDER BY
        ID DESC

    ...

END TRANSACTION

Let's say two INSERT statements are executed at the same time (let's call the two INSERT transactions T1 and T2 for simplification). That's two simultaneous trigger calls. The trigger and stored procedure are both atomic in my case.

But do I need to worry about isolation for the SELECTstatement in the stored procedure? Is it guaranteed that the last record inserted will be correctly selected? Or, could I run into a situation where T1 selects the T2 record and vice-versa?

Thank you.

Upvotes: 1

Views: 1013

Answers (1)

WPrecht
WPrecht

Reputation: 1382

Isolation levels are well covered in the MSDN documentation: Transaction Isolation Levels and they most definitely can affect how the SPs operate. Also, as mentioned yesterday, the SP in the trigger may not see the insert that caused the trigger.

Upvotes: 1

Related Questions