THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Don't swap horses in midstream.

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 
            
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.

 

Published Monday, February 01, 2010 2:07 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Alex, thanks for this tip.  The workaround is obvious but I have to admit the scenario did not dawn on me prior to your post.

February 1, 2010 2:27 PM
 

Alex K said:

Aaron,

You are welcome.

BTW, we are going to be quite busy. Looks like we need to change a lot of modules - all our readers use snapshot.

February 1, 2010 9:14 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement