MySQL 交易功能 Transaction 整理

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

2022-02-06 補充其他相關內容(併發、資料鎖定)

• 併發：
  若系統開發測試時，沒什麼問題。但正式運行後，慢慢出現資料錯亂的現象，尤其使用人數越多，發生頻率越高。看程式碼找不出原因所在，測試又無法重現問題。
  此時，可能遇到併發問題。
  多個操作對資料庫進行交錯存取，第一個操作還未執行完，第二個操作即插隊對資料庫進行存取。
  多個操作併發，而這些操作不懂排隊，一下A操作存取、一下B操作存取、可能又換回A操作存取、可能出現C操作存取、可能又換成B操作存取.......。當A、B、C操作，都跑完了，資料也亂了。
• 資料鎖定(lock)：
  系統中的一個操作，在程式碼實做中，可能包含好幾個連續的動作，對資料庫進行多次讀取、寫入。
  若這一連串對資料庫的動作，須一氣呵成，不容許外在干擾，避免操作中途，資料產生異動。
  解決的方式，便是在關鍵部分進行資料鎖定，讓其他嘗試存取的操作，只能排隊等前一個操作完成。
• 資料鎖定可能的影響：
• 性能下降(這裡指在系統資源足夠的情況下，能處理的併發數量)：排隊等待前一個操作完成，才能繼續執行，無可避免性能下降。所以只能考量怎樣讓性能下降的越小，避免系統資源還很足夠，使用者卻感覺到系統反應很慢。
  對程式而言，可讓處於資料鎖定階段的程式越少越好。
  對資料庫而言，被鎖定的資料越少越好，InnoDB 可對資料列鎖定，MyISAM 只能對整個資料表鎖定 LOCK TABLES (MySQL 鎖定資料表)， 比較下 InnoDB 更不會鎖定到非必要資料。
• 產生死鎖(deadlock)：進行資料鎖定時，須考量會不會在併發發生時，產生A操作排隊等待B操作，B操作排隊等待A操作，A、B互相無止境等待的情況發生(產生死鎖，最後逾時失敗)。
  若遇到某個操作等很久，最終失敗，可以看看log是不是發生死鎖等待逾時。
  當系統邏輯越來越複雜、新增非預期功能、維護他人系統，可能一不小心就會發生死鎖。
  但只要避免資料錯亂的鎖定處理正確，發生死鎖並不會令資料產生錯誤，正是因為資料鎖定了，才會產生死鎖，讓操作失敗，回復原狀。
  發生死鎖時，可重新把相關程式邏輯、流程，再次釐清，是不是有哪些情況下，可不用對資料加鎖，若是對多個表加鎖，加鎖順序是否可修改。
  
• MVCC (Multiversion Concurrency Control、多版本併發控制)：
  InnoDB 是多版本的儲存引擎，嘗試更動資料時，不會立即更動，而是保存了不同時間點的資料快照，以控制併發時的資料存取。
  https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
  (MySQL :: MySQL 5.7 Reference Manual :: 14.3 InnoDB Multi-Versioning)

後面繼續 MySQL Transaction 的內容。
MySQL 常用的兩個資料表類型：MyISAM、InnoDB，MyISAM 不支援交易功能，所以以下的整理，均是針對 InnoDB 而言。

交易功能4個特性 (ACID)

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

開始進入交易模式

• SQL 指令：START TRANSACTION 或 BEGIN

結束交易模式

• 交易完成：使用 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 的指令

• DDL 的指令
• 官網說明：http://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

會造成自動終止交易並 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 實現的鎖定模式

• 共享鎖定 (shared lock) (S lock)：permits the transaction that holds the lock to read a row.
• 排他鎖定 (exclusive lock) (X lock)：permits the transaction that holds the lock to update or delete a row.
• 官網說明：http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html
• 比較：
  S：共享鎖定
  X：排他鎖定
  IS (Intention shared lock) (意圖共用鎖)：對資料表某幾列加上 S 鎖前，要先取得 IS 鎖，或更強的鎖。
  IX (Intention exclusive lock) (意圖排他鎖)：對資料表某幾列加上 X 鎖前，要先取得 IX 。
  鎖的強度比較：X>SIX>S=IS>IS
  SIX：先加 S 鎖，再加 IX 鎖。SIX = S + IX
  

  	X	IX	S	IS
  X	衝突	衝突	衝突	衝突
  IX	衝突	兼容	衝突	兼容
  S	衝突	衝突	兼容	兼容
  IS	衝突	兼容	兼容	兼容

