SQL Server 2005 version introduced the concept of Snapshot isolation setting. It is one of those hidden gems inside SQL Server which is under appreciated. Snapshot is a great option for increasing the concurrency of application at the same time giving application better consistency of data by not returning dirty reads. In this blog post, we will try to talk about two interesting things to know when it comes to using Snapshot Isolation (SI).
As a DBA administering SQL Server with Snapshot isolation enabled on the server, it is critical to know these tips. The more you play around with Snapshot Isolation, more you will discover these internals.
Track your SQL Server Error Logs
Error and informational messages related to versioning based isolation are written to SQL Server error log. The versioning feature writes error messages to the SQL Server Errorlog when it encounters conditions that will disrupt transactions that use versioning. DBA’s should review the error log and the Windows Application Event Log on a regular basis to monitor the versioning feature for errors.
Informational messages like the following can also be found in the error log. A sample of two of the informational messages that are reported related to versioning are produced each time a database is enabled or disabled for either of the versioning based isolation levels:
2014-11-29 22:25:04.98 spid52 Setting database option READ_COMMITTED_SNAPSHOT to ON for database SnapshotDB.
2014-11-29 22:26:07.53 spid52 Starting up database 'SnapshotDB'.
2014-11-29 22:28:10.70 spid52 Setting database option READ_COMMITTED_SNAPSHOT to OFF for database SnapshotDB.
2014-11-29 22:28:10.81 spid52 Starting up database 'SnapshotDB'.
Note that this message is not specific to versioning, anytime any database option is changed the new setting is reported in the SQL Server error log.
Too many update conflicts under Snapshot Isolation
Transactions running under Snapshot Isolation automatically detect conflict detection and there is no way to turn it off. When the update conflict occurs, the Snapshot Isolation transaction fails with the following error:
Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tbl_conflict' directly or indirectly in database 'SnapshotDB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
It is important to understand the possible causes and some suggestion on how to minimize the update conflicts in your environment:
- We possibly are having a long running Snapshot Isolation transaction. The longer the duration, the higher the chance that a concurrent update will cause the update conflict.
- Evaluate if the UPDATE that encountered the conflict need to run under a snapshot transaction? Remember that the UPDATE will take locks under a snapshot transaction just as it does with all of the other available isolation levels.
The two big benefits of snapshot transactions are:
-
-
No lost updates. This is probably the reason why they are running the UPDATE under a snapshot transaction in the first place.
-
No Shared lock will be taken for data being read.
-
We can minimize update conflict by acquiring UPDATE lock on the rows ahead inside the transaction scope. Additionally, we can also monitor update conflict using perfmon counters.
Conclusion
As discussed, keep looking for additional information when working with Snapshot Isolation. Evaluate on a case to case basis if you will need to use Snapshot Isolation for every transaction. There is no shortcuts on learning new behavior and Snapshot isolation is no different. Know the fineprint details before converting every other connection into a SI mode. As we wrap up, to know which databases are enabled with Snapshot Isolation – use the below DMV.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc, is_read_committed_snapshot_on
FROM sys.databases
Additional Resources that might interest you…
Learn more about Embarcadero DBArtisan, the premier cross-platform database administration tool, and try DBArtisan for free.