2017年4月19日 星期三

[SQL Server] 了解ACID

前言
在SQL SERVER中，一段語法被執行時，被視為一個Logical unit(邏輯位元)。
我們可以透過BEGIN TRANSACTION 跟 ROLLBACK/COMMIT將多段語法打包為一個Logical unit
為了確保每個Logical unit在進行資料的變更時是可靠安全的，必須符合四大原則，也就是我們常聽到的ACID原則。
  • Atomicity (原子性、不可部份完成性）
  • Consistency (一致性)
  • Isolation (隔離性)
  • Durability (持久性)
下面分別介紹其定義並帶一些簡單的範例來解釋


Atomicity
所有的Logical Unit都必須符合原子性，所謂的原子性就是指整個流程要不是全部成功，不然就整段失敗，不會有部分完成的情況：
舉例來說：
 
IF NOT EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Currency'
  AND TABLE_SCHEMA = 'dbo'
)
BEGIN
CREATE TABLE dbo.Currency
(
CurrecnyID INT IDENTITY(1,1) PRIMARY KEY,
CurrencyCode CHAR(3),
CONSTRAINT DF_Currency_CurrencyCode CHECK(CurrencyCode LIKE '[A-Z][A-Z][A-Z]')
)
END
GO

我們建立一個Currency的Table，根據Constraint，其欄位CurrencyCode必須符合三個字元全部為英文字母的限制。
此時我們故意在同一段insert statement之中塞入一個合法的值跟一個不合法的值。

--確認其原子性
INSERT INTO dbo.Currency
(CurrencyCode)
VALUES('ABC'),('123')

--回傳筆數為0筆
SELECT COUNT(*)
FROM dbo.Currency

我們從結果來看，就算有其中一筆資料是合法的，整段語法若是有錯誤，並不會有任何資料可以成功塞進去，這就是「原子性」．

我們來實驗看看透過BEGIN TRANSACTION來完成多筆INSERT的行為：
--使用 'begin transaction'
BEGIN TRANSACTION
--Logical unit 開始
--非法的資料(塞入失敗)
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('123');
--合法的資料
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('RMB');
COMMIT; --Logical unit 結束
GO

--回傳筆數為1筆
SELECT COUNT(*)
FROM dbo.Currency

此時各位可能覺得很奇怪，這並不符合上面所說的原子性啊？
這是因為SQL Server預設XACT_ABORT 是off的，我們可以到
instance name --> Property --> Connections --> Default connections
看到其設定（非必要需求時請不要更動此Global的設定，應透過下面介紹的兩種方法解決）
 




所謂XACT_ABORT 指的是當交易過程中有錯誤時是否要完整取消整段交易，所以才會有上述:即使我第一段失敗了，第二段仍繼續完成的情況。

既然Default的XACT_ABORT是關閉的，那我們要怎麼解決原子性的問題呢？
有以下兩種方法：
  • 透過在Stored procedure或者adhoc語法中添加SET XACT_ABORT ON;

SET XACT_ABORT ON
GO
BEGIN TRANSACTION
--Logical unit 開始
--非法的資料(塞入失敗)
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('123');
--合法的資料
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('RMB');
COMMIT; --Logical unit 結束
SET XACT_ABORT OFF
GO

--回傳筆數為0筆
SELECT COUNT(*)
FROM dbo.Currency

  • 透過Try,Catch 自行定義Rollback跟commit的時機
BEGIN TRY
BEGIN TRANSACTION
--Logical unit 開始
--非法的資料(塞入失敗)
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('123');
--合法的資料
INSERT INTO dbo.Currency(CurrencyCode)
VALUES('RMB');
COMMIT; --Logical unit 結束
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH
GO

--回傳筆數為0筆
SELECT COUNT(*)
FROM dbo.Currency

關於使用Try Catch 來控制交易的流程，請詳細閱讀以下文章
除了上述案例，原子性同時也可以確保我們執行過程中所發生Deadlock、硬體問題（CPU問題、突然跳電)，可以完整的復原整個交易。

Consistency
在整段Logical unit完成時，並不會破壞你原先對table所制定的所有約束、關聯性，同時也確保你的Data format跟Index的format不會有任何的改變。
舉上面的例子來說：
在交易開始及完成後，並不會有任何一筆違反我原先制定的Check constraint的資料，PK 及 FK的關係也同樣不會被破壞，確保不會違反你的Business rule。
基本上Consistency必須由建立table的工程師所背書的，越嚴謹的限制就能更加保證你的資料的正確性。


Isolation

在許多User共同操作的環境中，多個交易同時發生是很常見的情況。
Isolation就是為了確保每個交易是互相隔離的，不然就有可能破壞掉他們的一致性了。
在此先簡單介紹一下兩個常聽到的名詞，也有需多人搞混：
  • Lock(鎖) : 當某段語法時需要存取某一部分資料時，會放"鎖"到要存取的區塊。不同鎖的類型會有互斥或者可共存的情況，SQL Server透過鎖來確保流程。
  • Block(封鎖) : 假設有兩個Process，Process 1 先放了一個鎖在要存取的區間中，此時Process 2要對同樣區間放上另外一個鎖，但這個鎖跟Process 1 的鎖互斥時，Process 2就得等待Process 1 完成並釋放掉Lock，才可以存取此段區間。
    這樣的過程就叫做    Block。

SQL Server就是透過Lock跟Block來完成隔離性，舉例來說：交易過程中去修改資料，不會同時有另外一個Session可以對同樣區塊同時進行修改，其他Session來讀取資料時，只會讀到完整的交易結束後的資料，不會讀到Transaction進行到一半的資料。
關於交易的隔離又有分許多種等級，日後有機會在介紹。

Durability
在你的Logical unit完成之後，不管之後發生任何事，資料都會完整地保留在資料庫，這就是Durability。
基本上SQL Server 透過在交易完成前後紀錄Transaction log來確保Durability(Write-ahead log)，就算發生大停電、硬體發生問題(有一個例外狀況：Log所存放的disk故障時)，所有的交易行為都會被完好保存。

另外，如果你在交易過程中試圖重啟你的database，SQL Server會從Log中找到Pending住且還沒有完成的交易，並將其寫入disk，這個行為稱之為roll forward。

推薦書籍:
Chapter 20: Blocking and blocked processes



張貼者： 於
標籤：

1 則留言:

訂閱： 張貼留言 (Atom)