?--SQL Server中的事務(wù)與鎖

?--參考資料：

?--作者：張龍豪

?--博客地址：https://www.cnblogs.com/knowledgesea/p/3714417.html

?

?--建表腳本 Start

USE [Northwind]

GO

/****** Object:?Table [dbo].[lives]??Script Date: 06/07/2023 10:03:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[lives](

[Eat] [nchar](100) NULL,

[Play] [nchar](100) NULL,

[Numb] [int] NULL

) ON [PRIMARY]

GO

USE [Northwind]

GO

/****** Object:?Table [dbo].[Earth]??Script Date: 06/07/2023 10:04:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Earth](

[Animal] [nchar](100) NULL,

[Plants] [nchar](100) NULL,

[Mountain] [nchar](100) NULL,

[Others] [nchar](100) NULL

) ON [PRIMARY]

GO

?--建表腳本 End

?

--了解事務(wù)和鎖

--事務(wù)：保持邏輯數(shù)據(jù)一致性與可恢復(fù)性，必不可少的利器。

--鎖：多用戶訪問同一數(shù)據(jù)庫(kù)資源時(shí)，對(duì)訪問的先后次序權(quán)限管理的一種機(jī)制，沒有他事務(wù)或許將會(huì)一塌糊涂，不能保證數(shù)據(jù)的安全正確讀寫。

--死鎖：是數(shù)據(jù)庫(kù)性能的重量級(jí)殺手之一，而死鎖卻是不同事務(wù)之間搶占數(shù)據(jù)資源造成的。

--不懂的聽上去，挺神奇的，懂的感覺我在扯淡，下面帶你好好領(lǐng)略下他們的風(fēng)采，嗅査下他們的狂騷。。

--先說事務(wù)--概念，分類

--用華仔無間道中的一句來給你詮釋下：去不了終點(diǎn)，回到原點(diǎn)。

--舉例說明：

--在一個(gè)事務(wù)中，你寫啦2條sql語句，一條是修改訂單表狀態(tài),一條是修改庫(kù)存表庫(kù)存-1 。 如果在修改訂單表狀態(tài)的時(shí)候出錯(cuò)，事務(wù)能夠回滾，數(shù)據(jù)將恢復(fù)到?jīng)]修改之前的數(shù)據(jù)狀態(tài)，下面的修改庫(kù)存也就不執(zhí)行，這樣確保你關(guān)系邏輯的一致，安全。。

--事務(wù)就是這個(gè)樣子，倔脾氣，要么全部執(zhí)行，要么全部不執(zhí)行，回到原數(shù)據(jù)狀態(tài)。

--書面解釋：事務(wù)具有原子性，一致性，隔離性，持久性。

--原子性：事務(wù)必須是一個(gè)自動(dòng)工作的單元，要么全部執(zhí)行，要么全部不執(zhí)行。

--一致性：事務(wù)結(jié)束的時(shí)候，所有的內(nèi)部數(shù)據(jù)都是正確的。

--隔離性：并發(fā)多個(gè)事務(wù)時(shí)，各個(gè)事務(wù)不干涉內(nèi)部數(shù)據(jù)，處理的都是另外一個(gè)事務(wù)處理之前或之后的數(shù)據(jù)。

--持久性：事務(wù)提交之后，數(shù)據(jù)是永久性的，不可再回滾。

--然而在SQL Server中事務(wù)被分為3類常見的事務(wù)：

--自動(dòng)提交事務(wù)：是SQL Server默認(rèn)的一種事務(wù)模式，每條Sql語句都被看成一個(gè)事務(wù)進(jìn)行處理，你應(yīng)該沒有見過，一條Update 修改2個(gè)字段的語句，只修該了1個(gè)字段而另外一個(gè)字段沒有修改。。

--顯式事務(wù)：T-sql標(biāo)明，由Begin Transaction開啟事務(wù)開始，由Commit Transaction 提交事務(wù)、Rollback Transaction 回滾事務(wù)結(jié)束。

--隱式事務(wù)：使用Set IMPLICIT_TRANSACTIONS ON 將將隱式事務(wù)模式打開，不用Begin Transaction開啟事務(wù)，當(dāng)一個(gè)事務(wù)結(jié)束，這個(gè)模式會(huì)自動(dòng)啟用下一個(gè)事務(wù)，只用Commit Transaction 提交事務(wù)、Rollback Transaction 回滾事務(wù)即可。

--顯式事務(wù)的應(yīng)用

--常用語句就四個(gè)。

--Begin Transaction：標(biāo)記事務(wù)開始。

--Commit Transaction：事務(wù)已經(jīng)成功執(zhí)行，數(shù)據(jù)已經(jīng)處理妥當(dāng)。

--Rollback Transaction：數(shù)據(jù)處理過程中出錯(cuò)，回滾到?jīng)]有處理之前的數(shù)據(jù)狀態(tài)，或回滾到事務(wù)內(nèi)部的保存點(diǎn)。

--Save Transaction：事務(wù)內(nèi)部設(shè)置的保存點(diǎn)，就是事務(wù)可以不全部回滾，只回滾到這里，保證事務(wù)內(nèi)部不出錯(cuò)的前提下。

--上面的都是心法，下面的給你來個(gè)招式，要看仔細(xì)啦。

---開啟事務(wù)

begin tran

--錯(cuò)誤撲捉機(jī)制，看好啦，這里也有的。并且可以嵌套。

begin try

??--語句正確

??insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)

??--Numb為int類型，出錯(cuò)

??insert into lives (Eat,Play,Numb) values ('豬肉','足球','abc')

??--語句正確

??insert into lives (Eat,Play,Numb) values ('狗肉','籃球',2)

end try

begin catch

??select Error_number() as ErrorNumber,?--錯(cuò)誤代碼

?????Error_severity() as ErrorSeverity,?--錯(cuò)誤嚴(yán)重級(jí)別，級(jí)別小于10 try catch 捕獲不到

?????Error_state() as ErrorState ,?--錯(cuò)誤狀態(tài)碼

?????Error_Procedure() as ErrorProcedure , --出現(xiàn)錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。

?????Error_line() as ErrorLine,?--發(fā)生錯(cuò)誤的行號(hào)

?????Error_message() as ErrorMessage?--錯(cuò)誤的具體信息

??if(@@trancount>0) --全局變量@@trancount，事務(wù)開啟此值+1，他用來判斷是有開啟事務(wù)

???rollback tran?---由于出錯(cuò)，這里回滾到開始，第一條語句也沒有插入成功。

end catch

if(@@trancount>0)

commit tran?--如果成功Lives表中，將會(huì)有3條數(shù)據(jù)。

--表本身為空表，ID ,Numb為int 類型，其它為nvarchar類型

select * from lives

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

---開啟事務(wù)

begin tran

--錯(cuò)誤撲捉機(jī)制，看好啦，這里也有的。并且可以嵌套。

begin try???

??--語句正確

??insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)??

??--加入保存點(diǎn)

??save tran pigOneIn

??--Numb為int類型，出錯(cuò)

??insert into lives (Eat,Play,Numb) values ('豬肉','足球',2)

??--語句正確

??insert into lives (Eat,Play,Numb) values ('狗肉','籃球',3)

end try

begin catch

??select Error_number() as ErrorNumber,?--錯(cuò)誤代碼

?????Error_severity() as ErrorSeverity,?--錯(cuò)誤嚴(yán)重級(jí)別，級(jí)別小于10 try catch 捕獲不到

?????Error_state() as ErrorState ,?--錯(cuò)誤狀態(tài)碼

?????Error_Procedure() as ErrorProcedure , --出現(xiàn)錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。

?????Error_line() as ErrorLine,?--發(fā)生錯(cuò)誤的行號(hào)

?????Error_message() as ErrorMessage?--錯(cuò)誤的具體信息

??if(@@trancount>0) --全局變量@@trancount，事務(wù)開啟此值+1，他用來判斷是有開啟事務(wù)

???rollback tran??---由于出錯(cuò)，這里回滾事務(wù)到原點(diǎn)，第一條語句也沒有插入成功。

end catch

if(@@trancount>0)

rollback tran pigOneIn --如果成功Lives表中，將會(huì)有3條數(shù)據(jù)。

--表本身為空表，ID ,Numb為int 類型，其它為nvarchar類型

select * from lives

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

--使用set xact_abort

--設(shè)置 xact_abort on/off , 指定是否回滾當(dāng)前事務(wù)，為on時(shí)如果當(dāng)前sql出錯(cuò)，回滾整個(gè)事務(wù)，為off時(shí)如果sql出錯(cuò)回滾當(dāng)前sql語句，其它語句照常運(yùn)行讀寫數(shù)據(jù)庫(kù)。

-- 需要注意的時(shí)：xact_abort只對(duì)運(yùn)行時(shí)出現(xiàn)的錯(cuò)誤有用，如果sql語句存在編譯時(shí)錯(cuò)誤，那么他就失靈啦。

delete lives?--清空數(shù)據(jù)

set xact_abort off

begin tran?

??--語句正確

??insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)??

??--Numb為int類型，出錯(cuò),如果1234..那個(gè)大數(shù)據(jù)換成'132dsaf' xact_abort將失效

??insert into lives (Eat,Play,Numb) values ('豬肉','足球',12345646879783213)

??--語句正確

??insert into lives (Eat,Play,Numb) values ('狗肉','籃球',3)

commit tran

select * from lives

--為on時(shí)，結(jié)果集為空，因?yàn)檫\(yùn)行是數(shù)據(jù)過大溢出出錯(cuò)，回滾整個(gè)事務(wù)。

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

--事務(wù)把死鎖給整出來啦

--跟著做：打開兩個(gè)查詢窗口，把下面的語句，分別放入2個(gè)查詢窗口，在5秒內(nèi)運(yùn)行2個(gè)事務(wù)模塊。

begin tran?

?update lives set play='羽毛球'

?waitfor delay '0:0:5'??

?update dbo.Earth set Animal='老虎'?

commit tran

begin tran?

?update Earth set Animal='老虎'?

?waitfor?delay '0:0:5' --等待5秒執(zhí)行下面的語句

?update lives set play='羽毛球'

commit tran

select * from lives

select * from Earth

?

--為什么呢，下面我們看看鎖，什么是鎖。

--并發(fā)事務(wù)成敗皆歸于鎖——鎖定

--在多用戶都用事務(wù)同時(shí)訪問同一個(gè)數(shù)據(jù)資源的情況下，就會(huì)造成以下幾種數(shù)據(jù)錯(cuò)誤。

--更新丟失：多個(gè)用戶同時(shí)對(duì)一個(gè)數(shù)據(jù)資源進(jìn)行更新，必定會(huì)產(chǎn)生被覆蓋的數(shù)據(jù)，造成數(shù)據(jù)讀寫異常。

--不可重復(fù)讀：如果一個(gè)用戶在一個(gè)事務(wù)中多次讀取一條數(shù)據(jù)，而另外一個(gè)用戶則同時(shí)更新啦這條數(shù)據(jù)，造成第一個(gè)用戶多次讀取數(shù)據(jù)不一致。

--臟讀：第一個(gè)事務(wù)讀取第二個(gè)事務(wù)正在更新的數(shù)據(jù)表，如果第二個(gè)事務(wù)還沒有更新完成，那么第一個(gè)事務(wù)讀取的數(shù)據(jù)將是一半為更新過的，一半還沒更新過的數(shù)據(jù)，這樣的數(shù)據(jù)毫無意義。

--幻讀：第一個(gè)事務(wù)讀取一個(gè)結(jié)果集后，第二個(gè)事務(wù)，對(duì)這個(gè)結(jié)果集經(jīng)行增刪操作，然而第一個(gè)事務(wù)中再次對(duì)這個(gè)結(jié)果集進(jìn)行查詢時(shí)，數(shù)據(jù)發(fā)現(xiàn)丟失或新增。

--然而鎖定，就是為解決這些問題所生的，他的存在使得一個(gè)事務(wù)對(duì)他自己的數(shù)據(jù)塊進(jìn)行操作的時(shí)候，而另外一個(gè)事務(wù)則不能插足這些數(shù)據(jù)塊。這就是所謂的鎖定。

--鎖定從數(shù)據(jù)庫(kù)系統(tǒng)的角度大致可以分為6種：

--共享鎖（S）：還可以叫他讀鎖?？梢圆l(fā)讀取數(shù)據(jù)，但不能修改數(shù)據(jù)。也就是說當(dāng)數(shù)據(jù)資源上存在共享鎖的時(shí)候，所有的事務(wù)都不能對(duì)這個(gè)資源進(jìn)行修改，直到數(shù)據(jù)讀取完成，共享鎖釋放。

--排它鎖（X）：還可以叫他獨(dú)占鎖、寫鎖。就是如果你對(duì)數(shù)據(jù)資源進(jìn)行增刪改操作時(shí)，不允許其它任何事務(wù)操作這塊資源，直到排它鎖被釋放，防止同時(shí)對(duì)同一資源進(jìn)行多重操作。

--更新鎖（U）：防止出現(xiàn)死鎖的鎖模式，兩個(gè)事務(wù)對(duì)一個(gè)數(shù)據(jù)資源進(jìn)行先讀取在修改的情況下，使用共享鎖和排它鎖有時(shí)會(huì)出現(xiàn)死鎖現(xiàn)象，而使用更新鎖則可以避免死鎖的出現(xiàn)。資源的更新鎖一次只能分配給一個(gè)事務(wù)，如果需要對(duì)資源進(jìn)行修改，更新鎖會(huì)變成排他鎖，否則變?yōu)楣蚕礞i。

--意向鎖：SQL Server需要在層次結(jié)構(gòu)中的底層資源上（如行，列）獲取共享鎖，排它鎖，更新鎖。例如表級(jí)放置了意向共享鎖，就表示事務(wù)要對(duì)表的頁(yè)或行上使用共享鎖。在表的某一行上上放置意向鎖，可以防止其它事務(wù)獲取其它不兼容的的鎖。意向鎖可以提高性能，因?yàn)閿?shù)據(jù)引擎不需要檢測(cè)資源的每一列每一行，就能判斷是否可以獲取到該資源的兼容鎖。意向鎖包括三種類型：意向共享鎖（IS），意向排他鎖（IX），意向排他共享鎖（SIX）。

--架構(gòu)鎖：防止修改表結(jié)構(gòu)時(shí)，并發(fā)訪問的鎖。

--大容量更新鎖：允許多個(gè)線程將大容量數(shù)據(jù)并發(fā)的插入到同一個(gè)表中，在加載的同時(shí)，不允許其它進(jìn)程訪問該表。

--這些鎖之間的相互兼容性，也就是，是否可以同時(shí)存在。?

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

-- 死鎖

--什么是死鎖，為什么會(huì)產(chǎn)生死鎖。我用 “事務(wù)把死鎖給整出來啦” 標(biāo)題下的兩個(gè)事務(wù)產(chǎn)生的死鎖來解釋應(yīng)該會(huì)更加生動(dòng)形象點(diǎn)。

--例子是這樣的：

--第一個(gè)事務(wù)（稱為A）：先更新lives表 --->>停頓5秒---->>更新earth表

--第二個(gè)事務(wù)（稱為B）：先更新earth表--->>停頓5秒---->>更新lives表

--先執(zhí)行事務(wù)A----5秒之內(nèi)---執(zhí)行事務(wù)B，出現(xiàn)死鎖現(xiàn)象。

--過程是這樣子的：

--A更新lives表，請(qǐng)求lives的排他鎖，成功。

--B更新earth表，請(qǐng)求earth的排他鎖，成功。

--5秒過后

--A更新earth，請(qǐng)求earth的排它鎖，由于B占用著earth的排它鎖，等待。

--B更新lives，請(qǐng)求lives的排它鎖，由于A占用著lives的排它鎖，等待。

--這樣相互等待對(duì)方釋放資源，造成資源讀寫擁擠堵塞的情況，就被稱為死鎖現(xiàn)象，也叫做阻塞。而為什么會(huì)產(chǎn)生，上例就列舉出來啦。

--然而數(shù)據(jù)庫(kù)并沒有出現(xiàn)無限等待的情況，是因?yàn)閿?shù)據(jù)庫(kù)搜索引擎會(huì)定期檢測(cè)這種狀況，一旦發(fā)現(xiàn)有情況，立馬選擇一個(gè)事務(wù)作為犧牲品。犧牲的事務(wù)，將會(huì)回滾數(shù)據(jù)。有點(diǎn)像兩個(gè)人在過獨(dú)木橋，兩個(gè)無腦的人都走在啦獨(dú)木橋中間，如果不落水，必定要有一個(gè)人給退回來。這種相互等待的過程，是一種耗時(shí)耗資源的現(xiàn)象，所以能避則避。

--哪個(gè)人會(huì)被退回來，作為犧牲品，這個(gè)我們是可以控制的。控制語法：

set deadlock_priority?<級(jí)別>

--死鎖處理的優(yōu)先級(jí)別為 low<normal<high，不指定的情況下默認(rèn)為normal，犧牲品為隨機(jī)。如果指定，犧牲品為級(jí)別低的。

--還可以使用數(shù)字來處理標(biāo)識(shí)級(jí)別：-10到-5為low，-5為normal，-5到10為high。

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

--減少死鎖的發(fā)生，提高數(shù)據(jù)庫(kù)性能

--死鎖耗時(shí)耗資源，然而在大型數(shù)據(jù)庫(kù)中，高并發(fā)帶來的死鎖是不可避免的，所以我們只能讓其變的更少。

--按照同一順序訪問數(shù)據(jù)庫(kù)資源，上述例子就不會(huì)發(fā)生死鎖啦

--保持是事務(wù)的簡(jiǎn)短，盡量不要讓一個(gè)事務(wù)處理過于復(fù)雜的讀寫操作。事務(wù)過于復(fù)雜，占用資源會(huì)增多，處理時(shí)間增長(zhǎng)，容易與其它事務(wù)沖突，提升死鎖概率。

--盡量不要在事務(wù)中要求用戶響應(yīng)，比如修改新增數(shù)據(jù)之后在完成整個(gè)事務(wù)的提交，這樣延長(zhǎng)事務(wù)占用資源的時(shí)間，也會(huì)提升死鎖概率。

--盡量減少數(shù)據(jù)庫(kù)的并發(fā)量。

--盡可能使用分區(qū)表，分區(qū)視圖，把數(shù)據(jù)放置在不同的磁盤和文件組中，分散訪問保存在不同分區(qū)的數(shù)據(jù)，減少因?yàn)楸碇蟹胖面i而造成的其它事務(wù)長(zhǎng)時(shí)間等待。

--避免占用時(shí)間很長(zhǎng)并且關(guān)系表復(fù)雜的數(shù)據(jù)操作。

--使用較低的隔離級(jí)別，使用較低的隔離級(jí)別比使用較高的隔離級(jí)別持有共享鎖的時(shí)間更短。這樣就減少了鎖爭(zhēng)用。

--可參考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx

--查看鎖活動(dòng)情況：

--查看鎖活動(dòng)情況

select * from sys.dm_tran_locks

--查看事務(wù)活動(dòng)情況

dbcc opentran

--可參考：http://msdn.microsoft.com/zh-cn/library/ms190345.aspx

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

--為事務(wù)設(shè)置隔離級(jí)別

--所謂事物隔離級(jí)別，就是并發(fā)事務(wù)對(duì)同一資源的讀取深度層次。分為5種。

--read uncommitted ：這個(gè)隔離級(jí)別最低啦，可以讀取到一個(gè)事務(wù)正在處理的數(shù)據(jù)，但事務(wù)還未提交，這種級(jí)別的讀取叫做臟讀。

--read committed：這個(gè)級(jí)別是默認(rèn)選項(xiàng)，不能臟讀，不能讀取事務(wù)正在處理沒有提交的數(shù)據(jù)，但能修改。

--repeatable read：不能讀取事務(wù)正在處理的數(shù)據(jù)，也不能修改事務(wù)處理數(shù)據(jù)前的數(shù)據(jù)。

--snapshot：指定事務(wù)在開始的時(shí)候，就獲得了已經(jīng)提交數(shù)據(jù)的快照，因此當(dāng)前事務(wù)只能看到事務(wù)開始之前對(duì)數(shù)據(jù)所做的修改。

--serializable：最高事務(wù)隔離級(jí)別，只能看到事務(wù)處理之前的數(shù)據(jù)。?

--語法

set tran isolation level <級(jí)別>

--read uncommitted隔離級(jí)別的例子：

begin tran?

?set deadlock_priority low

?update Earth set Animal='老虎'?

?waitfor?delay '0:0:5' --等待5秒執(zhí)行下面的語句

rollback tran

--開另外一個(gè)查詢窗口執(zhí)行下面語句

set tran isolation level read uncommitted

select * from Earth?--讀取的數(shù)據(jù)為正在修改的數(shù)據(jù) ，臟讀

waitfor?delay '0:0:5'?--5秒之后數(shù)據(jù)已經(jīng)回滾

select * from Earth?--回滾之后的數(shù)據(jù)

--read committed隔離級(jí)別的例子：

begin tran?

?update Earth set Animal='老虎'?

?waitfor?delay '0:0:10' --等待10秒執(zhí)行下面的語句

rollback tran

set tran isolation level read committed

select * from Earth ---獲取不到老虎，不能臟讀

update Earth set Animal='猴子1'??--可以修改

waitfor?delay '0:0:10'?--10秒之后上一個(gè)事務(wù)已經(jīng)回滾

select * from Earth?--修改之后的數(shù)據(jù)，而不是猴子

--剩下的幾個(gè)級(jí)別，不一一列舉啦，自己理解吧。

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

--設(shè)置鎖超時(shí)時(shí)間

--發(fā)生死鎖的時(shí)候，數(shù)據(jù)庫(kù)引擎會(huì)自動(dòng)檢測(cè)死鎖，解決問題，然而這樣子是很被動(dòng)，只能在發(fā)生死鎖后，等待處理。

--然而我們也可以主動(dòng)出擊，設(shè)置鎖超時(shí)時(shí)間，一旦資源被鎖定阻塞，超過設(shè)置的鎖定時(shí)間，阻塞語句自動(dòng)取消，釋放資源，報(bào)1222錯(cuò)誤。

--好東西一般都具有兩面性，調(diào)優(yōu)的同時(shí)，也有他的不足之處，那就是一旦超過時(shí)間，語句取消，釋放資源，但是當(dāng)前報(bào)錯(cuò)事務(wù)，不會(huì)回滾，會(huì)造成數(shù)據(jù)錯(cuò)誤，你需要在程序中捕獲1222錯(cuò)誤，用程序處理當(dāng)前事務(wù)的邏輯，使數(shù)據(jù)正確。

--查看超時(shí)時(shí)間,默認(rèn)為-1

select @@lock_timeout

--設(shè)置超時(shí)時(shí)間

set lock_timeout 0 --為0時(shí)，即為一旦發(fā)現(xiàn)資源鎖定，立即報(bào)錯(cuò)，不在等待，當(dāng)前事務(wù)不回滾，設(shè)置時(shí)間需謹(jǐn)慎處理后事啊，你hold不住的。

--查看與殺死鎖和進(jìn)程

----------------------------------檢測(cè)死鎖-------------------------------------------

--如果發(fā)生死鎖了，我們?cè)趺慈z測(cè)具體發(fā)生死鎖的是哪條SQL語句或存儲(chǔ)過程？

--這時(shí)我們可以使用以下存儲(chǔ)過程來檢測(cè)，就可以查出引起死鎖的進(jìn)程和SQL語句。SQL Server自帶的系統(tǒng)存儲(chǔ)過程sp_who和sp_lock也可以用來查找阻塞和死鎖, 但沒有這里介紹的方法好用。?

use master

go

create procedure sp_who_lock

as

begin

declare @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 @@ERROR

?

?insert into #tmp_lock_who(spid,bl) select?0 ,blocked

??from (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>0

?IF @@ERROR<>0 RETURN @@ERROR?

??

-- 找到臨時(shí)表的記錄數(shù)

?select?@intCountProperties = Count(*),@intCounter = 1

?from #tmp_lock_who

?

?IF @@ERROR<>0 RETURN @@ERROR?

?

?if @intCountProperties=0

?select '現(xiàn)在沒有阻塞和死鎖信息' as message

-- 循環(huán)開始

while @intCounter <= @intCountProperties

begin

-- 取第一條記錄

?select?@spid = spid,@bl = bl

?from #tmp_lock_who where Id = @intCounter?

?begin

?if @spid =0?

??????select '引起數(shù)據(jù)庫(kù)死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '進(jìn)程號(hào),其執(zhí)行的SQL語法如下'

?else

??????select '進(jìn)程號(hào)SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '進(jìn)程號(hào)SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其當(dāng)前進(jìn)程執(zhí)行的SQL語法如下'

?DBCC INPUTBUFFER (@bl )

?end?

-- 循環(huán)指針下移

?set @intCounter = @intCounter + 1

end

drop table #tmp_lock_who

return 0

end

?

--用法

exec sp_who_lock

drop procedure sp_who_lock

----------------------------------殺死鎖和進(jìn)程----------------------------------

--如何去手動(dòng)的殺死進(jìn)程和鎖？最簡(jiǎn)單的辦法，重新啟動(dòng)服務(wù)。但是這里要介紹一個(gè)存儲(chǔ)過程，通過顯式的調(diào)用，可以殺死進(jìn)程和鎖。

use master

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_killspid]

GO

create proc p_killspid

@dbname varchar(200)??--要關(guān)閉進(jìn)程的數(shù)據(jù)庫(kù)名

as??

??declare @sql?nvarchar(500)??

??declare @spid nvarchar(20)

??declare #tb cursor for

????select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)

??open #tb

??fetch next from #tb into @spid

??while @@fetch_status=0

??begin??

????exec('kill '+@spid)

????fetch next from #tb into @spid

??end??

??close #tb

??deallocate #tb

go

--用法??

exec p_killspid?'newdbpy'?

----------------------------------查看鎖信息----------------------------------

--如何查看系統(tǒng)中所有鎖的詳細(xì)信息？在企業(yè)管理管理器中，我們可以看到一些進(jìn)程和鎖的信息，這里介紹另外一種方法。

--查看鎖信息

create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)

??,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for?

??select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid

??from master..syslockinfo where rsc_type in(4,5)

open tb

fetch next from tb into @rid,@dbname,@id

while @@fetch_status=0

begin

??set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'

??exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id

??insert into #t values(@rid,@objname)

??fetch next from tb into @rid,@dbname,@id

end

close tb

deallocate tb

select 進(jìn)程id=a.req_spid

??,數(shù)據(jù)庫(kù)=db_name(rsc_dbid)

??,類型=case rsc_type when 1 then 'NULL 資源（未使用）'

????when 2 then '數(shù)據(jù)庫(kù)'

????when 3 then '文件'

????when 4 then '索引'

????when 5 then '表'

????when 6 then '頁(yè)'

????when 7 then '鍵'

????when 8 then '擴(kuò)展盤區(qū)'

????when 9 then 'RID（行 ID)'

????when 10 then '應(yīng)用程序'

??end

??,對(duì)象id=rsc_objid

??,對(duì)象名=b.obj_name

??,rsc_indid

?from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go

drop table #t