April 29, 2008 at 8:31 am
HI All,
I have been looking into the snapshot isolation mode for one of my archiving queries that runs overnight and have a question regarding setting the isolation level.
To be able to use the read committed snapshot i have to use set transaction isolation level SNAPSHOT in my database. However, i first have to run alter database set read_committed_snapshot ON. My question is whether or not this then uses row versioning locks for EVERY transaction (unless explicitily coded otherwise) or just for the ones where you specify set transaction isolation level SNAPSHOT
From BOL i have the following extract, which seems to indicate that every transaction will use row versioning.
The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
If im right in my thoughts, then this will mean that every transaction will effectively hold a copy of the data "as is" in the tempdb database and this will surely hamper performance significantly.
I hope im just reading the BOL statements wrong and the snapshot is only uses for those transactions where i explicitly set it.
April 29, 2008 at 9:02 am
It will row version everything. It needs to keep the original copy of anything being used by any transaction because the database engine does not know if you will come long and run another transaction in snapshot isolation mode that needs to look at the previous committed version of a record. So, that previous version needs to be held until the end of the transaction.
I hope that made sense.
There is overhead for this, but you may be surprised at how low it is. You will need to test performance, but unless you have lots of very long transactions the overhead is not bad.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply