SQL Server中的事务与锁
了解事務(wù)和鎖
事務(wù)：保持邏輯數(shù)據(jù)一致性與可恢復(fù)性，必不可少的利器。
鎖：多用戶訪問同一數(shù)據(jù)庫資源時，對訪問的先后次序權(quán)限管理的一種機制，沒有他事務(wù)或許將會一塌糊涂，不能保證數(shù)據(jù)的安全正確讀寫。
死鎖：是數(shù)據(jù)庫性能的重量級殺手之一，而死鎖卻是不同事務(wù)之間搶占數(shù)據(jù)資源造成的。
不懂的聽上去，挺神奇的，懂的感覺我在扯淡，下面帶你好好領(lǐng)略下他們的風(fēng)采，嗅査下他們的狂騷。。
先說事務(wù)--概念，分類
用華仔無間道中的一句來給你詮釋下：去不了終點，回到原點。
舉例說明：
在一個事務(wù)中，你寫啦2條sql語句，一條是修改訂單表狀態(tài),一條是修改庫存表庫存-1 。 如果在修改訂單表狀態(tài)的時候出錯，事務(wù)能夠回滾，數(shù)據(jù)將恢復(fù)到?jīng)]修改之前的數(shù)據(jù)狀態(tài)，下面的修改庫存也就不執(zhí)行，這樣確保你關(guān)系邏輯的一致，安全。。
事務(wù)就是這個樣子，倔脾氣，要么全部執(zhí)行，要么全部不執(zhí)行，回到原數(shù)據(jù)狀態(tài)。
書面解釋：事務(wù)具有原子性，一致性，隔離性，持久性。
- 原子性：事務(wù)必須是一個自動工作的單元，要么全部執(zhí)行，要么全部不執(zhí)行。
- 一致性：事務(wù)結(jié)束的時候，所有的內(nèi)部數(shù)據(jù)都是正確的。
- 隔離性：并發(fā)多個事務(wù)時，各個事務(wù)不干涉內(nèi)部數(shù)據(jù)，處理的都是另外一個事務(wù)處理之前或之后的數(shù)據(jù)。
- 持久性：事務(wù)提交之后，數(shù)據(jù)是永久性的，不可再回滾。
然而在SQL Server中事務(wù)被分為3類常見的事務(wù)：
- 自動提交事務(wù)：是SQL Server默認的一種事務(wù)模式，每條Sql語句都被看成一個事務(wù)進行處理，你應(yīng)該沒有見過，一條Update 修改2個字段的語句，只修該了1個字段而另外一個字段沒有修改。。
- 顯式事務(wù)：T-sql標明，由Begin Transaction開啟事務(wù)開始，由Commit Transaction 提交事務(wù)、Rollback Transaction 回滾事務(wù)結(jié)束。
- 隱式事務(wù)：使用Set IMPLICIT_TRANSACTIONS ON 將將隱式事務(wù)模式打開，不用Begin Transaction開啟事務(wù)，當(dāng)一個事務(wù)結(jié)束，這個模式會自動啟用下一個事務(wù)，只用Commit Transaction 提交事務(wù)、Rollback Transaction 回滾事務(wù)即可。
顯式事務(wù)的應(yīng)用
常用語句就四個。
- Begin Transaction：標記事務(wù)開始。
- Commit Transaction：事務(wù)已經(jīng)成功執(zhí)行，數(shù)據(jù)已經(jīng)處理妥當(dāng)。
- Rollback Transaction：數(shù)據(jù)處理過程中出錯，回滾到?jīng)]有處理之前的數(shù)據(jù)狀態(tài)，或回滾到事務(wù)內(nèi)部的保存點。
- Save Transaction：事務(wù)內(nèi)部設(shè)置的保存點，就是事務(wù)可以不全部回滾，只回滾到這里，保證事務(wù)內(nèi)部不出錯的前提下。
上面的都是心法，下面的給你來個招式，要看仔細啦。
1 ---開啟事務(wù)2 begin tran3 --錯誤撲捉機制，看好啦，這里也有的。并且可以嵌套。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 , --錯誤狀態(tài)碼 16 Error_Procedure() as ErrorProcedure , --出現(xiàn)錯誤的存儲過程或觸發(fā)器的名稱。 17 Error_line() as ErrorLine, --發(fā)生錯誤的行號 18 Error_message() as ErrorMessage --錯誤的具體信息 19 if(@@trancount>0) --全局變量@@trancount，事務(wù)開啟此值+1，他用來判斷是有開啟事務(wù) 20 rollback tran ---由于出錯，這里回滾到開始，第一條語句也沒有插入成功。 21 end catch 22 if(@@trancount>0) 23 commit tran --如果成功Lives表中，將會有3條數(shù)據(jù)。 24 25 --表本身為空表，ID ,Numb為int 類型，其它為nvarchar類型 26 select * from lives?
---開啟事務(wù) 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 catchselect Error_number() as ErrorNumber, --錯誤代碼Error_severity() as ErrorSeverity, --錯誤嚴重級別，級別小于10 try catch 捕獲不到Error_state() as ErrorState , --錯誤狀態(tài)碼Error_Procedure() as ErrorProcedure , --出現(xiàn)錯誤的存儲過程或觸發(fā)器的名稱。Error_line() as ErrorLine, --發(fā)生錯誤的行號Error_message() as ErrorMessage --錯誤的具體信息if(@@trancount>0) --全局變量@@trancount，事務(wù)開啟此值+1，他用來判斷是有開啟事務(wù)rollback tran ---由于出錯，這里回滾事務(wù)到原點，第一條語句也沒有插入成功。 end catch if(@@trancount>0) rollback tran pigOneIn --如果成功Lives表中，將會有3條數(shù)據(jù)。--表本身為空表，ID ,Numb為int 類型，其它為nvarchar類型 select * from lives使用set xact_abort
設(shè)置 xact_abort on/off , 指定是否回滾當(dāng)前事務(wù)，為on時如果當(dāng)前sql出錯，回滾整個事務(wù)，為off時如果sql出錯回滾當(dāng)前sql語句，其它語句照常運行讀寫數(shù)據(jù)庫。
?需要注意的時：xact_abort只對運行時出現(xiàn)的錯誤有用，如果sql語句存在編譯時錯誤，那么他就失靈啦。
delete lives --清空數(shù)據(jù) set xact_abort off begin tran --語句正確insert into lives (Eat,Play,Numb) values ('豬肉','足球',1) --Numb為int類型，出錯,如果1234..那個大數(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時，結(jié)果集為空，因為運行是數(shù)據(jù)過大溢出出錯，回滾整個事務(wù)。
事務(wù)把死鎖給整出來啦
跟著做：打開兩個查詢窗口，把下面的語句，分別放入2個查詢窗口，在5秒內(nèi)運行2個事務(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ù)據(jù)資源的情況下，就會造成以下幾種數(shù)據(jù)錯誤。
- 更新丟失：多個用戶同時對一個數(shù)據(jù)資源進行更新，必定會產(chǎn)生被覆蓋的數(shù)據(jù)，造成數(shù)據(jù)讀寫異常。
- 不可重復(fù)讀：如果一個用戶在一個事務(wù)中多次讀取一條數(shù)據(jù)，而另外一個用戶則同時更新啦這條數(shù)據(jù)，造成第一個用戶多次讀取數(shù)據(jù)不一致。
- 臟讀：第一個事務(wù)讀取第二個事務(wù)正在更新的數(shù)據(jù)表，如果第二個事務(wù)還沒有更新完成，那么第一個事務(wù)讀取的數(shù)據(jù)將是一半為更新過的，一半還沒更新過的數(shù)據(jù)，這樣的數(shù)據(jù)毫無意義。
- 幻讀：第一個事務(wù)讀取一個結(jié)果集后，第二個事務(wù)，對這個結(jié)果集經(jīng)行增刪操作，然而第一個事務(wù)中再次對這個結(jié)果集進行查詢時，數(shù)據(jù)發(fā)現(xiàn)丟失或新增。
然而鎖定，就是為解決這些問題所生的，他的存在使得一個事務(wù)對他自己的數(shù)據(jù)塊進行操作的時候，而另外一個事務(wù)則不能插足這些數(shù)據(jù)塊。這就是所謂的鎖定。
鎖定從數(shù)據(jù)庫系統(tǒng)的角度大致可以分為6種：
- 共享鎖（S）：還可以叫他讀鎖。可以并發(fā)讀取數(shù)據(jù)，但不能修改數(shù)據(jù)。也就是說當(dāng)數(shù)據(jù)資源上存在共享鎖的時候，所有的事務(wù)都不能對這個資源進行修改，直到數(shù)據(jù)讀取完成，共享鎖釋放。
- 排它鎖（X）：還可以叫他獨占鎖、寫鎖。就是如果你對數(shù)據(jù)資源進行增刪改操作時，不允許其它任何事務(wù)操作這塊資源，直到排它鎖被釋放，防止同時對同一資源進行多重操作。
- 更新鎖（U）：防止出現(xiàn)死鎖的鎖模式，兩個事務(wù)對一個數(shù)據(jù)資源進行先讀取在修改的情況下，使用共享鎖和排它鎖有時會出現(xiàn)死鎖現(xiàn)象，而使用更新鎖則可以避免死鎖的出現(xiàn)。資源的更新鎖一次只能分配給一個事務(wù)，如果需要對資源進行修改，更新鎖會變成排他鎖，否則變?yōu)楣蚕礞i。
- 意向鎖：SQL Server需要在層次結(jié)構(gòu)中的底層資源上（如行，列）獲取共享鎖，排它鎖，更新鎖。例如表級放置了意向共享鎖，就表示事務(wù)要對表的頁或行上使用共享鎖。在表的某一行上上放置意向鎖，可以防止其它事務(wù)獲取其它不兼容的的鎖。意向鎖可以提高性能，因為數(shù)據(jù)引擎不需要檢測資源的每一列每一行，就能判斷是否可以獲取到該資源的兼容鎖。意向鎖包括三種類型：意向共享鎖（IS），意向排他鎖（IX），意向排他共享鎖（SIX）。
- 架構(gòu)鎖：防止修改表結(jié)構(gòu)時，并發(fā)訪問的鎖。
- 大容量更新鎖：允許多個線程將大容量數(shù)據(jù)并發(fā)的插入到同一個表中，在加載的同時，不允許其它進程訪問該表。
這些鎖之間的相互兼容性，也就是，是否可以同時存在。?
| 請求的模式 | IS | S | U | IX | SIX | X |
| 意向共享 (IS) | 是 | 是 | 是 | 是 | 是 | 否 |
| 共享 (S) | 是 | 是 | 是 | 否 | 否 | 否 |
| 更新 (U) | 是 | 是 | 否 | 否 | 否 | 否 |
| 意向排他 (IX) | 是 | 否 | 否 | 是 | 否 | 否 |
| 意向排他共享 (SIX) | 是 | 否 | 否 | 否 | 否 | 否 |
| 排他 (X) | 否 | 否 | 否 | 否 | 否 | 否 |
鎖兼容性具體參見：http://msdn.microsoft.com/zh-cn/library/ms186396.aspx
鎖粒度和層次結(jié)構(gòu)參見：http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx
?死鎖
什么是死鎖，為什么會產(chǎn)生死鎖。我用 “事務(wù)把死鎖給整出來啦” 標題下的兩個事務(wù)產(chǎn)生的死鎖來解釋應(yīng)該會更加生動形象點。
例子是這樣的：
第一個事務(wù)（稱為A）：先更新lives表 --->>停頓5秒---->>更新earth表
第二個事務(wù)（稱為B）：先更新earth表--->>停頓5秒---->>更新lives表
先執(zhí)行事務(wù)A----5秒之內(nèi)---執(zhí)行事務(wù)B，出現(xiàn)死鎖現(xiàn)象。
過程是這樣子的：
這樣相互等待對方釋放資源，造成資源讀寫擁擠堵塞的情況，就被稱為死鎖現(xiàn)象，也叫做阻塞。而為什么會產(chǎn)生，上例就列舉出來啦。
然而數(shù)據(jù)庫并沒有出現(xiàn)無限等待的情況，是因為數(shù)據(jù)庫搜索引擎會定期檢測這種狀況，一旦發(fā)現(xiàn)有情況，立馬選擇一個事務(wù)作為犧牲品。犧牲的事務(wù)，將會回滾數(shù)據(jù)。有點像兩個人在過獨木橋，兩個無腦的人都走在啦獨木橋中間，如果不落水，必定要有一個人給退回來。這種相互等待的過程，是一種耗時耗資源的現(xiàn)象，所以能避則避。
哪個人會被退回來，作為犧牲品，這個我們是可以控制的。控制語法：
set deadlock_priority <級別>死鎖處理的優(yōu)先級別為 low<normal<high，不指定的情況下默認為normal，犧牲品為隨機。如果指定，犧牲品為級別低的。
還可以使用數(shù)字來處理標識級別：-10到-5為low，-5為normal，-5到10為high。
減少死鎖的發(fā)生，提高數(shù)據(jù)庫性能
死鎖耗時耗資源，然而在大型數(shù)據(jù)庫中，高并發(fā)帶來的死鎖是不可避免的，所以我們只能讓其變的更少。
可參考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx
查看鎖活動情況：
--查看鎖活動情況 select * from sys.dm_tran_locks --查看事務(wù)活動情況 dbcc opentran可參考：http://msdn.microsoft.com/zh-cn/library/ms190345.aspx
為事務(wù)設(shè)置隔離級別
所謂事物隔離級別，就是并發(fā)事務(wù)對同一資源的讀取深度層次。分為5種。
- read uncommitted：這個隔離級別最低啦，可以讀取到一個事務(wù)正在處理的數(shù)據(jù)，但事務(wù)還未提交，這種級別的讀取叫做臟讀。
- read committed：這個級別是默認選項，不能臟讀，不能讀取事務(wù)正在處理沒有提交的數(shù)據(jù)，但能修改。
- repeatable read：不能讀取事務(wù)正在處理的數(shù)據(jù)，也不能修改事務(wù)處理數(shù)據(jù)前的數(shù)據(jù)。
- snapshot：指定事務(wù)在開始的時候，就獲得了已經(jīng)提交數(shù)據(jù)的快照，因此當(dāng)前事務(wù)只能看到事務(wù)開始之前對數(shù)據(jù)所做的修改。
- serializable：最高事務(wù)隔離級別，只能看到事務(wù)處理之前的數(shù)據(jù)。?
read uncommitted隔離級別的例子：
begin tran set deadlock_priority lowupdate Earth set Animal='老虎' waitfor delay '0:0:5' --等待5秒執(zhí)行下面的語句 rollback tran開另外一個查詢窗口執(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隔離級別的例子：
begin tran update Earth set Animal='老虎' waitfor delay '0:0:10' --等待5秒執(zhí)行下面的語句 rollback tran set tran isolation level read committed select * from Earth ---獲取不到老虎，不能臟讀 update Earth set Animal='猴子1' --可以修改 waitfor delay '0:0:10' --10秒之后上一個事務(wù)已經(jīng)回滾 select * from Earth --修改之后的數(shù)據(jù)，而不是猴子?
剩下的幾個級別，不一一列舉啦，自己理解吧。
設(shè)置鎖超時時間
發(fā)生死鎖的時候，數(shù)據(jù)庫引擎會自動檢測死鎖，解決問題，然而這樣子是很被動，只能在發(fā)生死鎖后，等待處理。
然而我們也可以主動出擊，設(shè)置鎖超時時間，一旦資源被鎖定阻塞，超過設(shè)置的鎖定時間，阻塞語句自動取消，釋放資源，報1222錯誤。
好東西一般都具有兩面性，調(diào)優(yōu)的同時，也有他的不足之處，那就是一旦超過時間，語句取消，釋放資源，但是當(dāng)前報錯事務(wù)，不會回滾，會造成數(shù)據(jù)錯誤，你需要在程序中捕獲1222錯誤，用程序處理當(dāng)前事務(wù)的邏輯，使數(shù)據(jù)正確。
--查看超時時間,默認為-1 select @@lock_timeout --設(shè)置超時時間 set lock_timeout 0 --為0時，即為一旦發(fā)現(xiàn)資源鎖定，立即報錯，不在等待，當(dāng)前事務(wù)不回滾，設(shè)置時間需謹慎處理后事啊，你hold不住的。查看與殺死鎖和進程
--檢測死鎖 --如果發(fā)生死鎖了，我們怎么去檢測具體發(fā)生死鎖的是哪條SQL語句或存儲過程？ --這時我們可以使用以下存儲過程來檢測，就可以查出引起死鎖的進程和SQL語句。SQL Server自帶的系統(tǒng)存儲過程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 intcreate 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 <= @intCountProperties begin -- 取第一條記錄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)) +'阻塞,其當(dāng)前進程執(zhí)行的SQL語法如下'DBCC INPUTBUFFER (@bl )end -- 循環(huán)指針下移set @intCounter = @intCounter + 1 enddrop table #tmp_lock_whoreturn 0 end--殺死鎖和進程 --如何去手動的殺死進程和鎖？最簡單的辦法，重新啟動服務(wù)。但是這里要介紹一個存儲過程，通過顯式的調(diào)用，可以殺死進程和鎖。use master goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_killspid] GOcreate proc p_killspid @dbname varchar(200) --要關(guān)閉進程的數(shù)據(jù)庫名 as declare @sql nvarchar(500) declare @spid nvarchar(20)declare #tb cursor forselect spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)open #tbfetch next from #tb into @spidwhile @@fetch_status=0begin exec('kill '+@spid)fetch next from #tb into @spidend close #tbdeallocate #tb go--用法 exec p_killspid 'newdbpy' --查看鎖信息 --如何查看系統(tǒng)中所有鎖的詳細信息？在企業(yè)管理管理器中，我們可以看到一些進程和鎖的信息，這里介紹另外一種方法。 --查看鎖信息 create table #t(req_spid int,obj_name sysname)declare @s nvarchar(4000),@rid int,@dbname sysname,@id int,@objname sysnamedeclare tb cursor for select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objidfrom master..syslockinfo where rsc_type in(4,5) open tb fetch next from tb into @rid,@dbname,@id while @@fetch_status=0 beginset @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@idinsert into #t values(@rid,@objname)fetch next from tb into @rid,@dbname,@id end close tb deallocate tbselect 進程id=a.req_spid,數(shù)據(jù)庫=db_name(rsc_dbid),類型=case rsc_type when 1 then 'NULL 資源（未使用）'when 2 then '數(shù)據(jù)庫'when 3 then '文件'when 4 then '索引'when 5 then '表'when 6 then '頁'when 7 then '鍵'when 8 then '擴展盤區(qū)'when 9 then 'RID（行 ID)'when 10 then '應(yīng)用程序'end,對象id=rsc_objid,對象名=b.obj_name,rsc_indidfrom master..syslockinfo a left join #t b on a.req_spid=b.req_spidgo drop table #t來源：https://www.cnblogs.com/knowledgesea/p/3714417.html
轉(zhuǎn)載于:https://www.cnblogs.com/littlewrong/p/10405355.html
總結(jié)
以上是生活随笔為你收集整理的SQL Server中的事务与锁的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 可持久化线段树——主席树
- 下一篇： pycharm 中按照文档引包方式，引包