Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Setting Isolation Level in stored procedure

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-07-18 : 11:05:08
Hello all,
in some stored procedure (written by someone else),
I've found these settings at the beginning:

SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Is a good practice to set the Isolation Level to "Read Committed"?
Is not already the default setting for SQL Server 2008?

Is a good idea to put also the Xact_Abort ON?

Let me know.

Thanks a lot.

Luis

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-18 : 13:10:52
Read committed is the default. What isolation level is the database using? What you need in the stored procedure is dependent upon its needs and what the database is set to.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-07-18 : 13:37:57
Read Committed is the default default so to speak. You can change a Database's Default to Snapshot Isolation. So that maybe one reason, they want to override this. Also the Transaction Isolation Level is for a session so if it was set to something else earlier in the session, this statement would have an effect - changing it to READ COMMITTED.

Not sure about the XACT_ABORT ON in Stored Procedures, we don't do this but then again we are only starting to get into the TRY/CATCH.
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-07-19 : 03:40:15
Where I can find the Isolation Level of my DB? Under Properties -> Options I've not found it.
Maybe, I think, these statements has been added to guarantee the atomicity of the operations.

L
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-19 : 13:29:30
select
CASE is_read_committed_snapshot_on
WHEN 1 THEN 'Read Committed Snapshot'
ELSE 'Read Committed'
END as TransactionIsolationLevel
from sys.databases
where database_id = DB_ID()

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -