資料庫的 ACID

一直以來都沒把ACID的定義記清楚

只知道在程式裡開transaction

最後commit或rollback

不過這也誤打誤撞的符合ACID要求

所謂的ACID的定義

A 是 Atomic 原子性

原子在過去是這世界物質組成的最小物質

當然現在還有發現什麼上帝粒子啦

但當初的原子是最小物質

所以原子的特性

就是這個事務(transaction)是不可分割的組成

C 則是 Consistency 一致性

也就是這個事務中的每一個操作都是一致的

不是全部成功,就是全部失敗

I 是 Isolation 隔離性

也就是在這個事務還沒結束前(commit or rollback)

在資料庫做的所有修改

其他session(連線)是看不見的

隔離性又有分四種

下面再做說明

D 是 Durability 持久性

敘述上是說一但事務結束後

所做的修改都會永久保留

但我覺得這跟做一次transaction比較沒有關聯

而是比較偏向備份等方向

 

隔離等級

又有分四

Read Uncommitted

可以讀取到未commit的資料

也就是有 dirty read 的現象發生

我用 doctrine ORM 跟 MariaDB 做了這個實驗(Oracle不支援Read uncommitted)

先設定 isolation level

$conn->setTransactionIsolation(\Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED);

並在最後rollback

但在rollback之前sleep很久的時間

$conn->beginTransaction();

$product = new Product();
$product->setName("product");

$entityManager->persist($product);
$entityManager->flush();

sleep(1000000);
$conn->rollBack();

並執行這隻php

然後用另一隻php select出所有資料

$productRepository = $entityManager->getRepository('Product');
$products = $productRepository->findAll();

foreach ($products as $product) {
    echo sprintf("-%s\n", $product->getName());
}

可以發現

程式上雖然最後是rollback

但在rollback還沒執行之前

其他的session都是可以看到這筆新增的資料的

這也就會發生很多不可預期的錯誤

難怪Oracle不支援這種模式

 

Read committed

就是只能讀到commit之後的資料,大部分資料庫都是默認這種隔離等級

但這會造成一個問題

在同一個session下讀取到的資料會有不同

假設

A session先讀取 id = 4 的資料

B session 更改了 id = 4 的資料

B session commit

A session 又讀取了 id = 4 的資料

這時候讀到的就會與第一次不一樣了

這叫 unrepeatable read

不可重複讀

一樣來做個實驗

但因為Doctrine有cache的問題

一直無法實現這個問題

所以在select的那隻php改用PDO

$stmt = $db->query('SELECT * FROM products where id = 4');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($results);

sleep(5);

$stmt2 = $db->query('SELECT * FROM products where id = 4');
$results2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);

print_r($results2);

先select一次並睡著5秒

在這五秒之間

就是去執行另一隻update的程式

就會發現五秒之後抓出來的資料跟第一次不同了

 

Repeatable read

這個等級解決了 dirty read 的問題

也解決了 unrepeatable read的問題

但仍然有Phantom read的問題

Phantom read就是當A取得某一個範圍的資料後

B 對這範圍內的資料有新增修改刪除等動作後

A 再次取得這個範圍內的資料就會有不同

造成幻讀的問題(兩次讀到的資料不同,就像幻覺一樣XD)

一樣用PHP來實驗這個問題

$db->beginTransaction();

$stmt = $db->query('SELECT COUNT(*) FROM products');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//use $results
print_r($results);

sleep(5);

$stmt2 = $db->query('SELECT COUNT(*) FROM products');
$results2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);

print_r($results2);

$db->commit();

A 先 count Product 這個資料表

然後睡5秒

這時候執行B去新增一筆資料

$conn->beginTransaction();

$product = new Product();
$product->setName($newProductName);

$entityManager->persist($product);
$entityManager->flush();

$conn->commit();

A 睡醒之後會再去count一次

這時候得到的數字就增加了

最後一個隔離級別

叫做

Serializable 序列化級別

顧名思義

就是每個操作都要按照順序來執行

我們把剛剛測試Phantom read的code

將isolation level調整為 serializable 之後

可以發現 B 在執行新增的時候

必須先等A睡醒之後才會新增

但 B 的 code 裡面並沒有 sleep

也就是 B 的停頓

是資料庫避免Phantom read造成的

以上的實驗code都放在 GitHub

 

 

ref:

https://mariadb.com/kb/en/the-mariadb-library/set-transaction/

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/transactions.html

https://openhome.cc/Gossip/HibernateGossip/IsolationLevel.html

http://www.cnblogs.com/xuanzhi201111/p/4103696.html

看更多