1083
Chapter 47: Managing Transactions, Locking, and Blocking
47
Best Practice
Oracle’s default transaction behavior is just like snapshot isolation, which is why some DBAs moving
up to SQL Server like snapshot isolation, and why some assume snapshot isolation must be better
somehow than traditional transaction isolation levels.It’s true that snapshot isolation can eliminate some locking and blocking issues and would therefore
improve performance given the right hardware. However, and the best practice is this point, if you
choose snapshot isolation, it should be an architecture issue, not a performance issue. If another
transaction is updating the data, should the user wait for the new data, or should the user see the
before image of the data? For many applications, returning the before image would paint a false
picture.Enabling Row Versioning
Snapshot actually leverages SQL Server’s row versioning technology, which copies any
row being updated into TempDB. Confi guring snapshot isolation therefore require fi rst
enabling row versioning for the database. In addition to the TempDB load, row versioning
also adds a 14-byte row identifi er to each row. This extra data is added to the row when
the row is modifi ed if it hasn’t been done previously. It is used to store the pointer to the
versioned row.Snapshot isolation uses row versioning, which writes copies of the rows to TempDB. This increases the load on
TempDB. Use care enabling this feature if your tempdb is already stressed.Row versioning alters the row structure so that a copy of the row can be sent to TempDB.The following code enables snapshot isolation. To alter the database and turn on snapshot
isolation, there can be no other connections to the database:USE AdventureWorks2012
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON
-- check snapshot isolation
select name,
snapshot_isolation_state,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
from sys.databases
where database_id = DB_ID()c47.indd 1083c47.indd 1083 7/31/2012 10:23:32 AM7/31/2012 10:23:32 AM
http://www.it-ebooks.info