跳到主要內容

[轉貼]MySQL 交易功能 Transaction 整理 (XYZ的筆記本)

作者：
全文轉貼自：http://xyz.cinc.biz/2013/05/mysql-transaction.html  (XYZ的筆記本)
----------------------------------------------------------------------------------------------------------
資料庫的交易(Transaction)功能，能確保多個 SQL 指令，全部執行成功，或全部不執行，不會因為一些意外狀況，而只執行一部份指令，造成資料異常。

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 的指令

會造成自動終止交易並 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:
    • 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. 
    • UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
      說明：
      • 在遇到的資料列加上排他鎖
      • 加上排他鎖的資料，其他連線能用普通的 select ... 讀取，但不能用 select ... lock in share mode。
      • 所以排他鎖跟共享鎖主要的差異，在於是否允許其他連線使用 select ... lock in share mode
    • 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 1session 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 1session 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 1session 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種隔離層級

      • 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

      死結 (dead lock)

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

       範例

      

      session 1session 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> 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)

      其他參考資料：
      標籤：

      留言

      張貼留言

      這個網誌中的熱門文章

      用 C# 批次控制 Word 合併列印

      作者：
      前由 我有全區的電話資料，問題在於我要依不同里別來製作出電話簿。結果如下圖： 單純採用合併列印無法達成我的需求。解決方法係用「功能變數」儲存上一個里別，與現在里別進行比較：若不同，則換頁。不過，這樣功能變數還蠻長的。最後，我還是採用 C# 來解決。 解決方案 用 C# 控制 WORD 中合併列印的「資料來源 Data Source」，給予不同里別的「sqlstatement」。迴圈處理不同的里別即可。但可預見其處理過程會很慢，不過還好，我可以不用在意它，有跑出結果即可。 程式碼 IList<string> areas = new List<string>() { "後壁", "侯伯", "嘉苳", "土溝", "嘉田", "嘉民", "菁豊", "崁頂", "後廍", "墨林", "菁寮", "新嘉", "頂長", "平安", "仕安", "竹新", "新東", "長安", "頂安", "福安", "烏樹" }; string root = @"D:\"; // 根目錄 string data = root + @"\data.docm"; // 資料檔（即資料來源） string template = root + @"\template.docx"; // 已設定好格式與合併欄位的 Word 檔 string output = @"d:\Final"; // 輸出之資料夾 object oMissing = System.Reflection.Missing.Va...
      張貼留言
      閱讀完整內容

      VLC c# 順利編譯

      作者：
      原文網址： http://www.cnblogs.com/haibindev/archive/2011/12/21/2296173.html 原文作者： haibindev 原文標題：c#万能视频播放器 本文的重點在於修正 class VlcPlayer，使其能順利在 VC# Express 2010 .Net Framework 4 下順利編譯。 修正重點在於 CallingConvention = CallingConvention. StdCall 改成 CallingConvention = CallingConvention. Cdecl using System; using System.Runtime.InteropServices; using System.Security; using System.Text; namespace VlcDotNet { class VlcPlayer { private IntPtr libvlc_instance_; private IntPtr libvlc_media_player_; private double duration_; public VlcPlayer(string pluginPath) { string plugin_arg = "--plugin-path=" + pluginPath; string[] arguments = { "-I", "dummy", "--ignore-config", "--no-video-title", plugin_arg }; libvlc_instance_ = LibVlcAPI.libvlc_new(arguments); libvlc_media_player_ = LibVlcAPI.libvlc_media_player_new(libvlc_instance_); } public ...
      3 則留言
      閱讀完整內容

      [Symfony+Doctrine] 透過非 Id 來使用 Pessimistic Lock

      作者：
      根據 文件 ，Doctrine 有 Pessimistic Lock，又分為兩種： LockMode::PESSIMISTIC_WRITE：對應至 MySQL 的 Select FOR UPDATE LockMode::PESSIMISTIC_READ：對應至 MySQL 的 Select LOCK IN SHARE MODE 差別在於 LOCK IN SHARE MODE 會將在 row 的資料鎖定(row-level lock)，在非同一交易(Transaction)下，不給寫入，其他交易可以讀取， 且可以繼續 Select LOCK IN SHARE MODE 。而 FOR UPDATE 不僅鎖定該資料，在非同一交易下，不給寫入，其它交易可以讀取， 但不能 Select LOCK IN SHARE MODE 。MySQL 文件有更詳細的比較與情境使用的說明，參考 網址 。 現在問題是，我們要完全採用 ORM 來處理資料。Doctrine 的文件提到 EntityManager::find 可以採用 Pessimistic Lock， 但 find 是透過 id 來處理 。而其他 find 系列函數（包括：findAll, findBy, findOneBy）皆不支援 LockMode。 因此，勢必要有方法來「透過非 id 來使用 Pessimistic Lock」。透過查看原始碼，簡單的方法是有的，解法之範例如下： 19 public function depositAction() 20 { 21 22 $em = $this->getDoctrine()->getManager(); 23 24 $em->transactional(function ($em) { 25 $entityName = 'AcmeTrainingBundle:Account'; 26 $lockMode = LockMode::PESSIMISTIC_READ; 27 $orderBy = null; 28 $...
      張貼留言
      閱讀完整內容