CODEWITHSUNDEEP
sql-server-2008stored-procedures
Hoorayo
Hoorayo

Reputation: 615

SET TRANSACTION ISOLATION LEVEL Scope on stored procedure

I am creating a stored procedure and I do not want any locking through out the script. I am sure that I need to state "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" in the beginning of the script. But I am not sure how to start after the statement. One of the example from Microsoft show that start with "BEGIN TRANSACTION" and end with "END TRANSACTION". There is someone who starts without any BEGIN and END statement after "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". Please, suggest me for the right way of using "SET TRANSACTION ISOLATION LEVEL" statement to achieve my goal.

http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION

SQL Server: how to set default isolation level for the entire stored procedure?

BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 -- will a transaction level for a atomic transaction created by SQL Server for this statement be READ COMMITTED 
SELECT * FROM T
END

Upvotes: 1

Views: 8960

Answers (1)

makison
makison

Reputation: 383

Both statements are ok

Transaction scope in a stored procedure, from MSDN

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Upvotes: 1

Related Questions