• 參考：
  https://export.writer.zoho.com/public/acksinkwung/%E8%B3%87%E6%96%99%E5%BA%AB%E4%BA%A4%E6%98%93%E9%8E%96%E5%AE%9A/fullpage
  http://stackoverflow.com/questions/11837428/whats-the-difference-between-an-exclusive-lock-and-a-shared-lock
  http://baike.baidu.com/view/711264.htm

不同 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 的秒數，此為最長等待時間。避免發生無法預測的死結，而一直等待。

交易中連線查詢

• 交易中的連線，若有對 InnoDB 資料表進行操作，
  用 「SELECT * FROM information_schema.innodb_trx\G;」可查詢到。
  (註：測試交易連線，若尚未對 InnoDB 資料表進行操作過，此指令查詢不到。)
  參考：https://stackoverflow.com/a/30555715
• 若為 MariaDB，使用「SELECT @@in_transaction; 」指令，可查詢目前連線是否處於交易中。
  參考：https://stackoverflow.com/a/36840813

範例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：交易層級 REPEATABLE-READ，可避免 non-repeatable read

session 1(交易連線)	session 2
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | +---+---+ 1 row in set (0.00 sec) (a 是主鍵)
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) (進入交易模式)
	mysql> UPDATE xx SET b=1 WHERE a=1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 1 | +---+---+ 1 row in set (0.00 sec) (更改b的值為1)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 1 | +---+---+ 1 row in set (0.00 sec) (之前沒有SELECT過，仍是取得其他連線更改過的b=1資料)
	mysql> UPDATE xx SET b=2 WHERE a=1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ 1 row in set (0.00 sec) (更改b的值為2)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 1 | +---+---+ 1 row in set (0.00 sec) (之前SELECT過，取得跟之前SELECT一樣的結果，不會取得之後其他連線的更動結果)
mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ 1 row in set (0.00 sec) (COMMIT後，取得其他連線更動的最新結果)

範例3：InnoDB 的交易層級 REPEATABLE-READ，可避免 phantom read

session 1(交易連線)	session 2
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | +---+---+ 1 row in set (0.00 sec) (a 是主鍵)
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) (進入交易模式)
	mysql> INSERT INTO xx (a, b) VALUES (2,1); Query OK, 1 row affected (0.07 sec) mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) (新增一筆 a=2,b=1 的資料)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) (之前沒有SELECT過，仍是取得其他連線新增過筆數過的資料)
	mysql> INSERT INTO xx (a, b) VALUES (3,2); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | | 2 | 1 | | 3 | 2 | +---+---+ 3 rows in set (0.00 sec) (新增一筆 a=3,b=2 的資料)
mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) (之前SELECT過，取得跟之前SELECT一樣的結果，仍是兩筆資料，不會取得之後其他連線新增的資料)
mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM xx; +---+---+ | a | b | +---+---+ | 1 | 0 | | 2 | 1 | | 3 | 2 | +---+---+ 3 rows in set (0.00 sec) (COMMIT後，取得其他連線更動的最新結果)

範例4：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)

範例5：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 鎖定)

範例6：兩個連線 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

範例7：一個連線先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)

範例8：
兩個連線，一個開啟交易，並「FOR UPDATE」對一筆資料加排他鎖。
另一個連線，雖沒開啟交易，但仍不可以使用「FOR UPDATE」、「LOCK IN SHARE MODE」讀取該筆資料。

session 1(交易連線)	session 2(非交易連線)
MariaDB [test]> BEGIN; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)	MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM user WHERE u_id=3 FOR UPDATE; (對資料加排他鎖)
	MariaDB [test]> SELECT * FROM user WHERE u_id=3 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (雖不是交易的連線，仍不能讀取被鎖定的資料，會等到逾時，出現失敗訊息)

其他參考資料：

• http://zh.wikipedia.org/wiki/ACID
• http://oss.org.cn/man/database/mysql/inonodb_zh/8.htm