大家都知道Scan去找資料是不好的，但您知道這樣的SQL在執行中，會拿了多少的Lock嗎?
我們可以從SQL Profiler中的Lock:Acquired及Lock:Released來觀察哦!
最近在論談上看到「两个事务 更新一张堆表 遇到奇怪的死锁问题」。
這讓我想到之前在TechDay上到強哥說的，你認為SQL Update時，一定是Row Lock嗎?
當某個Table沒有建立任何的Index，要更新資料就是要一筆筆的找，解法就是建立index把資料打散!
強哥的測試Script如下(使用TSQL2012)，
--SESSION I
USE TSQL2012
GO
IF OBJECT_ID('employees') IS NOT NULL
DROP TABLE employees
GO
SELECT *
INTO employees
FROM [HR].[Employees]
GO
SELECT *
FROM [dbo].[employees]
BEGIN TRANSACTION
UPDATE [dbo].[employees]
SET [firstname] = [firstname] + '@'
WHERE empid = 1
SELECT *
FROM [dbo].[employees]
WHERE empid = 1
--ROLLBACK
--SESSION 2
SELECT *
FROM [dbo].[employees]
WHERE empid = 2
--WHERE lastname='Funk'
--solution
DROP INDEX IDX_SPLIT ON [dbo].[employees]
CREATE CLUSTERED INDEX IDX_SPLIT ON [dbo].[employees]
([empid] DESC,[lastname] ASC )
GO
那這個問題的狀況也類似，Script如下，
USE tempdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
[A] [nvarchar](10) NULL,
[B] [nvarchar](10) NOT NULL,
[C] [nvarchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa1', N'b1', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa2', N'b3', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b4', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b5', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b2', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b6', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b7', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b8', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa1', N'b9', N'11')
開2個查詢視窗，先執行第1個查詢，再切到第2個查詢執行，
--查詢1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE table1
SET A = 'aa1'
WHERE B = 'b3'
EXEC sp_lock @@spid
WAITFOR DELAY '00:00:10'
UPDATE table1
SET A = 'aa2'
WHERE B = 'b8'
EXEC sp_lock @@spid
WAITFOR DELAY '00:00:10'
COMMIT TRAN
--查詢2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE table1
SET A = 'aa3'
WHERE B = 'b1'
EXEC sp_lock @@spid
COMMIT TRAN
等一下子後，查詢2就會被犧牲掉，如下，
Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 1205, Level 13, State 45, Line 3
交易 (處理序識別碼 117) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
DeadLock的圖如下，
sp_lock的資訊如下，
Update時，因為沒有任何的index，所以是用Scan的方式，如下，
所以，它會一筆筆地找，先取得Update Lock，不符合就Rease Update Lock，然後再取下一筆，有符合的，就取得 Exclusive Lock 。
而第2個查詢，會等待是因為要取得 Update Lock ，而該筆資料被查詢1取得了Exclusive Lock了!
So...這種狀況就是建立Index讓它可以馬上找到，而不需用Scan的方式去找，而造成Lock的等待，
CREATE NONCLUSTERED INDEX nidx_table1_B
ON table1(B);
而建立了index後，可以發現，不會逐筆掃，而會直接針對該筆資料進行 Update 及 Exclusive Lock 然後修改完資料後，Release Lock! 如下，
參考資料
Using a Clustered Index to Solve a SQL Server Deadlock Issue
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
