新增查詢, 執行下列指令:
print @@SPID
得到連線id 55
2.去"活動監視器"
3.接著回到查詢部分，下以下指令
begin transaction
select * from Customers
where CompanyName Like 'B%'
print 'trans count: ' + Convert(varchar(4), @@TranCount)//印出目前有幾筆交易，因為SQL沒有自動轉字串功能所以要將int的TranCount轉成字串。
4.結束交易
commit transaction
print 'trans count: ' + Convert(varchar(4), @@TranCount)
(PS:如果commit transaction前面有指令錯誤的話，SQL會強制取消交易，這時再印TranCount會是零)
(PS:如果有多筆交易，執行一次commit transaction只會結束掉一筆交易)
到這裡可以知道所謂的交易就是由begin transaction .....commit transaction包住的指令。而且還沒下達commit transaction指令，表示transaction尚未結束。
體驗交易處理的完整性與可回復性
5.
begin transaction
update Customers
set CompanyName = 'Bottom-Dollar Markets 123'
where CustomerID = 'BOTTM'//update 資料
select * from Customers
where CompanyName Like 'B%'
此時發現 BOTTM的CompanyName的值已經變成Bottom-Dollar Markets 123
6.接著打上
rollback transaction
//這是取消交易的指令，並且回復到交易前的狀態
select * from Customers
where CompanyName Like 'B%'
此時，BOTTM的CompanyName的值又變回Bottom-Dollar Markets
//切記已經commit的交易就無法再rollback了
Rollback原理:
利用一個暫時交易紀錄檔來回復異動資料，在transaction開始時,SQLserver會將要更改的資料一 鎖定並且進行更改，同時也會建立一個暫時交易紀錄檔，來存放交易中更改資料的過程及內容。
交易一旦被commit時，則會將暫時交易紀錄檔的內容儲存到資料庫的log檔中，使該項交易異動視為已完成。因此無法rollback回去了。除非使用備份還原功能。
體驗交易的隔離性(Isolation):
一、隔離等級為:read commted
1.開兩個查詢視窗
2.在第一個視窗(spid=55)
set transaction isolation level read committed
begin transaction
select * from Customers
where CompanyName Like 'B%'
3.在第二個視窗(spid=58)
use northwind
update Customers
set CompanyName = 'Blauer See Delikatessen 123'
where CustomerID = 'BLAUS'
之後下指令select CompanyName from Customers where CustomerID='BLAUS'
可以知道在第一視窗進行隔離等級為read commited的交易之下
第二視窗仍可以更動資料，資料更新成功
4. 接著去第一視窗執行:
update Customers
set CompanyName = 'Bottom-Dollar Markets 123'
where CustomerID = 'BOTTM'
5.之後去第二視窗執行
select CompanyName from Customers where CustomerID='BOTTM'
這時候無法查詢到資料，因為第一視窗仍在交易進行中，將資料給鎖住(lock)。
打開活動監視器看處理序發現spid為55的源頭封鎖者的值為1，表示第一視窗將資料給鎖住。
再看看id為58的欄位，等候類型的值LCK_M_S ，封鎖者的值是55。
6.去第一視窗打上
rollback tran
這時第二視窗已經秀出查詢結果了。
read commited:第一視窗不允許第二視窗讀取(select)第一視窗正在更新(update)且尚未commit的資料，因此該資料後來被更動的機率就比較小(因為第二視窗等第一視窗更動完再去select)，且第二視窗不會讀到交易尚未完成的資料，不過讀完資料後，無法得知該資料是否還會有人來更改。第一視窗在交易過程中，第二視窗仍可以更新(update)其他筆資料，因此同一個交易中第一視窗如有兩次以上select，每次select結果可能不盡相同。
二、隔離等級為repeatable read
1.重複以上1~6步驟再做一次, 但是第一視窗隔離等級改用repeatable read,
( 差別在於 repeatable read )
set transaction isolation level repeatable read
2.
我們發現在第三步驟時，第二視窗無法更新資料，被鎖定了。
打開活動監視器看處理序發現spid為55的源頭封鎖者的值為1，表示第一視窗將資料給鎖住。
再看看id為58的欄位，等候類型的值LCK_M_X ，封鎖者的值是55。
在交易中，第一視窗讀取(select)到的資料將不允許別人更改或刪除。如此保證第一視窗在交易中每次都可以讀取到相同的內容。但別人(第二視窗仍可以在該資料表insert資料，所以交易過程中如有兩次以上select時候，可能會突然多出一兩筆記錄來。這就是repeatable read的定義。
別忘了最後一步去第一視窗執行
rollback tran
三、隔離等級:Serializable
__________________________________________________________________
再度體會 Transaction 的「隔離性」
-- SERIALIZABLE 如何預防 "phantom data"
1.第一個視窗執行以下指令:
set transaction isolation level SERIALIZABLE//記得要改isolation level
begin transaction
select * from Customers
where CompanyName Like 'B%'
請記住where條件
2. 在第二視窗執行以下指令:
update Customers
set CompanyName = 'Bottom-Dollar Markets 123'
where CustomerID = 'CHOPS'
select * from Customers
where CustomerID Like 'C%'
注意:這邊where條件跟第一視窗已經不同，
第二視窗是更新C開頭的某筆資料
並查詢C開頭的紀錄
3.發現這時第一視窗將資料給lock住了
打開活動監視器結果跟read committed一樣
看處理序發現spid為55的源頭封鎖者的值為1，表示第一視窗將資料給鎖住。
再看看id為58的欄位，等候類型的值LCK_M_X ，封鎖者的值是55。
Serializable:第一視窗會將整個資料表鎖定，不允許別人insert,delete,update資料。其他人必須排隊(serial)等第一視窗交易完畢才可以使用這個資料表。因此serializable的並行性最低。
4.記得去第一視窗執行
rollback tran
心得:實做一遍之後對於交易有概念了，光看書根本無法吸收，不夠具體無法想像。
沒有留言:
張貼留言