![]() Use ALTER DATABASE to allow snapshot isolation. It appears that READ_COMMITTED row versioning is in place.Īdding SET TRANSACTION ISOLATION LEVEL SNAPSHOT on query 1 and query 2, and run query 1 or query 2 returns error - Snapshot isolation transaction failed accessing database 'TEST' because snapshot isolation is not allowed in this database. DMV shows query 1 incur exclusive lock, but query 2 returns details with 'Original' without query 1 commit the transaction. Second test, rollback previous transaction, set READ_COMMITTED_SNAPSHOT ON but leave ALLOW_SNAPSHOT_ISOLATION OFF. OBJECT_NAME(resource_associated_entity_id) AS tbl_name, USE TEST ĭB_NAME(tl.resource_database_id) AS DBName, In this test, query 2 is waiting for query 1 to commit, dm_tran_locks DMV shows that exclusive lock on TABLE1 incurred by query 1. CREATE DATABASE TEST įirst test with both settings confirmed to be OFF. It shows examples of how a update statement and a select statement might return totally different and unexpected results. Good article on how snapshot isolation can change the expected behavior of your app. ![]() I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.Ī quick look at performance overhead using Snapshot Isolation. MS SQL Server automatically applies snapshot isolation for that table. If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. ![]() If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits. Fast forward to 41:45 in the video to get to the part where she answers your question. Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation ( direct MP4 download link (544MB)).
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |