[T-SQL]交易Transaction概念

2020-10-03 置頂文章

5209
0
Database

關於SQL - 交易的重點整理：

為什麼需要交易？
什麼是交易？
怎麼使用交易？

交易主要用來保證資料的合理性與併發處理的能力！
通俗的說，
1. 交易來保證避免資料處於一種不合理的中間狀態
例子：A匯錢給B，A帳戶會減少存款、B帳戶會增加存款，此兩者的交易必須同時成功，或同時失敗，不能存在只有一方的帳戶金額的變動(不合理的中間狀態)，利用交易的機制可以解決這個問題。

2. 交易來實現多個用戶對共享資源的同時存取(如何避免多用戶同時存取(併發訪問)不出錯)
例子1：線上售票系統，提供給多個用戶同時使用，不同的站點，同時訪問一樣的1000張演唱會門票。
例子2：facebook社群：Taiwan 程式語言讀書會，在這個社群裡的資料庫，用戶發表的每篇文章就唯一地儲存在這個資料庫裡，那為什麼在同一個社群裡的每個用戶都可以看到這些文章，而有些用戶甚至有權限能夠刪除文章內容，像是社群管理員，也許也能夠修改其他用戶的權限，這也是多個用戶，存取同一個資料的例子。
例子3：怎麼避免多用戶同時存取時，呈現給用戶的資料是合理的？假設A, B用戶同時修改M資料表的同一個欄位，A用戶修改欄位的值為8，那麼修改後的值，應該設定只有A用戶看得到；而B用戶修改欄位的值為3，此也應該設定只有B用戶看得到(彼此的操作是互不影響的)。而兩者最終儲存的值，必須在資料庫設定一個機制(通常是以最晚的儲存時間為準 - 最新)，是以誰的最終儲存值，為此資料表的最終值。

3. 交易就類似執行緒的鎖的概念：交易也是透過鎖來解決很多問題的。

4. 交易與hibernate, spring與java的執行緒有何關聯？

5. struts：多個用戶訪問同一筆資料時，此機制是會將同一筆資料複製成多筆副本，讓多個用戶各自存取不同的物件(副本)。

6. 延伸學習：Hibernate的交易隔離層級：髒讀（dirty read）無法重複的讀取（unrepeatable read）幻讀（phantom read）
參考資料：https://openhome.cc/Gossip/HibernateGossip/IsolationLevel.html

7. 交易與第三方外掛程式的關係：
直接使用交易的技術難度很大，很多人是借助第三方外掛程式來實現，因此一般人不需要細細地研究資料庫中的語法細節。
若想使用第三方外掛程式完成預期的功能，一般必須借助資料庫中的交易機制。

-----------------------------------------------------------------------------------------------------------

select * from bank;--調整用
delete from bank;--調整用

--有關交易的例子：

create database test;

use Test;

create table bank(
	customer_name nvarchar(200),
	current_money money
)

insert into bank values ('Lucy', 1000);
insert into  bank values ('Yuta', 1);

--alter:對資料表的結構進行修改(alter table 資料表名 add constraint 約束的名字 check約束(約束條件))
alter table bank add constraint check_current_money check(current_money >= 1);--增加約束(提款時帳戶裡面不能沒錢)

上面的程式碼輸出：


--Lucy匯1000元給Yuta
--(update 資料表名 set 欄位名......... where.......)
update bank set current_money = current_money - 1000 where customer_name = 'Lucy';
--UPDATE 陳述式與 CHECK 條件約束 "check_current_money" 衝突 => Lucy的帳戶會變成沒錢

update bank set current_money = current_money  + 1000 where customer_name = 'Yuta';

上面的程式碼輸出：
UPDATE 陳述式與 CHECK 條件約束 "check_current_money" 衝突，導致輸出這個 "不合理的中間狀態" 資料表，照理說Lucy的帳戶必須減少1000元，但因為Lucy的帳戶餘額會變成0元與check約束有衝突。這種錯誤是非常嚴重的，當然在銀行界是不允許發生的！

因此我們必須透過Transaction機制，屏蔽掉 "不合理的中間狀態" 。
"要麼成功、要麼失敗"

--透過交易機制，屏蔽掉不合理的中間狀態
begin transaction--開啟交易
declare @errorSum int;--宣告一個變數
set @errorSum = 0;--指定初始值

update bank set current_money = current_money - 1000
	where customer_name = 'Lucy';
set @errorSum = @errorSum + @@ERROR;
--不論正確或錯誤，資料會被保存到@@ERROR這個系統的變數裡，
--如果正確@@ERROR = 0；如果錯誤，@@ERRPR儲存的就是錯誤相應的值(錯誤編號)
--最後再將@@ERROR儲存的值指定給@errorSum

update bank set current_money = current_money + 1000
	where customer_name = 'Yuta';
set @errorSum = @errorSum + @@ERROR;

if(@errorSum <> 0)--若@errorSum = 0代表轉帳成功
	begin
		print '轉帳失敗';
		rollback transaction;--回復交易
	end
else
	begin
		print '轉帳成功';
		commit transaction;--確認交易
	end

上面的程式碼輸出：
轉帳失敗，所以再次執行：select * from bank; 語句顯示的資料表是：
轉帳失敗，所以兩者帳戶的餘額均未變動！

參考資料來源：《郝斌數據庫Sql Server 2005》自學視頻

如有敘述錯誤，還請不吝嗇留言指教，thanks!

回首頁