2013年5月6日 星期一

MySQL 交易功能 Transaction 整理

資料庫的交易(Transaction)功能,能確保多個 SQL 指令,全部執行成功,或全部不執行,不會因為一些意外狀況,而只執行一部份指令,造成資料異常。

MySQL 常用的兩個資料表類型:MyISAM、InnoDB,MyISAM 不支援交易功能,所以以下的整理,均是針對 InnoDB 而言。

交易功能4個特性 (ACID)

  •  Atomicity (原子性、不可分割):交易內的 SQL 指令,不管在任何情況,都只能是全部執行完成,或全部不執行。若是發生無法全部執行完成的狀況,則會回滾(rollback)到完全沒執行時的狀態。
  • Consistency (一致性):交易完成後,必須維持資料的完整性。所有資料必須符合預設的驗證規則、外鍵限制...等。
  • Isolation (隔離性):多個交易可以獨立、同時執行,不會互相干擾。這一點跟後面會提到的「隔離層級」有關。
  • Durability (持久性):交易完成後,異動結果須完整的保留。

開始進入交易模式

  • SQL 指令:START TRANSACTIONBEGIN

結束交易模式

  • 交易完成:使用 COMMIT 儲存所有變動,並結束交易。
  • 交易過程異常:使用 ROLLBACK 回滾,取消交易,還原到未進行交易的狀態。(若交易過程連線中斷,沒 COMMIT 提交的變更,亦會如同執行 ROLLBACK 取消交易)

儲存點 (SAVEPOINT)

  • 交易過程中,可標示多個不同的儲存點,有需要時可 ROLLBACK 到某個儲存點。
  • 建立儲存點:SAVEPOINT 名稱
  • 刪除儲存點:RELEASE SAVEPOINT 名稱
  • ROLLBACK 到某個儲存點:ROLLBACK TO SAVEPOINT 名稱
  • 如果建立新儲存點時,已有同名稱的舊儲存點,舊儲存點將被刪除,並建立新的儲存點。
  • 官網說明:http://dev.mysql.com/doc/refman/5.7/en/savepoint.html

不能 ROLLBACK 的指令

會造成自動終止交易並 COMMIT 的指令

  • 執行這些指令時,如同先執行了 commit,也就是會先有 commit 的效果。
  • DDL 指令:ALERT TABLE、CREATE INDEX、CREATE TABLE、DROP TABLE、DROP DATABASE、RENAME TABLE、TRUNCATE、LOCK TABLES、UNLOCK TABLES...等
  • SET AUTOCOMMIT=1、 BEGIN、START TRANSACTION
  • 其他,可參考官網更詳細的說明:http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

 AUTOCOMMIT 自動提交設定

  • AUTOCOMMIT 的設定值,預設一般都是 1
  • 查詢目前 AUTOCOMMIT 的設定值:SELECT @@AUTOCOMMIT
  • 將 AUTOCOMMIT 改為 0 時 ( SET AUTOCOMMIT=0 ),就算沒使用 START TRANSACTION 或 BEGIN ,整個連線執行的 SQL 指令,都會等到下達 COMMIT 提交後,才會真正儲存變更。也就是當 AUTOCOMMIT=0 時,跟在交易模式下相同。

InnoDB的3種加鎖的類型

  • Record lock: This is a lock on an index record.
  • Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
  • Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
  • 官網說明:http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html
  • 重點1:Next-key locking combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters
    (select 過程搜尋遇到的資料列都會被加鎖)

InnoDB 實現的鎖定模式

不同 SQL Statements 在 InnoDB 的鎖定

  • SELECT ... FROM: is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.
    說明:取讀資料快照,不加鎖。但在 SERIALIZABLE 隔離層級時,會自動在遇到的資料加鎖(shared next-key locks)。
  • SELECT ... FROM ... LOCK IN SHARE MODE (加共享鎖):sets shared next-key locks on all index records the search encounters.
    說明:
    • 在 select 過程遇到的資料列加上共享鎖。
    • 加上共享鎖的資料,其他連線還是能讀取。
    • 加上共享鎖的資料,也允許其他連線再執行 select ... lock in share mode
    情況測試:
    • [情況1] 有一交易A正在進行中,並異動某些資料列,例如 update ...where id=1,但尚未commit。一般情形,其他連線 select...where id=1,會立即得到資料。但其他連線若下達 select...where id=1 lock in share mode,則須等交易A執行 commit 後,結果才會出來。
    • [情況2] 有一交易A正在進行中,使用 select...where id=1 lock in share mode 指令,但尚未commit。此時其他連線下達 update ... where id =1,則須等交易A執行 commit 後,才會執行。
      註:測試此情形時(在 REPEATABLE READ 隔離層級下),發現一個情形(紅字部份)
      session 1:begin;
      session 1:select v from tt where id=1; /* v=74 */
      session 2:update tt set v=30 where id =1;
      session 1:select v from tt where id=1; /* v=74 */
      session 1:select v from tt where id=1 lock in share mode; /* v=30 */
      (似乎使用 lock in share mode 會再重讀一次最新的 snapshot ?)
      session 2:update tt set v=50 where id =1; /* 未執行,等待session1 commit */
      session 1:commit; /* 此時 session2 的 update 也執行了 */
      說明:
      後來在官網看到一段說明,
      才知道加 lock read(FOR UPDATE、LOCK IN SHARE MODE) 能取得最新的資料狀態。
      http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
      內容如下
      If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:
  • SELECT ... FROM ... FOR UPDATE (加排它鎖):For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.
    說明:
    • 在遇到的資料列加上排他鎖。
    • 加上排他鎖的資料,其他連線能用普通的 select ... 讀取鎖定的資料但不能用 select ... lock in share mode 讀取鎖定的資料 ( select ... from ... for update 當然也不行)。
    • 所以排他鎖跟共享鎖主要的差異,在於是否允許其他連線使用 select ... lock in share mode 讀取鎖定的資料。
  • UPDATE ... WHERE ... (加排它鎖) sets an exclusive next-key lock on every record the search encounters.
  • DELETE FROM ... WHERE ... (加排它鎖)sets an exclusive next-key lock on every record the search encounters.
    說明:在遇到的資料列加上排他鎖。
  • INSERT (加排它鎖)sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
    說明:在 insert 的資料列加上排他鎖。
  • 官網說明:http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

