/*============================================================================
  File:     IsolationLevels.sql

  Summary:  This script shows the database level settings that can be changed
			to use a variety of different isolation settings, their combinations
			and their effects on data access.

  Date:     Mar 2006

  SQL Server Version: 9.00.1399.06 (RTM)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE AdventureWorks
go

------------------------------------------------------------------------------
-- DEFAULT Behavior - no changes to database
-- Verify that NONE of the snapshot options have been turned on
------------------------------------------------------------------------------

SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go

-- From this window execute the following partial transaction:
BEGIN TRANSACTION
UPDATE Person.Contact 
	SET FirstName = 'Modified' WHERE ContactID = 3
SELECT * FROM Person.Contact WHERE ContactID = 3

-- Go to a second window and see what the behavior is for 
-- READ UNCOMMITTED and READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

-- Now rollback the transaction and see what happens?
ROLLBACK 

-- Switch to the other window - the "Modified" change was rolled back 
-- and now we have the original value... 

-- Increasing Isolation guarantees that data is repeatably read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
go
BEGIN TRAN
SELECT * FROM Person.Contact WHERE ContactID = 3
go

-- Now go back and see if you can modify (in another window) that same row
-- The modification will wait.

-- Try another reader? Reader waits on writer waiting on reader... why? 
-- to prevent lock starvation...
-- Locks are handled in first come, first served fashion and first incompatible
-- lock waits, all others are blocked.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go


------------------------------------------------------------------------------
-- Enable Read Committed using Statement-Level Snapshot
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK AFTER 5

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go

-- From this window execute the following partial transaction:
BEGIN TRANSACTION
UPDATE Person.Contact 
	SET FirstName = 'Modified' WHERE ContactID = 3
SELECT * FROM Person.Contact WHERE ContactID = 3

-- Go to a second window and see what the behavior is for 
-- READ UNCOMMITTED and READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

BEGIN TRAN
SELECT * FROM Person.Contact WHERE ContactID = 3
-- come back to this window to commit
-- COMMIT TRAN

SELECT * FROM Person.Contact WHERE ContactID = 3
COMMIT TRAN
go

-- Get the data back to original value = 'Kim'
UPDATE Person.Contact 
	SET FirstName = 'Kim' WHERE ContactID = 3

------------------------------------------------------------------------------
-- DISABLE RSCI - Statement-level Read Committed with Row Versioning
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK AFTER 5
go

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go

------------------------------------------------------------------------------
-- Enable Snapshot Isolation - Transaction-level Consistency
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
go

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go


-- From this window execute the following partial transaction:
BEGIN TRANSACTION
UPDATE Person.Contact 
	SET FirstName = 'Modified' WHERE ContactID = 3
SELECT * FROM Person.Contact WHERE ContactID = 3

-- Go to a second window and see what the behavior is for 
-- READ UNCOMMITTED and READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

BEGIN TRAN
SELECT * FROM Person.Contact WHERE ContactID = 3
-- come back to this window to commit
-- COMMIT TRAN

SELECT * FROM Person.Contact WHERE ContactID = 3
COMMIT TRAN
go

-- We need to ask for SNAPSHOT ISOLATION
--go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
SELECT * FROM Person.Contact WHERE ContactID = 3
go

-- Get the data back to original value = 'Kim'
UPDATE Person.Contact 
	SET FirstName = 'Kim' WHERE ContactID = 3
go

------------------------------------------------------------------------------
-- DISABLE Snapshot Isolation - Transaction-level Consistency
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION OFF
go

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go

------------------------------------------------------------------------------
-- Enable BOTH Read Committed with Statement-level Snapshot AND Snapshot Isolation
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK AFTER 5
go

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
go

-- Get statement level automatically
-- Get transaction level when you want it.

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go

------------------------------------------------------------------------------
-- DISABLE Snapshot Isolation - Transaction-level Consistency
------------------------------------------------------------------------------

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK AFTER 5
go

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION OFF
go

-- Verify setting
SELECT sd.[name] AS [Database Name]
	, sd.is_read_committed_snapshot_on AS [Read Committed with Statement-level Snapshot Enabled]
	, snapshot_isolation_state AS [Snaphot Isolation Enabled]
FROM sys.databases AS sd
WHERE sd.[name] = 'AdventureWorks'
go