ACID

前言

資料庫系統在寫入或新增資料時,為了確保交易正確可靠性,所以具備

  • 原子性(atomicity,稱不可分割性)
  • 一致性(consistency)
  • 隔離性(isolation,稱獨立性)
  • 持久性(durability)

這就是我們說的ACID.

下面我會跟大家簡述ACID.

Atomicity(原子性)

所有Logical Unit都必須符合原子性

整個流程要不是全部成功,不然就整段失敗,不會有部分完成

sample data.

1
2
3
4
5
6
7
8
IF (SELECT OBJECT_ID('dbo.ProductTest')) IS NOT NULL   
DROP TABLE dbo.ProductTest;

CREATE TABLE dbo.ProductTest ( ProductID INT CONSTRAINT ValueEqualsOne CHECK (ProductID = 1));

CREATE TABLE Product (ProductID INT)
INSERT INTO dbo.Product
SELECT 2

下面我們在sql-server有包Transation,因為ProductTest資料表有一個CONSTRAINT ProductID必須等於1,但目前Product表有1筆不符合此資料表有一個CONSTRAINT資料(ProductId=2)

如果按照(原子性)說法下面語法有包Tran應該會被RollBack,但事實上….

1
2
3
4
5
6
7
8
9
10
11
BEGIN TRAN

--第一句語法
INSERT INTO dbo.ProductTest
SELECT p.ProductID
FROM dbo.Product AS p

--第二句語法
INSERT INTO dbo.ProductTest VALUES (1)

COMMIT

我們在執行上面語法時會有一個Error,但第二句語法卻會成功新增資料.

此時各位會覺得很奇怪,這個語法並不符合原子性.

雖然Transation範圍是有包含Logical Unit,但Sql-Server XACT_ABORT預設是關閉.sta

  1. 開啟XACT_ABORT(預設XACT_ABORTOff
    XACT_ABORT的開關代表,交易過程中有錯誤時是否要完整取消整段交易
  2. 使用Try...Catch判斷RollBack時間點

Atomicity(原子性)可以確保我們在交易過程中失敗,不會有部分成功,部分失敗,造成系統不穩定.

Consistency(一致性) 

在Logical unit完成時不會破壞資料表制訂constraint,關聯性規則.

交易過程中並不會有任何一筆資料違反,我們制定的Check constraint的資料,PK及FK的關係也同樣不會被破壞,確保不會違反Business規則。

sql-server因為要檢查constraint,所以會導致些許Blocking.

Isolation

在現實生活中,會同步進行多個Translation,所以Isolation是為了確保各個交易是互相隔離不會同時互相影響(不然就有可能破壞掉他們的一致性了).

  • Lock:在某段語法被執行時,會放鎖在影響範圍,不同鎖的類型會有互斥或者可共存的情況,資料庫透過鎖確保流程.
  • Block:假如有兩段語法,語法A跟語法B影響資料範圍是一樣的,假如語法A先在此區域上且語法B嘗試要在同一個區域上,發現兩個鎖互斥,這時語法B就會等待語法A執行完後再執行(此狀態稱之為Block).
  • DeadLock:交易結束前會對於影響資料範圍上鎖,如果另一個語法對於某區段資料異動就會產生Block,但如果兩個交易剛好影響範圍是互相顛倒順序,這個Block會無法被釋放….此時就會產生DeadLock(每一段時間會有一個DeadLock Monitor決定誰是被犧牲者,把她資源釋放).

交易隔離有分四種

Isolation Level

  • Read UnCommited: 大家常見的NOLOCK hint(髒讀取),但並不是真的沒有Lock(會放Sch-S Lock),主要是避免在髒讀取時有人對Schema異動或修改
    • ex: (NOLOCK)hint
  • Read Commited: SELECT查詢已經Commit資料,在Transaction中SELECT完畢當下就會釋放掉查詢Shared Lock,Shared Lock不會保留到交易結束.
  • Repeatable Read: Repeatable Read跟Read Commited最大差異是,Repeatable Read會把Shared Lock保留到交易結束
  • Serializable Read: Shared Lock查詢條件範圍都鎖住並保留到最後(Transaction結束)
    • ex: (Hold Lock) hint

Durability

當你完成Transaction後,不管發生什麼事情資料都會完整保留在DB中,一般來說Transaction完成前後都會寫Transaction log

假如DB遇到非預期錯誤(ex:停電),在DB Server重啟後,DB會依照之前紀錄嘗試Recovery.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE INDEX IX_PeriodDate_T99 ON dbo.T99(
PeriodDate
)

ALTER TABLE dbo.T99 ADD PeriodDate AS DATEADD(MINUTE,DATEPART(MINUTE,CreateDate) %5 * -1,
DATETIMEFROMPARTS(
DATEPART(YEAR,CreateDate),
DATEPART(MONTH,CreateDate),
DATEPART(DAY,CreateDate),
DATEPART(HOUR,CreateDate),
DATEPART(MINUTE,CreateDate),0,0)
)

SELECT p.*
FROM dbo.T99 t
JOIN [dbo].[ReportPeriod] p ON p.StartDate = t.PeriodDate
JOIN @Transaction t1 ON t.TransactionId = t1.TransactionId AND p.ProductId = t1.ProductId

此文作者:Daniel Shih(石頭)
此文地址https://isdaniel.github.io/ACID/
版權聲明:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^