SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Setting Isolation Level in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 07/18/2013 :  11:05:08  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 07/18/2013 :  13:10:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Canada
594 Posts

Posted - 07/18/2013 :  13:37:57  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 07/19/2013 :  03:40:15  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 07/19/2013 :  13:29:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000