Transaction - The first sight
Database Transaction
大部分的RDB都支援 Transaction, 一連串的SQL script。如果其中一個失敗則整個Transaction退到一開始的狀態，如同開始前建立個check point，執行期間出錯則rollback 到check point。
Database Transaction 資料庫交易
例如轉帳，Ａ、Ｂ各有100元在戶頭，Ａ轉30元給Ｂ，則 Ａ戶頭 -30 與 Ｂ戶頭 +30這兩個update SQL語法需要都執行成功才算一個完整的Transaction（交易）
DBMS處理transaction 須具備ACID特性來保證交易的完整可靠
Atomicity - 原子性、不可分割，途中發生錯誤即返回原始狀態
Consistency - 一致性，開始與結束資料庫的完整性沒有被破壞(data integrity)
Isolation - 隔離性，Database 允許同時間對相同row進行讀寫，隔離性可以防止concurrent 交叉執行導致數據不一的狀況
Durability - 持久性，交易處理完，對數據的修改是永久的
Isolation level 有分以下四種，Read uncommitted, Read commuted, Repeatable Read, Serializable
Read uncommitted - transaction 可以讀到別的transaction尚未commit 的資料（完全無保護）
Read committed - transaction 可以讀到別的transaction已經commit 的資料，解決dirty read issue
Repeatable Read - 每次transaction讀取特定欄位，只要query條件相同，讀取到的資料就相同。（解決Non-repeatable Reads issue）
Serializable - Concurrent transaction 只要順序相同，得到的結果也會是相同。也就是執行T1後執行T2，無論另一個transaction是否對自己操作的資料有和更動，都得到一樣的結果。（解決Phantom Reads issue）
大多數concurrent read, write database 都會遇到transaction 還沒update完就被另一個transaction存取，使得顯示同一資料顯示不一致內容。
MySQL InnoDB 預設的isolation level 是Repeatable Read
可以透過以下指令去做調整level
SET SESSION transaction_isolation='SERIALIZABLE';
PostgreSQL 預設的isolation level 是 Read Committed
針對DB存取的三種現象 (p1) dirty read, (p2) Non-repeatable read and (p3) Phantom read
p1可以用transaction關鍵字去解決
p2 則會是在同一個transaction中，針對讀過的row在同一個transaction執行期間內保持一樣，因此會建立snapshot於transaction期間。所以在這種況只能解決p3 Phantom中read的問題，有寫入修改的則無法保證一致。
e.g.: t1 read attribute A twice should be the same result during transaction no matter t2 modify attribute A in the same time.
p3 則可透過Serializable 去解決，但並非避免p3就能稱作Serializable，是transaction執行結果一樣才能這樣稱呼。基本上針對同一個欄位的操作同一時間只能有一個transaction，就會考慮到lock相關的機制(e.g.: LOCK IN SHARE MODE)。
e.g.: t1 count attribute B twice by same condition "WHERE B >10" should be the same result during transaction no matter t2 modify attribute B and committed in the same time.
-- LOCK IN SHARE MODE
BEGIN;
SELECT balance FROM accounts WHERE account_id = 5 LOCK IN SHARE MODE;
UPDATE account SET balance = 75 WHERE account_id = 5;
COMMIT;
Use of Locks
在資料庫應用上，系統設計上盡可能短時間內處理完lock中的entity的更動，以不影響到其他transaction。
entity 的定義：An Entity is a set of fields. It is like a user-defined structure that can be re-used across different transactions. Only appropriate and related fields should be grouped into an Entity. 例如：地址可以由address1, address2, city, zipcode組成。
MySQL InnoDB 實作了以下這兩種row-level lock類型
Exclusive locks 排他鎖 - 為單一transaction所持有，通常用在更新資料。在holder-list中只有該transaction，非該擁有的non-holders 在FIFO queue等候處理完，並接著喚醒第一個排隊的transaction去操作。由於是獨占，所以一但被其他鎖(exclusive or shared lock)後就無法再用exclusive lock.
範例：t1 在 row r使用shared lock, 另外t1也對r使用shared lock。兩個都同時擁有，但都不能grant成exclusive lock
SQL語法: SELECT * FROM table WHERE ... FOR UPDATE
Shared locks 共享鎖 - 不同於exclusive locks, 可以允許holder-list裡的holders去讀取資料，在所有holders「都」release 後才能修改資料，甚至是加exclusive lock鎖。如果多個transaction都在排隊，一但被授權shared lock請求，queue中的transaction一併被授權。
範例：t1 在row r使用exclusive lock, 其他transaction t2 都無法對r再grant其他兩種lock上去，等t1釋放。
SQL語法: SELECT * FROM table WHERE ... LOCK IN SHARE MODE
Intention Locks 意向鎖, 分成intention shared locks 和 intention exclusive locks
InnoDB 支援多粒度locking, 混合row lock and table lock，例如： LOCK TABLES ... WRITE 就是針對某個table用exclusive lock去操作row。詳細地說，transaction必須先取得table的intention lock才能在對該table的row加上shared 或exclusive lock.
SQL語法:
LOCK TABLES t1 READ
UNLOCK TABLES
InnoDB 引擎支援row-level locking, 也支援table-level locking
table-level - 開銷小，加鎖快，不會出現dead lock。鎖定顆粒度大，發生鎖衝突機率高，併發度最低。
row-level - 開銷大，加鎖慢，會出現dead lock。顆粒度最小，發生鎖衝突率低，能併發度最高。
對於鎖，整體來來說有兩種方法論: Pessimistic Locking 悲觀鎖, Optimistic Locking 樂觀鎖。
以MySQL為例子，假設存取資料時被其他transaction修改的機率很高，對於資料會頻繁更動保持「悲觀」的態度，就要把資料保護不讓他人修改。悲觀鎖對應到的就是DBMS所提供的locking 機制。
樂觀鎖則是對資料會被修改這件事保持樂觀態度，通常會用version版號來實作，對應到MySQL InnoDB上就是MVCC (Multi-Version Concurrency Control)，把row的歷史版本儲存在rollback segment裡，除了可以給transaction rollback外，也會被用在snapshot data (consistent read)
P.S: consistent read也就是Non-locking Reads, 先前提到的isolation level = Repeatable read 或 Read Committed的SELECT操作預設是consistent read不加lock。若是加上 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE則是locking read
留言
張貼留言