了解事務和鎖
事務:保持邏輯資料一緻性與可恢複性,必不可少的利器。
鎖:多使用者通路同一資料庫資源時,對通路的先後次序權限管理的一種機制,沒有他事務或許将會一塌糊塗,不能保證資料的安全正确讀寫。
死鎖:是資料庫性能的重量級殺手之一,而死鎖卻是不同僚務之間搶占資料資源造成的。
不懂的聽上去,挺神奇的,懂的感覺我在扯淡,下面帶你好好領略下他們的風采,嗅査下他們的狂騷。。
先說事務--概念,分類
用華仔無間道中的一句來給你诠釋下:去不了終點,回到原點。
舉例說明:
在一個事務中,你寫啦2條sql語句,一條是修改訂單表狀态,一條是修改庫存表庫存-1 。 如果在修改訂單表狀态的時候出錯,事務能夠復原,資料将恢複到沒修改之前的資料狀态,下面的修改庫存也就不執行,這樣確定你關系邏輯的一緻,安全。。
事務就是這個樣子,倔脾氣,要麼全部執行,要麼全部不執行,回到原資料狀态。
書面解釋:事務具有原子性,一緻性,隔離性,持久性。
- 原子性:事務必須是一個自動工作的單元,要麼全部執行,要麼全部不執行。
- 一緻性:事務結束的時候,所有的内部資料都是正确的。
- 隔離性:并發多個事務時,各個事務不幹涉内部資料,處理的都是另外一個事務處理之前或之後的資料。
- 持久性:事務送出之後,資料是永久性的,不可再復原。
然而在SQL Server中事務被分為3類常見的事務:
- 自動送出事務:是SQL Server預設的一種事務模式,每條Sql語句都被看成一個事務進行處理,你應該沒有見過,一條Update 修改2個字段的語句,隻修該了1個字段而另外一個字段沒有修改。。
- 顯式事務:T-sql标明,由Begin Transaction開啟事務開始,由Commit Transaction 送出事務、Rollback Transaction 復原事務結束。
- 隐式事務:使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事務模式打開,不用Begin Transaction開啟事務,當一個事務結束,這個模式會自動啟用下一個事務,隻用Commit Transaction 送出事務、Rollback Transaction 復原事務即可。
顯式事務的應用
常用語句就四個。
- Begin Transaction:标記事務開始。
- Commit Transaction:事務已經成功執行,資料已經處理妥當。
- Rollback Transaction:資料處理過程中出錯,復原到沒有處理之前的資料狀态,或復原到事務内部的儲存點。
- Save Transaction:事務内部設定的儲存點,就是事務可以不全部復原,隻復原到這裡,保證事務内部不出錯的前提下。
上面的都是心法,下面的給你來個招式,要看仔細啦。
1 ---開啟事務
2 begin tran
3 --錯誤撲捉機制,看好啦,這裡也有的。并且可以嵌套。
4 begin try
5 --語句正确
6 insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)
7 --Numb為int類型,出錯
8 insert into lives (Eat,Play,Numb) values ('豬肉','足球','abc')
9 --語句正确
10 insert into lives (Eat,Play,Numb) values ('狗肉','籃球',2)
11 end try
12 begin catch
13 select Error_number() as ErrorNumber, --錯誤代碼
14 Error_severity() as ErrorSeverity, --錯誤嚴重級别,級别小于10 try catch 捕獲不到
15 Error_state() as ErrorState , --錯誤狀态碼
16 Error_Procedure() as ErrorProcedure , --出現錯誤的存儲過程或觸發器的名稱。
17 Error_line() as ErrorLine, --發生錯誤的行号
18 Error_message() as ErrorMessage --錯誤的具體資訊
19 if(@@trancount>0) --全局變量@@trancount,事務開啟此值+1,他用來判斷是有開啟事務
20 rollback tran ---由于出錯,這裡復原到開始,第一條語句也沒有插入成功。
21 end catch
22 if(@@trancount>0)
23 commit tran --如果成功Lives表中,将會有3條資料。
24
25 --表本身為空表,ID ,Numb為int 類型,其它為nvarchar類型
26 select * from lives
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiInBnauADOyITO3kTOxMTM4EzNw8CX1ADNxAjMvwFO1MDO5MzLcl2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.jpg)
---開啟事務
begin tran
--錯誤撲捉機制,看好啦,這裡也有的。并且可以嵌套。
begin try
--語句正确
insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)
--加入儲存點
save tran pigOneIn
--Numb為int類型,出錯
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, --錯誤代碼
Error_severity() as ErrorSeverity, --錯誤嚴重級别,級别小于10 try catch 捕獲不到
Error_state() as ErrorState , --錯誤狀态碼
Error_Procedure() as ErrorProcedure , --出現錯誤的存儲過程或觸發器的名稱。
Error_line() as ErrorLine, --發生錯誤的行号
Error_message() as ErrorMessage --錯誤的具體資訊
if(@@trancount>0) --全局變量@@trancount,事務開啟此值+1,他用來判斷是有開啟事務
rollback tran ---由于出錯,這裡復原事務到原點,第一條語句也沒有插入成功。
end catch
if(@@trancount>0)
rollback tran pigOneIn --如果成功Lives表中,将會有3條資料。
--表本身為空表,ID ,Numb為int 類型,其它為nvarchar類型
select * from lives
使用set xact_abort
設定 xact_abort on/off , 指定是否復原目前事務,為on時如果目前sql出錯,復原整個事務,為off時如果sql出錯復原目前sql語句,其它語句照常運作讀寫資料庫。
需要注意的時:xact_abort隻對運作時出現的錯誤有用,如果sql語句存在編譯時錯誤,那麼他就失靈啦。
delete lives --清空資料
set xact_abort off
begin tran
--語句正确
insert into lives (Eat,Play,Numb) values ('豬肉','足球',1)
--Numb為int類型,出錯,如果1234..那個大資料換成'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時,結果集為空,因為運作是資料過大溢出出錯,復原整個事務。
事務把死鎖給整出來啦
跟着做:打開兩個查詢視窗,把下面的語句,分别放入2個查詢視窗,在5秒内運作2個事務子產品。
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秒執行下面的語句
update lives set play='羽毛球'
commit tran
select * from lives
select * from Earth
為什麼呢,下面我們看看鎖,什麼是鎖。
并發事務成敗皆歸于鎖——鎖定
在多使用者都用事務同時通路同一個資料資源的情況下,就會造成以下幾種資料錯誤。
- 更新丢失:多個使用者同時對一個資料資源進行更新,必定會産生被覆寫的資料,造成資料讀寫異常。
- 不可重複讀:如果一個使用者在一個事務中多次讀取一條資料,而另外一個使用者則同時更新啦這條資料,造成第一個使用者多次讀取資料不一緻。
- 髒讀:第一個事務讀取第二個事務正在更新的資料表,如果第二個事務還沒有更新完成,那麼第一個事務讀取的資料将是一半為更新過的,一半還沒更新過的資料,這樣的資料毫無意義。
- 幻讀:第一個事務讀取一個結果集後,第二個事務,對這個結果集經行增删操作,然而第一個事務中再次對這個結果集進行查詢時,資料發現丢失或新增。
然而鎖定,就是為解決這些問題所生的,他的存在使得一個事務對他自己的資料塊進行操作的時候,而另外一個事務則不能插足這些資料塊。這就是所謂的鎖定。
鎖定從資料庫系統的角度大緻可以分為6種:
- 共享鎖(S):還可以叫他讀鎖。可以并發讀取資料,但不能修改資料。也就是說當資料資源上存在共享鎖的時候,所有的事務都不能對這個資源進行修改,直到資料讀取完成,共享鎖釋放。
- 排它鎖(X):還可以叫他獨占鎖、寫鎖。就是如果你對資料資源進行增删改操作時,不允許其它任何事務操作這塊資源,直到排它鎖被釋放,防止同時對同一資源進行多重操作。
- 更新鎖(U):防止出現死鎖的鎖模式,兩個事務對一個資料資源進行先讀取在修改的情況下,使用共享鎖和排它鎖有時會出現死鎖現象,而使用更新鎖則可以避免死鎖的出現。資源的更新鎖一次隻能配置設定給一個事務,如果需要對資源進行修改,更新鎖會變成排他鎖,否則變為共享鎖。
- 意向鎖:SQL Server需要在層次結構中的底層資源上(如行,列)擷取共享鎖,排它鎖,更新鎖。例如表級放置了意向共享鎖,就表示事務要對表的頁或行上使用共享鎖。在表的某一行上上放置意向鎖,可以防止其它事務擷取其它不相容的的鎖。意向鎖可以提高性能,因為資料引擎不需要檢測資源的每一列每一行,就能判斷是否可以擷取到該資源的相容鎖。意向鎖包括三種類型:意向共享鎖(IS),意向排他鎖(IX),意向排他共享鎖(SIX)。
- 架構鎖:防止修改表結構時,并發通路的鎖。
- 大容量更新鎖:允許多個線程将大容量資料并發的插入到同一個表中,在加載的同時,不允許其它程序通路該表。
這些鎖之間的互相相容性,也就是,是否可以同時存在。
現有的授權模式 | ||||||
---|---|---|---|---|---|---|
請求的模式 | IS | S | U | IX | SIX | X |
意向共享 (IS) | 是 | 否 | ||||
共享 (S) | ||||||
更新 (U) | ||||||
意向排他 (IX) | ||||||
意向排他共享 (SIX) | ||||||
排他 (X) |
鎖相容性具體參見:http://msdn.microsoft.com/zh-cn/library/ms186396.aspx
鎖粒度和層次結構參見:http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx
死鎖
什麼是死鎖,為什麼會産生死鎖。我用 “事務把死鎖給整出來啦” 标題下的兩個事務産生的死鎖來解釋應該會更加生動形象點。
例子是這樣的:
第一個事務(稱為A):先更新lives表 --->>停頓5秒---->>更新earth表
第二個事務(稱為B):先更新earth表--->>停頓5秒---->>更新lives表
先執行事務A----5秒之内---執行事務B,出現死鎖現象。
過程是這樣子的:
- A更新lives表,請求lives的排他鎖,成功。
- B更新earth表,請求earth的排他鎖,成功。
- 5秒過後
- A更新earth,請求earth的排它鎖,由于B占用着earth的排它鎖,等待。
- B更新lives,請求lives的排它鎖,由于A占用着lives的排它鎖,等待。
這樣互相等待對方釋放資源,造成資源讀寫擁擠堵塞的情況,就被稱為死鎖現象,也叫做阻塞。而為什麼會産生,上例就列舉出來啦。
然而資料庫并沒有出現無限等待的情況,是因為資料庫搜尋引擎會定期檢測這種狀況,一旦發現有情況,立馬選擇一個事務作為犧牲品。犧牲的事務,将會復原資料。有點像兩個人在過獨木橋,兩個無腦的人都走在啦獨木橋中間,如果不落水,必定要有一個人給退回來。這種互相等待的過程,是一種耗時耗資源的現象,是以能避則避。
哪個人會被退回來,作為犧牲品,這個我們是可以控制的。控制文法:
set deadlock_priority <級别>
死鎖處理的優先級别為 low<normal<high,不指定的情況下預設為normal,犧牲品為随機。如果指定,犧牲品為級别低的。
還可以使用數字來處理辨別級别:-10到-5為low,-5為normal,-5到10為high。
減少死鎖的發生,提高資料庫性能
死鎖耗時耗資源,然而在大型資料庫中,高并發帶來的死鎖是不可避免的,是以我們隻能讓其變的更少。
- 按照同一順序通路資料庫資源,上述例子就不會發生死鎖啦
- 保持是事務的簡短,盡量不要讓一個事務處理過于複雜的讀寫操作。事務過于複雜,占用資源會增多,處理時間增長,容易與其它事務沖突,提升死鎖機率。
- 盡量不要在事務中要求使用者響應,比如修改新增資料之後在完成整個事務的送出,這樣延長事務占用資源的時間,也會提升死鎖機率。
- 盡量減少資料庫的并發量。
- 盡可能使用分區表,分區視圖,把資料放置在不同的磁盤和檔案組中,分散通路儲存在不同分區的資料,減少因為表中放置鎖而造成的其它事務長時間等待。
- 避免占用時間很長并且關系表複雜的資料操作。
- 使用較低的隔離級别,使用較低的隔離級别比使用較高的隔離級别持有共享鎖的時間更短。這樣就減少了鎖争用。
可參考:http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx
檢視鎖活動情況:
--檢視鎖活動情況
select * from sys.dm_tran_locks
--檢視事務活動情況
dbcc opentran
可參考:http://msdn.microsoft.com/zh-cn/library/ms190345.aspx
為事務設定隔離級别
所謂事物隔離級别,就是并發事務對同一資源的讀取深度層次。分為5種。
- read uncommitted:這個隔離級别最低啦,可以讀取到一個事務正在處理的資料,但事務還未送出,這種級别的讀取叫做髒讀。
- read committed:這個級别是預設選項,不能髒讀,不能讀取事務正在處理沒有送出的資料,但能修改。
- repeatable read:不能讀取事務正在處理的資料,也不能修改事務處理資料前的資料。
- snapshot:指定事務在開始的時候,就獲得了已經送出資料的快照,是以目前事務隻能看到事務開始之前對資料所做的修改。
- serializable:最高事務隔離級别,隻能看到事務處理之前的資料。
--文法
set tran isolation level <級别>
read uncommitted隔離級别的例子:
begin tran
set deadlock_priority low
update Earth set Animal='老虎'
waitfor delay '0:0:5' --等待5秒執行下面的語句
rollback tran
開另外一個查詢視窗執行下面語句
set tran isolation level read uncommitted
select * from Earth --讀取的資料為正在修改的資料 ,髒讀
waitfor delay '0:0:5' --5秒之後資料已經復原
select * from Earth --復原之後的資料
read committed隔離級别的例子:
begin tran
update Earth set Animal='老虎'
waitfor delay '0:0:10' --等待5秒執行下面的語句
rollback tran
set tran isolation level read committed
select * from Earth ---擷取不到老虎,不能髒讀
update Earth set Animal='猴子1' --可以修改
waitfor delay '0:0:10' --10秒之後上一個事務已經復原
select * from Earth --修改之後的資料,而不是猴子
剩下的幾個級别,不一一列舉啦,自己了解吧。
設定鎖逾時時間
發生死鎖的時候,資料庫引擎會自動檢測死鎖,解決問題,然而這樣子是很被動,隻能在發生死鎖後,等待處理。
然而我們也可以主動出擊,設定鎖逾時時間,一旦資源被鎖定阻塞,超過設定的鎖定時間,阻塞語句自動取消,釋放資源,報1222錯誤。
好東西一般都具有兩面性,調優的同時,也有他的不足之處,那就是一旦超過時間,語句取消,釋放資源,但是目前報錯事務,不會復原,會造成資料錯誤,你需要在程式中捕獲1222錯誤,用程式處理目前事務的邏輯,使資料正确。
--檢視逾時時間,預設為-1
select @@lock_timeout
--設定逾時時間
set lock_timeout 0 --為0時,即為一旦發現資源鎖定,立即報錯,不在等待,目前事務不復原,設定時間需謹慎處理後事啊,你hold不住的。
檢視與殺死鎖和程序
--檢測死鎖
--如果發生死鎖了,我們怎麼去檢測具體發生死鎖的是哪條SQL語句或存儲過程?
--這時我們可以使用以下存儲過程來檢測,就可以查出引起死鎖的程序和SQL語句。SQL Server自帶的系統存儲過程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
-- 找到臨時表的記錄數
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '現在沒有阻塞和死鎖資訊' as message
-- 循環開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起資料庫死鎖的是: '+ CAST(@bl AS VARCHAR(10)) + '程序号,其執行的SQL文法如下'
else
select '程序号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '程序号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其目前程序執行的SQL文法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循環指針下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
--殺死鎖和程序
--如何去手動的殺死程序和鎖?最簡單的辦法,重新啟動服務。但是這裡要介紹一個存儲過程,通過顯式的調用,可以殺死程序和鎖。
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) --要關閉程序的資料庫名
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'
--檢視鎖資訊
--如何檢視系統中所有鎖的詳細資訊?在企業管理管理器中,我們可以看到一些程序和鎖的資訊,這裡介紹另外一種方法。
--檢視鎖資訊
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 程序id=a.req_spid
,資料庫=db_name(rsc_dbid)
,類型=case rsc_type when 1 then 'NULL 資源(未使用)'
when 2 then '資料庫'
when 3 then '檔案'
when 4 then '索引'
when 5 then '表'
when 6 then '頁'
when 7 then '鍵'
when 8 then '擴充盤區'
when 9 then 'RID(行 ID)'
when 10 then '應用程式'
end
,對象id=rsc_objid
,對象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
go
drop table #t
仔細閱讀,希望能分享給你一點點東西,謝謝,over。