October 26, 2009 at 5:08 am
In our DB, we have isolation level set to default : read committed. Now one of the person in team has set it to one of the procs as "repeatable read". Please let me know the following:-
1: Does this setting is for the proc duration? After the procs execution, will the same set back to default level.
2: How to know what is the current transaction isolation level in DB.
cheers
Siddarth
October 26, 2009 at 5:22 am
Use DBCC USEROPTIONS to know the database isolation level
October 26, 2009 at 7:29 am
siddartha pal (10/26/2009)
1: Does this setting is for the proc duration? After the procs execution, will the same set back to default level.
AS PER BOL,
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.
For more info,
http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx
---------------------------------------------------------------------------------
October 26, 2009 at 7:37 am
thanks alot for the info.
cheers
Siddarth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply