We can begin a transaction under snapshot isolation, but we cannot switch to it in the middle of an outstanding transaction. For example, the following procedure looks good and passes a smoke test:
CREATE PROCEDURE dbo.SelectCountry
@CountrySymbol CHAR(2)
AS
BEGIN ;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;
SELECT CountrySymbol ,
Description
FROM data.Countries
WHERE CountrySymbol = @CountrySymbol ;
END ;
GO
-- Smoke test: this call completes
EXECUTE dbo.SelectCountry @CountrySymbol = 'US' ;
However, the procedure fails if it is invoked in the middle of an outstanding transaction that did not begin under snapshot isolation level:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRANSACTION ;
EXECUTE dbo.SelectCountry @CountrySymbol = 'US' ;
COMMIT ;
Msg 3951, Level 16, State 1, Procedure SelectCountry, Line 6
Transaction failed in database 'Test' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.
Apparently the author of this stored procedure overlooked the possibility that it can be invoked in an outstanding transaction that did not begin under snapshot isolation level. Is is very easy to improve the robustness of this stored procedure:
ALTER PROCEDURE dbo.SelectCountry @CountrySymbol CHAR(2)
AS
BEGIN ;
IF @@TRANCOUNT = 0
BEGIN ;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;
END ;
SELECT CountrySymbol ,
Description
FROM data.Countries
WHERE CountrySymbol = @CountrySymbol ;
END ;
As usual in defensive programming, let us not stop at this - we also need to review all other modules and proactively apply the same fix wherever appropriate.