程式筆記

找出互相影響的spid

• Blocking：
  • 方法一：SQL
    • 找出長時間處理且未結束session
    • spid=66執行被BlockBy_SessionID=65擋住, 可能因為spid=65的作業還沒commit, 導致無法完成。
    • 可參考SQLStatement check資料處理內容
    • 
  • 方法二：exec sp_lock
    • 欄位說明 
    • spid=66的Update正在wait, 其ObjId為741577680, 同時在spid=65也有處理741577680, 可能spid=66等待spid=65對資料的鎖定結束。
• DeadLock：event監控
  • 監控條件為name =  xml_deadlock_report
  • 

確認要kill的程序內容, 再決定誰該kill

• DBCC INPUTBUFFER(66)
• DBCC INPUTBUFFER(65)
• 

kill spid

• kill 66 or kill 65

模擬DeadLock

--open Companyty query windows in SSMS-A
CREATE TABLE ##Companies (
    CompanyId INT IDENTITY,
    CompanyName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

INSERT INTO ##Companies (CompanyName, Phone)
VALUES ('Matt', '800-555-1212'), ('Jim', '619-555-8080')
GO

CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO ##Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

--執行Ａ全部

--open Companyty query windows in SSMS-B
BEGIN TRAN;   

--Step1
UPDATE ##Companies
SET CompanyName = 'Jack'
WHERE CompanyId = 1

--Step3
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1


--open Companyty query windows in SSMS-C
BEGIN TRAN;

--Step2
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1


--Step4
UPDATE ##Companies
SET phone = N'555-9999'	
WHERE CompanyId = 1

--先執行B-Step1, 再執行C-Step2, 再執行B-Step3, 最後執行C-Step4, 此時B-Step3會被中斷回覆(交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。)​

模擬Blocking

--open empty query windows in SSMS-A
CREATE TABLE ##Companies (
    CompanyId INT IDENTITY,
    CompanyName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

INSERT INTO ##Companies (CompanyName, Phone)
VALUES ('Matt', '800-555-1212'), ('Jim', '619-555-8080')
GO

CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO ##Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

--執行Ａ全部

--open empty query windows in SSMS-B
BEGIN TRAN;   

--Step1
UPDATE ##Companies
SET CompanyName = 'Jack'
WHERE CompanyId = 1

--Step3
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1


--open empty query windows in SSMS-C
BEGIN TRAN;

--Step2
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1


--Step4
UPDATE ##Companies
SET phone = N'555-9999'	
WHERE CompanyId = 1


--先執行B-Step1及B-Step3, 再執行C-Step2及C-Step4, 此時C畫面會一直在執行中。