SHARE
TWEET

Untitled

SQLSoldier Jun 20th, 2015 238 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Create test database
  2. Create Database TestIsoLevel
  3. Go
  4.  
  5. -- Switch to test database
  6. Use TestIsoLevel;
  7. Go
  8.  
  9. -- Creat test table with data
  10. Select *
  11. Into dbo.AllDBs
  12. From sys.databases
  13. Where database_id <= 4;
  14. Go
  15.  
  16. -- Create view to select from table and return lock info
  17. Create View dbo.TranLocks
  18. As
  19. Select Distinct TL.*
  20. From dbo.AllDBs A, sys.dm_tran_locks TL
  21. Where request_session_id = @@Spid
  22. And TL.resource_associated_entity_id = object_id('dbo.AllDBs');
  23. Go
  24.  
  25. -- Test in read committed isolation (default, but let's be explicit)
  26. Set Transaction Isolation Level Read Committed;
  27.  
  28. Select *
  29. From dbo.TranLocks;
  30. Go
  31.  
  32. -- Test in read uncommitted (equivalent to nolock) should get a S-sch lock instead
  33. Set Transaction Isolation Level Read Uncommitted;
  34.  
  35. Select *
  36. From dbo.TranLocks;
  37. Go
  38.  
  39. -- Prove that justing running the same query (no view) using nolock gives
  40. -- same locks as Read Uncommitted on the view
  41. Set Transaction Isolation Level Read Committed;
  42.  
  43. Select Distinct TL.*
  44. From dbo.AllDBs A with(nolock), sys.dm_tran_locks TL
  45. Where request_session_id = @@Spid
  46. And TL.resource_associated_entity_id = object_id('dbo.AllDBs');
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top