Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Create test database
- Create Database TestIsoLevel
- Go
- -- Switch to test database
- Use TestIsoLevel;
- Go
- -- Creat test table with data
- Select *
- Into dbo.AllDBs
- From sys.databases
- Where database_id <= 4;
- Go
- -- Create view to select from table and return lock info
- Create View dbo.TranLocks
- As
- Select Distinct TL.*
- From dbo.AllDBs A, sys.dm_tran_locks TL
- Where request_session_id = @@Spid
- And TL.resource_associated_entity_id = object_id('dbo.AllDBs');
- Go
- -- Test in read committed isolation (default, but let's be explicit)
- Set Transaction Isolation Level Read Committed;
- Select *
- From dbo.TranLocks;
- Go
- -- Test in read uncommitted (equivalent to nolock) should get a S-sch lock instead
- Set Transaction Isolation Level Read Uncommitted;
- Select *
- From dbo.TranLocks;
- Go
- -- Prove that justing running the same query (no view) using nolock gives
- -- same locks as Read Uncommitted on the view
- Set Transaction Isolation Level Read Committed;
- Select Distinct TL.*
- From dbo.AllDBs A with(nolock), sys.dm_tran_locks TL
- Where request_session_id = @@Spid
- And TL.resource_associated_entity_id = object_id('dbo.AllDBs');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement