Transaction ACID－褪色的世界．斑剝的記憶

DB的核心，花銀子買DB的原因：

1.Atomicity→Transaction Unit
‧Auto Commit→SQL指述(以SQL為單位)
例：新增一筆訂單資料，兩筆明細資料，如果其中一筆明細資料新增失敗，正常的一筆訂單資料及明細資料仍會成功新增。
INSERT INTO 訂單資料表
VALUES (‘O01’,’GETDATE()’,’RINOA’)
INSERT INTO 明細資料表
VALUES (‘O01’,’P01’,’20’,’1000’)
INSERT INTO 明細資料表
VALUES (‘O01’,’P02’,’15’,’２０００元’) --錯誤的單價，新增失敗
GO
接著查詢新增結果
SELECT *
FROM 訂單資料表
WHERE OID=’O01’
SELECT *
FROM 明細資料表
WHERE OID=’O01’
GO
‧Implicit Transaction→Connection(以連線為單位)
例：同樣使用上面的範例，如果其中一筆明細資料新增失敗，則所有交易皆不會新增。
先於SQL Server設定：
set implicit_transactions on
set xact_abort on  --代表自動回復
‧Explicit Transaction→將單元以Begin Transaction~Commit Transaction包起來
注意：需搭配自動或自訂回復
例：(自訂回復)
Begin Transaction tranA
    INSERT INTO 訂單資料表
    VALUES (‘O01’,’GETDATE()’,’RINOA’)
    INSERT INTO 明細資料表
    VALUES (‘O01’,’P01’,’20’,’1000’)
    INSERT INTO 明細資料表
    VALUES (‘O01’,’P02’,’15’,’２０００元’) --錯誤的單價，新增失敗
if @@error<>0
    rollback Transaction tranA
else
    Commit Transaction tranA
如果要自動回復，則：
set xact_abort on --代表自動回復
Begin Transaction tranA
    INSERT INTO 訂單資料表
    VALUES (‘O01’,’GETDATE()’,’RINOA’)
    INSERT INTO 明細資料表
    VALUES (‘O01’,’P01’,’20’,’1000’)
    INSERT INTO 明細資料表
    VALUES (‘O01’,’P02’,’15’,’２０００元’) --錯誤的單價，新增失敗
Commit Transaction tranA
2005/2008版本支援TRY~CATCH：
Begin Try
    Begin Transaction tranB
      INSERT INTO 訂單資料表
      VALUES (‘O01’,’GETDATE()’,’RINOA’)
      INSERT INTO 明細資料表
      VALUES (‘O01’,’P01’,’20’,’1000’)
    INSERT INTO 明細資料表
      VALUES (‘O01’,’P02’,’15’,’２０００元’) --錯誤的單價，新增失敗
    Commit Transaction tranB
End Try
Begin Catch
    RAISERROR(‘單價輸入錯誤’);
    rollback transaction
End Catch
2.Consistency→Data Integrity
‧Entity Integrity→(Row)unique→Primary key、Unique key
‧Domain Integrity→(Column)值範圍→型別、Not Null、Check、Default
‧Referential Integrity→值依存性→Foreign key
3.IsolationàLock→Concurrency Control並行控制
☆樂觀(預設值)：可解決Lost update及Dirty Read問題，但存在non-Repeatable Read及Phantoms Read問題，但有最大查詢效果。
☆悲觀：全程使用Exclusive Lock(X)，可解決所有Concurrency Problem。
★Concurrency Problem：Lost update、Dirty Read、non-Repeatable Read、Phantoms Read

★鎖定的種類：Share Lock(S)、Update Lock(U)、Exclusive Lock(X)

例：(Dirty Read)
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED; --預設值

發現T2會一直等待，如果接著在T1查詢中下Rollback Transaction T1，則T2會完成查詢。
例：(Dirty Read)沿用上述例子
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; --適用READ_ONLY，例如歷史資料
發現T2會讀取到員工姓名=’李四’的結果，接著在T1查詢中下Rollback Transaction T1，則T2會查到T1更新員工姓名=’李四’之前的值。

例：不可重覆讀取(non-Repeatable Read)-UPDATE產生之問題
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED; --預設值

發現T1前後兩次的查詢結果不一樣。解法：
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ;
註：即Update_Lock(U)改為Exclusive_Lock(X)+ROW LOCK

例：幻影讀取(Phantoms Read)-Delete或Insert產生之問題

發現T1多了1筆紀錄，與non-Repeatable Read問題不同(影響某筆record)，Phantoms Read影響整個Table。解法：
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
註：即Update_Lock(U)改為Exclusive_Lock(X)+KEY LOCK

總結：
```
SET TRANSACTION ISOLATION LEVEL  
    { READ UNCOMMITTED  --存在Lost update、Dirty Read、non-Repeatable Read、Phantoms Read問題
    | READ COMMITTED    --預設值，存在on-Repeatable Read、Phantoms Read問題
    | REPEATABLE READ   --存在Phantoms Read問題
    | SERIALIZABLE      --不存在任何問題
    }  ;
```
4.Durability→Data Restore
Data Copy→Full Backup、Different Back