了解事務和鎖
事務：保持邏輯資料一緻性與可恢複性，必不可少的利器。
鎖：多使用者通路同一資料庫資源時，對通路的先後次序權限管理的一種機制，沒有他事務或許将會一塌糊塗，不能保證資料的安全正确讀寫。
死鎖：是資料庫性能的重量級殺手之一，而死鎖卻是不同僚務之間搶占資料資源造成的。
不懂的聽上去，挺神奇的，懂的感覺我在扯淡，下面帶你好好領略下他們的風采，嗅査下他們的狂騷。。
先說事務--概念，分類
用華仔無間道中的一句來給你诠釋下：去不了終點，回到原點。
舉例說明：
在一個事務中，你寫啦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 
---開啟事務
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不住的。 仔細閱讀，希望能分享給你一點點東西，謝謝，over。
分類: 資料庫
标簽: sql事務, sql鎖, sql死鎖