讀取可能產生的異常情況

  • 髒讀(dirty read)
    session 1 session 2
    BEGIN;
    SELECT v FROM tt WHERE id = 1;
    /* v = 5 */
    BEGIN;
    UPDATE tt SET v = 100 WHERE id = 1;
    /* v = 100 */
    SELECT v FROM tt WHERE id = 1;
    /* v = 100 */
    /* v = 100 */
    髒讀(dirty read)
    /* v = 100 */
    /* v 實際為 5,但 session 1以為是 100 */
    ROLLBACK;
    /* v = 5 */
  • 無法重覆讀取到相同結果(non-repeatable read)
    session 1 session 2
    BEGIN;
    SELECT v FROM tt WHERE id = 1;
    /* v = 5 */
    BEGIN;
    UPDATE tt SET v = 100 WHERE id = 1;
    /* v = 100 */
    COMMIT;
    /* v = 100 */
    SELECT v FROM tt WHERE id = 1;
    /* v = 100 */
    無法重覆讀取到相同結果(non-repeatable read)
    第一次讀到 v=5
    第二次讀到 v=100
  • 幻讀(phantom read)
    session 1 session 2
    BEGIN;
    SELECT * FROM tt;
    /*
    id = 1, v=5
    */
    BEGIN;
    INSERT INTO tt VALUES ( 2, 8);
    COMMIT;
    SELECT * FROM tt;
    /*
    id = 1, v=5
    id = 2, v=8
    */
    幻讀(phantom read)
    兩次取得的筆數不相同

交易的4種隔離層級(isolation level)

  • READ UNCOMMITTED:這是最低的層級。SELECT 可以讀取其他交易中尚未 commit 的資料。如果讀取的資料,最後被 rollback,便會造成讀取到被取消的資料 (dirty read)。(注意是指 SELECT 不會被阻擋,如果是 UPDATE 仍會被阻擋)
    可能產生:髒讀 (dirty read)無法重覆讀取到相同結果 (non-repeatable read)幻讀 (phantom read)
  • READ COMMITTED:此層級會考慮其他交易的執行結果,所以 SELECT 可以讀取其他交易 commit 後的結果。尚未 commit 的結果不能讀取,所以不會有前一個層級 dirty read 的問題。但是,若兩個 SELECT 之間,有其他交易 commit 過資料了,會造成第一次跟第二次取得的資料不一樣 ,也就是重覆讀取可能結果不一樣 (non-repeatable read)。
    可能產生:無法重覆讀取到相同結果 (non-repeatable read)幻讀 (phantom read)
  • REPEATABLE READ:此為 innodb 預設的隔離層級。此隔離層級,不會考慮其他交易的修改。同一交易內,除非自己修改,否則重覆 SELECT 的結果一定相同,所以不會有前一個層級 non-repeatable read 的問題。
    注意:此說明僅針對 innodb,一般來說,此層級會有幻讀 (phantom read) 的問題,但 innodb 使用了 Next-Key Locking 的方式,避免了 phantom read。
    官方說明:Avoiding the Phantom Problem Using Next-Key Locking
  • SERIALIZABLE:跟 REPEATABLE READ 類似,但是將所有的 SELECT 指令都隱含轉換為  SELECT ... LOCK IN SHARE MODE
  • 官網說明:http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

設定交易層級

  • 查詢交易層級全域設定:SELECT @@global.tx_isolation;
  • 查詢交易層級目前連線的設定:SELECT @@tx_isolation;
  • 設定全域的交易層級: SET GLOBAL TRANSACTION ISOLATION LEVEL 層級名稱
    (注意:設定後,新的連線才會套用)
  • 設定目前連線的交易層級: SET SESSION TRANSACTION ISOLATION LEVEL 層級名稱
  • 層級名稱使用:READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
  • 在 my.cnf 設定檔的設定方式,則是在 [mysqld] 區段加層級設定(READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE),例如:
    [mysqld]
    transaction-isolation = READ-COMMITTED

