January 27, 2014 at 3:22 am
Hi All
I'm making a change of the database properties, so enabling ALLOW_SNAPSHOT_ISOLATION to ON. I'm doing this only once, i.e. I'm checking for the property snapshot_isolation_state in sys.databases.
Than within a SP I'm executing the following command:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
The SP executes for 5-10 minutes. I'm changing the isolation level to avoid locks in the database.
During and after execution of the sp I'm checking the isolation level with DBCC USEROPTIONS, and as expected not changed.
A colleague says that this is risky to do on prod environment. Once he had had issues with it, so he had needed to restart the system.
I think this is just a normal activity even without a risk. What do you think, is there any risk?
Can you share any experience, or suggest something?
Thanks in advance.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
January 27, 2014 at 4:54 am
IgorMi (1/27/2014)
Hi AllI'm making a change of the database properties, so enabling ALLOW_SNAPSHOT_ISOLATION to ON. I'm doing this only once, i.e. I'm checking for the property snapshot_isolation_state in sys.databases.
Than within a SP I'm executing the following command:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
The SP executes for 5-10 minutes. I'm changing the isolation level to avoid locks in the database.
During and after execution of the sp I'm checking the isolation level with DBCC USEROPTIONS, and as expected not changed.
A colleague says that this is risky to do on prod environment. Once he had had issues with it, so he had needed to restart the system.
I think this is just a normal activity even without a risk. What do you think, is there any risk?
Can you share any experience, or suggest something?
Thanks in advance.
Regards,
IgorMi
SQL Server Books Online details the following
SQL Server Books Online
When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.
It is possible the option hadnt been changed due to open connections!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2014 at 8:10 am
If you are doing connection pooling, the API stored proc sp_reset_connection does not reset the transaction isolation level, so it is important to make sure you reset the transaction isolation level back to the default when your stored procedure completes.
I usually don't use snapshot isolation. Instead, I set the database to read_commited_snapshot. You should be aware of the differences before you make that change.
February 8, 2014 at 2:56 pm
Hi Both,
I'd like to share with you the experience I got regarding this question. Maybe I didn't present it well. The idea was to use versioning only for the session from which the SP was run.
I understood it as: have it set "Allow snapshot isolation", and use it if you need from a stored procedure (SET TRANSACTION ISOLATION LEVEL SNAPSHOT), but only within the scope of the SP.
However it's now surpassed.
Setting the aforementioned setting (ALLOW SNAPSHOT ISOLATION to ON) for a database is making all update operations to take effect. I was thinking that (1) ALLOW SNAPSHOT ISOLATION level is a precondition for executing (2) SET TRANSACTION ISOLATION LEVEL SNAPSHOT for a database.
Just setting to ON the ALLOW SNAPSHOT ISOLATION setting is enough to start versioning and cause overloads to the tempdb, without even setting (2).
I still don't know what is then the difference between setting (1) only, and setting (1) and (2).
In the reference http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx you'll see this
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
and a bit further you'll meet this "You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it." which is a precondition for enabling READ_COMMINTED_SNAPSHOT. This is OK.
According to our tests, it seems that only the first ALTER-statement is enough.
Thanks again.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 8, 2014 at 4:56 pm
Igor Micev (2/8/2014)
The idea was to use versioning only for the session from which the SP was run.
In that case you need
Alter database mydb set read_committed_snapshot on
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply