ch 11 - transactional integrity.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter  11 - Transactional Integrity

-----------------------------------------------------------
-----------------------------------------------------------

--------------------------------------------
-- 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 2
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL 
  READ COMMITTED 
USE CHA2
SELECT NickName 
  FROM Customer
  WHERE CustomerID = 1

-- Transaction 1
USE CHA2
BEGIN TRANSACTION
  UPDATE Customer 
    SET Nickname = 'Transaction Fault'
    WHERE CustomerID = 1
COMMIT TRANSACTION

-- Transaction 2
USE CHA2
SELECT NickName 
  FROM Customer
  WHERE CustomerID = 1

COMMIT TRANSACTION


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

-- Transaction 2
BEGIN TRANSACTION
  USE CHA2
  SELECT CustomerID, LastName 
    FROM Customer
    WHERE NickName = 'Missy'

-- Transaction 1
USE CHA2
BEGIN TRANSACTION
  UPDATE Customer 
    SET NickName = 'Missy'
    WHERE CustomerID = 3
COMMIT TRANSACTION

-- Transaction 2
  USE CHA2
  SELECT CustomerID, LastName 
    FROM Customer
    WHERE NickName = 'Missy'

COMMIT TRANSACTION


--------------------------------------------------
-- Transaction Log sequence 

-- Reset the data 

USE OBXKites

UPDATE Product
  SET DiscontinueDate = '12/31/2003'
  WHERE Code = '1001'

UPDATE Product
  SET DiscontinueDate = '4/1/2003'
  WHERE Code = '1002'        


-- The test transaction

BEGIN TRANSACTION

UPDATE Product
  SET DiscountinueDate = '7/4/2003'
  WHERE ProductCode = '1001'

UPDATE Product
  SET DiscountinueDate = '7/4/2003'
  WHERE ProductCode = '1002'        

COMMIT TRANSACTION

ROLLBACK TRANSACTION


------------------------------------------------
-- Understanding Locks

BEGIN TRANSACTION

UPDATE Product
  SET DiscountinueDate = '7/4/2003'
  WHERE ProductCode = '1001'

Select @@SPID
exec sp_getlocks

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 ProductCode = '1001'
  Update Contact
    Set FirstName = 'Neals'
    Where ContactCode = '101'
Commit

--Transaction 1
-- Step 3 
Update Product
	Set ProductName = 'DeadLock Identification Tester'
	Where ProductCode = '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



--------------------------------------------
-- Application Locks

Select @@SPID


BEGIN TRANSACTION

DECLARE @ShareOK INT 
EXEC @ShareOK = Sp_GetAppLock 
                  @Resource = 'CableWorm', 
                  @LockMode = 'Exclusive'

/* Select @ShareOK
IF @ShareOK < 0 
   匛rror handling code

EXEC