SQL Server 事務

2、T-SQL查詢語句 詳解事務及鎖

                                           設計者：楊文

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

SQL Server 通過支持事務機制以保證數據的一致性。

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

1、首先，需要創建賬戶表bank，存放用戶張三和李四的賬戶信息。我們假設表結構如圖

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

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

如圖表示：

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

如圖

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

如果其中任何一條出現錯誤，則整個轉賬業務也應取消，兩個賬戶中的余額應恢復到原來的數據，從而確保轉賬

前和轉賬后的余額總和不變，即1001元。

5、理論：

a、什么是事務？

概念

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

b、屬性

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

一致性：當事務完成時，數據必須處于一致狀態。

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

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

6、如何執行事務

a、執行事務的語法

開始事務：BEGIN TRANSACTION

提交事務：COMMIT TRANSACTION

回滾（撤銷）事務：ROLLBACK TRANSACTION

7、事務應用實例

下面我們來應用事務來解決上述轉賬問題，T=SQL語句如下。

print '查看轉賬事務前的余額'

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 '查看轉賬事務過程中的余額'

select * from bank 

if @errorsum <>0

begin

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

rollback transaction

end

else

begin

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

commit transaction 

end 

go

print '查看轉賬事務后的余額'

select * from bank 

go 

交易失敗：

交易成功：

8、

認識鎖

什么是鎖

用于多用戶環境下保證數據庫完整性和一致性。

鎖的模式

共享鎖（S鎖）

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

排他鎖（X鎖）

用于數據修改。與其他任何鎖都不兼容。

更新鎖（U鎖）

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

如何查看鎖

使用sys.dm_tran_locks動態管理視圖

使用Profiler來捕捉鎖信息

9、認識死鎖          

死鎖形成的條件：

互斥條件

請求和等待條件

不剝奪條件

環路等待條件

預防死鎖：

破壞互斥條件

破壞請求和等待條件

破壞不剝奪條件