2、T-SQL查詢語句 詳解事務(wù)及鎖

2、T-SQL查詢語句 詳解事務(wù)及鎖

                                           設(shè)計者：楊文

                                           防偽碼：千淘萬浪雖辛苦，吹盡黃沙始到金

SQL Server 通過支持事務(wù)機制以保證數(shù)據(jù)的一致性。

案例：在日常生活中，大家可能使用過銀行轉(zhuǎn)賬，銀行轉(zhuǎn)賬操作往往會涉及兩個或兩個以上的賬戶。在轉(zhuǎn)出賬戶的存款減少一定金額的同時，轉(zhuǎn)入賬戶則會增加相應(yīng)金額的存款，現(xiàn)在，假定張三的賬戶轉(zhuǎn)賬1000元到李四的賬戶。該如何實現(xiàn)呢？

1、首先，需要創(chuàng)建賬戶表bank，存放用戶張三和李四的賬戶信息。我們假設(shè)表結(jié)構(gòu)如圖

2、賬戶表中要求賬戶余額不能少于一元，所以需要為字段currentMoney設(shè)置檢查約束，設(shè)置步驟如下。

首先，打開bank表的設(shè)計窗口，在currentMoney字段上右擊，在彈出的快捷菜單中選擇CHECK約束即可，

如圖表示：

3、然后，在打開的“CHECK 約束”對話框中，單機“添加”按鈕，修改約束名稱，并編輯檢查約束表達式即可，

如圖

4、如何解決呢？使用事務(wù)，轉(zhuǎn)賬過程就是一個事務(wù)，它需要兩條UPDATE語句來完成，這兩條語句是一個整體。

如果其中任何一條出現(xiàn)錯誤，則整個轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個賬戶中的余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)賬

前和轉(zhuǎn)賬后的余額總和不變，即1001元。

5、理論：

a、什么是事務(wù)？

概念

事務(wù)是一個不可分割的工作邏輯單元，包括一組命令，這組命令要么都執(zhí)行，要么都不執(zhí)行。

b、屬性

原子性：事務(wù)是一個完整的操作。事務(wù)的各元素是不可分的。

一致性：當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)。

隔離性：事務(wù)是獨立的，它不以任何方式依賴于或影響其他事務(wù)。

持久性：一旦事務(wù)被提交，事務(wù)的效果會被永久的保留在數(shù)據(jù)庫中。

6、如何執(zhí)行事務(wù)

a、執(zhí)行事務(wù)的語法

開始事務(wù)：BEGIN TRANSACTION

提交事務(wù)：COMMIT TRANSACTION

回滾（撤銷）事務(wù)：ROLLBACK TRANSACTION

7、事務(wù)應(yīng)用實例

下面我們來應(yīng)用事務(wù)來解決上述轉(zhuǎn)賬問題，T=SQL語句如下。

print '查看轉(zhuǎn)賬事務(wù)前的余額'

select * from bank 

go

begin transaction

declare @errorsum int

set @errorsum =0

update bank set customermoney =customermoney -1000

where customername ='張三'

set @errorsum =@errorsum +@@ERROR 

update bank set customermoney =customermoney +1000

where customername ='李四'

set @errorsum =@errorsum +@@ERROR 

print '查看轉(zhuǎn)賬事務(wù)過程中的余額'

select * from bank 

if @errorsum <>0

begin

print '交易失敗,回滾事物'

rollback transaction

end

else

begin

print '交易成功,提交事務(wù),寫入鍵盤,永久保存'

commit transaction 

end 

go

print '查看轉(zhuǎn)賬事務(wù)后的余額'

select * from bank 

go 

交易失?。?br>

交易成功：

8、

認識鎖

什么是鎖

用于多用戶環(huán)境下保證數(shù)據(jù)庫完整性和一致性。

鎖的模式

共享鎖（S鎖）

用于讀取數(shù)據(jù)。擁有共享鎖的資源不能被修改。

排他鎖（X鎖）

用于數(shù)據(jù)修改。與其他任何鎖都不兼容。

更新鎖（U鎖）

用于讀取和修改。與S鎖兼容，不影響讀??；與U鎖不兼容，避免死鎖

如何查看鎖

使用sys.dm_tran_locks動態(tài)管理視圖

使用Profiler來捕捉鎖信息

9、認識死鎖          

死鎖形成的條件：

互斥條件

請求和等待條件

不剝奪條件

環(huán)路等待條件

預(yù)防死鎖：

破壞互斥條件

破壞請求和等待條件

破壞不剝奪條件

本文出自 “一盞燭光” 博客，轉(zhuǎn)載請與作者聯(lián)系！