死結 (deadlock)

  • 不同交易之間,無窮盡互相等待的情況稱為死結。一般死結的行程會自動 ROLLBACK。
  • 可於 my.cnf 設定 innodb_lock_wait_timeout = n 的秒數,此為最長等待時間。避免發生無法預測的死結,而一直等待。



範例1:交易層級(READ UNCOMMITTED、READ COMMITTED)

READ UNCOMMITTED 層級可讀取到其他連線未 commit 的資料;
READ COMMITTED 層級不能讀取到其他連線未 commit 的資料。
session 1 session 2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM tt; /*目前資料*/ +----+---+ | id | v | +----+---+ | 1 | 5 | +----+---+ 1 row in set (0.00 sec)
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE tt SET v=99 WHERE id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tt; /* READ-UNCOMMITTED 可讀取到未 commit 的資料 */
+----+----+ | id | v | +----+----+ | 1 | 99 | +----+----+ 1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM tt; /* READ-COMMITTED 不能讀取到未 commit 的資料 */ +----+---+ | id | v | +----+---+ | 1 | 5 | +----+---+ 1 row in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM tt;
+----+----+ | id | v | +----+----+ | 1 | 99 | +----+----+ 1 row in set (0.00 sec)

範例2:SELECT ... FOR UPDATE 存在的資料

(資料表:user,索引:account)
session 1 session 2
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
BEGIN; BEGIN;
SELECT * FROM user
WHERE account = 'aa'
FOR UPDATE;
 /*資料表有一筆aa資料*/
(回傳1筆資料)
+---------+------+
| account | data |
+---------+------+
| aa      |      |
+---------+------+
1 row in set (0.00 sec)
session 2 接在 session 1 之後的動作:
[情況1]
SELECT * FROM user
WHERE account = 'aa'
FOR UPDATE;
(等待解鎖)
(aa資料已被session 1 鎖定)

---------------------------------
[情況2]
INSERT INTO user (account) VALUES ('bb');
(可以INSERT,不用等待解鎖)
(因為只有鎖定 aa 這一筆資料被鎖,可新增另一筆 bb 資料)
Query OK, 1 row affected, 1 warning (0.00 sec)


範例3:SELECT ... FOR UPDATE 不存在的資料

(資料表:user,索引:account)
session 1 session 2
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
BEGIN; BEGIN;
SELECT * FROM user
WHERE account = 'bb'
FOR UPDATE;
/*資料表無bb資料*/
(沒資料)
Empty set (0.00 sec)
session 2 接在 session 1 之後的動作:
[情況1]
SELECT * FROM user
WHERE account = 'bb'
FOR UPDATE;
(沒鎖定,不用等待解鎖)
Empty set (0.00 sec)


[情況2]
SELECT * FROM user
WHERE account = 'aa'
FOR UPDATE;
(aa為存在的資料)
(沒鎖定,回傳1筆資料)
+---------+------+
| account | data |
+---------+------+
| aa      |      |
+---------+------+
1 row in set (0.00 sec)

[情況3]
INSERT INTO user (account) VALUES ('任意資料');
(等待解鎖)
(不能INSERT,整個表被session 1 鎖定)



範例4:兩個連線 SELECT ... LOCK IN SHARE MODE 後,其中一個嘗試更動資料

=>兩個 session 都單純 LOCK IN SHARE MODE,兩者之後都可讀取, 但 LOCK IN SHARE MODE 後,另一個則不能更動資料。
session 1 session 2
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
BEGIN; BEGIN;
SELECT * FROM aa
WHERE id = 8
LOCK IN SHARE MODE;
(session 1,S LOCK)
+----+------+
| id | data |
+----+------+
|  8 | 0.00 |
+----+------+
1 row in set (0.00 sec)
SELECT * FROM aa
WHERE id = 8
LOCK IN SHARE MODE;
(可取得資料)
(session 2,S LOCK)
+----+------+
| id | data |
+----+------+
|  8 | 0.00 |
+----+------+
1 row in set (0.00 sec)
UPDATE aa SET data=10
WHERE id = 8;
(LOCK,因為 session 2 的 S LOCK)
commit;
UPDATE aa SET data=10
WHERE id = 8;
(session 2 commit 了,所以解鎖) Query OK, 1 row affected (7.34 sec) Rows matched: 1 Changed: 1 Warnings: 0


範例5:一個連線先X lock資料,另一個連線嘗試 LOCK IN SHARE MODE

=>若 session 1 更動資料(update、insert..), 則 session 2 即使用 LOCK IN SHARE MODE 也被會 lock ,須等 session 1 交易完成
session 1 session 2
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
BEGIN; BEGIN;
UPDATE aa SET data=10
WHERE id = 8;
(session 1 的 X LOCK)
SELECT * FROM aa
WHERE id = 8
LOCK IN SHARE MODE;
(等待解鎖)
(不可取得資料)
(因為 session 1 的 X LOCK)


其他參考資料:

3 則留言: