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

我們建立一個CurrencyTable,根據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 指的是當交易過程中有錯誤時是否要完整取消整段交易,所以才會有上述:即使我第一段失敗了,第二段仍繼續完成的情況。

既然DefaultXACT_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 自行定義Rollbackcommit的時機
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 formatIndex的format不會有任何的改變。
舉上面的例子來說:
在交易開始及完成後,並不會有任何一筆違反我原先制定的
Check constraint的資料,PK FK的關係也同樣不會被破壞,確保不會違反你的Business rule
基本上Consistency必須由建立table的工程師所背書的,越嚴謹的限制就能更加保證你的資料的正確性。


Isolation

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

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

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

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

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



1 則留言: