交易，鎖定，阻斷與死結

--顯性交易的運用

-- Before count
SELECT COUNT(*) BeforeCount FROM HumanResources.Department
-- Variable to hold the latest error integer value
DECLARE @Error int
BEGIN TRANSACTION
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Accounts Payable', 'Accounting')
SET @Error = @@ERROR
IF (@Error<> 0) GOTO Error_Handler
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Engineering', 'Research and Development')
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler
COMMIT TRAN
Error_Handler:
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- After count
SELECT COUNT(*) AfterCount FROM HumanResources.Department

--用 DBCC OPENTRAN 顯示先前仍在進行中的交易

BEGIN TRANSACTION
DELETE Production.ProductProductPhoto
WHERE ProductID = 317
DBCC OPENTRAN('AdventureWorks')
ROLLBACK TRAN

--檢視鎖定動態

USE AdventureWorks
BEGIN TRAN
SELECT ProductID, DocumentID, ModifiedDate
FROM Production.ProductDocument
WITH (TABLOCKX)

SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks

--SET TRANSACTION ISOLATION LEVEL 的運用
--Using SERIALIZABLE
USE AdventureWorks
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRAN
SELECT AddressTypeID, Name
FROM Person.AddressType
WHERE AddressTypeID BETWEEN 1 AND 6

--
SELECT resource_associated_entity_id, resource_type,
  request_mode, request_session_id
FROM sys.dm_tran_locks

--
COMMIT TRAN

-- Using READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

BEGIN TRAN

SELECT AddressTypeID, Name
FROM Person.AddressType
WHERE AddressTypeID BETWEEN 1 AND 6

--
SELECT resource_associated_entity_id, resource_type,
  request_mode, request_session_id
FROM sys.dm_tran_locks

--
COMMIT TRAN

-- Using SNAPSHOP
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE AdventureWorks
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

BEGIN TRAN

SELECT CurrencyRateID,
    EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateID = 8317

--
USE AdventureWorks
GO

UPDATE Sales.CurrencyRate
SET EndOfDayRate = 1.00
WHERE CurrencyRateID = 8317

--
SELECT CurrencyRateID,
    EndOfDayRate
FROM Sales.CurrencyRate
WHERE CurrencyRateID = 8317

-- 找出並解決造成阻斷的處理序

--1
BEGIN TRAN

UPDATE Production.ProductInventory
SET Quantity = 400
WHERE ProductID = 1 AND
LocationID = 1

--2
BEGIN TRAN

UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND
LocationID = 1

--3
SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL

--
DBCC INPUTBUFFER(52)

--
KILL 52

--SET LOCK TIMEOUT 的運用

SET LOCK_TIMEOUT 1000

UPDATE Production.ProductInventory
SET Quantity = 406
WHERE ProductID = 1 AND

 

LocationID = 1

--用 Trace Flag 找出死結所在
--1
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1
BEGIN
BEGIN TRAN

  UPDATE Purchasing.Vendor
  SET CreditRating = 1
  WHERE VendorID = 2
  UPDATE Purchasing.Vendor
  SET CreditRating = 2
  WHERE VendorID = 1

COMMIT TRAN
END

--2
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1
BEGIN
BEGIN TRAN

  UPDATE Purchasing.Vendor
  SET CreditRating = 2
  WHERE VendorID = 1

  UPDATE Purchasing.Vendor
  SET CreditRating = 1
  WHERE VendorID = 2
COMMIT TRAN
END

--3
DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

--
DBCC TRACEOFF (1222, -1)
GO
DBCC TRACESTATUS

--定義死結處理優先性
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET DEADLOCK_PRIORITY LOW

WHILE 1=1
BEGIN
BEGIN TRAN

  UPDATE Purchasing.Vendor
  SET CreditRating = 1
  WHERE VendorID = 2

  UPDATE Purchasing.Vendor
  SET CreditRating = 2
  WHERE VendorID = 1

COMMIT TRAN
END