Happy Programming

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 5 - Transactions, Locking and Blocking
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Dirty Read Transactional Fault

-- Transaction 1
USE CHA2

go
BEGIN TRANSACTION
UPDATE Customer
SET Nickname = 'Transaction Fault'
WHERE CustomerID = 1

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
USE CHA2
SELECT NickName
FROM Customer
WHERE CustomerID = 1

-- Transaction 1
COMMIT TRANSACTION


go

-----------------------------------------------------------
-- Non-Repeatable Read Transactional Fault

-- Transaction 1
USE CHA2

UPDATE Customer
SET Nickname = 'Non-Repeatable Read'
WHERE CustomerID = 1

-- Transaction 2
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION
SELECT NickName
FROM Customer
WHERE CustomerID = 1

COMMIT TRANSACTION





-----------------------------------------------------------
-- Phantom Row Transactional Fault

-- Transaction 1
USE CHA2;

UPDATE Customer
SET NickName = 'Missy'
WHERE CustomerID = 3;


-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

USE CHA2;

BEGIN TRANSACTION;

SELECT CustomerID, FirstName, LastName, Nickname
FROM Customer
WHERE NickName = 'Missy';

COMMIT TRANSACTION;




-----------------------------------------------------------
-- Read Committed - Default Behavior

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;








-------------------------------------------------
-- Controlling SQL Server Locking

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DBCC USEROPTIONS


--Locking Hints

USE OBXKites
UPDATE Product WITH (RowLock)
SET ProductName = ProductName + ' Updated'

EXEC sp_help ProductCategory

EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowRowlocks', FALSE
EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowPagelocks', FALSE





--------------------------------------------------
-- DeadLocks!

--Transaction 1
-- Step 1
USE OBXKites
BEGIN TRAN;
UPDATE Contact
SET LastName = 'Jorgenson2'
WHERE ContactCode = '101'

--Transaction 2
--Step 2
USE OBXKites
BEGIN TRAN
UPDATE Product
SET ProductName
= 'DeadLock Repair Kit'
WHERE Code = '1001'
UPDATE Contact
SET FirstName = 'Neals'
WHERE ContactCode = '101'
COMMIT

-- Step 3
UPDATE Product
SET ProductName = 'DeadLock Identification Tester'
WHERE Code = '1001'

COMMIT


---------------------------------------------
-- Deadlock with error handling

Use OBXKites
TranStart:

Begin Tran

Update Contact
Set LastName = '2Jorgenson2'
Where ContactCode = '101'

WaitFor Delay '00:00:05'

Update Product
Set ProductName = '2DeadLock Identification Tester'
Where ProductCode = '1001'
IF @@ERROR = 1205
BEGIN
PRINT 'Deadlock'
GOTO TranStart
END

COMMIT



----------------------------------------------------------------------------
-- Using Snapshot Isolation

USE Aesop;
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON

BEGIN TRAN
UPDATE Fable
SET Title = 'Rocking with Snapshots test'
WHERE FableID = 2;

SELECT Title FROM FABLE WHERE FableID = 2

COMMIT TRAN
---Transaction 2
USE Aesop;

SELECT Title
FROM FABLE
WHERE FableID = 2