推薦：解析SQL Server數(shù)據(jù)庫觸發(fā)器的安全隱患
觸發(fā)器權限和所有權 CREATE TRIGGER 權限默認授予定義觸發(fā)器的表所有者、sysadmin 固定服務器角色成員以及 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員，并且不可轉讓。 需要的環(huán)境 本文需要的環(huán)境是已經獲取了sql服務器的以上其中一個權限，目的是為了留下

死鎖是指在某組資源中，兩個或兩個以上的線程在執(zhí)行過程中，在爭奪某一資源時而造成互相等待的現(xiàn)象，若無外力的作用下,它們都將無法推進下去，死時就可能會產生死鎖,這些永遠在互相等待的進程稱為死鎖線程。簡單的說，進程A等待進程B釋放他的資源，B又等待A釋放他的資源，這樣互相等待就形成死鎖。

如在數(shù)據(jù)庫中，如果需要對一條數(shù)據(jù)進行修改，首先數(shù)據(jù)庫管理系統(tǒng)會在上面加鎖，以保證在同一時間只有一個事務能進行修改操作。如事務1的線程 T1具有表A上的排它鎖，事務2的線程T2 具有表B上的排它鎖，并且之后需要表A上的鎖。事務2無法獲得這一鎖，因為事務1已擁有它。事務2被阻塞，等待事務1。然后，事務1需要表B的鎖，但無法獲得鎖，因為事務2將它鎖定了。事務在提交或回滾之前不能釋放持有的鎖。因為事務需要對方控制的鎖才能繼續(xù)操作，所以它們不能提交或回滾，這樣數(shù)據(jù)庫就會發(fā)生死鎖了。

如在編寫存儲過程的時候，由于有些存儲過程事務性的操作比較頻繁，如果先鎖住表A，再鎖住表B，那么在所有的存儲過程中都要按照這個順序來鎖定它們。如果無意中某個存儲過程中先鎖定表B，再鎖定表A，這可能就會導致一個死鎖。而且死鎖一般是不太容易被發(fā)現(xiàn)的。

如果服務器上經常出現(xiàn)這種死鎖情況，就會降低服務器的性能，所以應用程序在使用的時候，我們就需要對其進行跟蹤，使用sp_who和sp_who2來確定可能是哪些用戶阻塞了其他用戶，我們還可以用下面的存儲過程來跟蹤具體的死鎖執(zhí)行的影響：

create procedure sp_who_lockasbegindeclare @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter int create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid)union select spid,blocked from sysprocesses where blocked>0IF @@ERROR<>0 RETURN @@ERROR -- 找到臨時表的記錄數(shù)select @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0select '現(xiàn)在沒有阻塞和死鎖信息' as message-- 循環(huán)開始while @intCounter <= @intCountPropertiesbegin-- 取第一條記錄select @spid = spid,@bl = blfrom #tmp_lock_who where id = @intCounter beginif @spid =0 select '引起數(shù)據(jù)庫死鎖的是: ' CAST(@bl AS VARCHAR(10)) '進程號,其執(zhí)行的SQL語法如下'elseselect '進程號SPID：' CAST(@spid AS VARCHAR(10)) '被' '進程號SPID：' CAST(@bl AS VARCHAR(10)) '阻塞,其當前進程執(zhí)行的SQL語法如下'DBCC INPUTBUFFER (@bl )end -- 循環(huán)指針下移set @intCounter = @intCounter 1enddrop table #tmp_lock_whoreturn 0

我們只需要通過在查詢分析器里面執(zhí)行sp_who_lock，就可以具體捕捉到執(zhí)行的堵塞進程，這時我們就可以對對應的SQL語句或者存儲過程進行性能上面的改進及設計。

所以我們在數(shù)據(jù)庫設計的時候，雖然不能完全避免死鎖，但可以使死鎖的數(shù)量盡量減少。增加事務的吞吐量并減少系統(tǒng)開銷，因為只有很少的事務，所以就得遵循下面的原則：

按同一順序訪問對象

如果所有并發(fā)事務按同一順序訪問對象，則發(fā)生死鎖的可能性會降低。在寫SQL語句或存儲過程的時候，就需要按照順序在兩個并發(fā)事務中先獲得表A上的鎖，然后獲得表B上的鎖，當?shù)谝粋�事務完成之前，另一個事務被阻塞在表A上。第一個事務提交或回滾后，第二個事務繼續(xù)進行，而不能在語句里面寫先獲得表B上的鎖，然后再獲得表A的鎖。

避免事務中的用戶交互

避免編寫包含用戶交互的事務，因為運行沒有用戶交互的批處理的速度要遠遠快于用戶手動響應查詢的速度，例如答復應用程序請求參數(shù)的提示。例如，如果事務正在等待用戶輸入，而用戶就去做別的事了，則用戶將此事務掛起使之不能完成。這樣將降低系統(tǒng)的吞吐量，因為事務持有的任何鎖只有在事務提交或回滾時才會釋放。即使不出現(xiàn)死鎖的情況，訪問同一資源的其它事務也會被阻塞，等待該事務完成。

保持事務簡短并在一個批處理中

在同一數(shù)據(jù)庫中并發(fā)執(zhí)行多個需要長時間運行的事務時通常發(fā)生死鎖。事務運行時間越長，其持有排它鎖或更新鎖的時間也就越長，從而堵塞了其它活動并可能導致死鎖。保持事務在一個批處理中，可以最小化事務的網(wǎng)絡通信往返量，減少完成事務可能的延遲并釋放鎖。

使用低隔離級別

確定事務是否能在更低的隔離級別上運行。執(zhí)行提交讀允許事務讀取另一個事務已讀取(未修改)的數(shù)據(jù)，而不必等待第一個事務完成。使用較低的隔離級別(例如提交讀)而不使用較高的隔離級別(例如可串行讀)可以縮短持有共享鎖的時間，從而降低了鎖定爭奪。

使用綁定連接

使用綁定連接使同一應用程序所打開的兩個或多個連接可以相互合作。次級連接所獲得的任何鎖可以象由主連接獲得的鎖那樣持有，反之亦然，因此不會相互阻塞。

下面有一些對死鎖發(fā)生的一些建議：

1)對于頻繁使用的表使用集簇化的索引;

2)設法避免一次性影響大量記錄的T-SQL語句，特別是INSERT和UPDATE語句;

3)設法讓UPDATE和DELETE語句使用索引;

4)使用嵌套事務時，避免提交和回退沖突;

5)對一些數(shù)據(jù)不需要及時讀取更新值的表在寫SQL的時候在表后臺加上(nolock)，如：Select * from tableA(nolock)

分享：怎樣用SQL Server事件探查器創(chuàng)建跟蹤
QL Server事件探查器(Profiler)可以幫助數(shù)據(jù)庫管理員跟蹤SQL Server數(shù)據(jù)庫所執(zhí)行的特定事件，監(jiān)視數(shù)據(jù)庫的行為;并將這些有價值的信息保存到文件或表，以便以后用來分析解決數(shù)據(jù)庫出現(xiàn)的問題，對數(shù)據(jù)庫引擎性能進行優(yōu)化。本文為大家一步一步講述如何用SQL Ser