mysql事務隔離性(mysql transaction isolation level)

參考資料
主要參考
1.shared locks and exclusive locks
http://stackoverflow.com/questions/11837428/whats-the-difference-between-an-exclusive-lock-and-a-shared-lock
2.SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173763.aspx
3.mysql高性能開發第一章節
4.non-repeatable read 定義參考wiki
https://en.wikipedia.org/wiki/Isolation_(database_systems)

次要參考
1.Details and Usage of Isolation Levels
https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html


一.關于shared locks and exclusive locks
    Think of a lockable object as a blackboard (lockable) in a class room
    containing a teacher (writer) and many students (readers).

    While a teacher is writing something (exclusive lock) on the board:
    Nobody can read it, because it's still being written,
        and she's blocking your view
            => If an object is exclusively locked,
        shared locks cannot be obtained.
        Other teachers won't come up and start writing either, or the board becomes unreadable,and confuses students
            => If an object is exclusively locked, other exclusive locks cannot be obtained.

    When the students are reading (shared locks) what is on the board:
        They all can read what is on it, together => Multiple shared locks can co-exist.
        The teacher waits for them to finish reading before she clears the board to write more
            => If one or more shared locks already exist, exclusive locks cannot be obtained.


    即:
    寫鎖(排它鎖exclusive locks)
    讀鎖(共享鎖shared locks)

    在寫時候
        add exclusive locks
        shared locks cannot be obtained
        exclusive locks cannot be obtained

    在讀的時候
        add shared locks
        shared locks can be obtain
        exclusive locks cannot be obtained
    這裡也更明白exclusive(排斥的，就想這門上加了） shared(在一個事務上，大家可以共享多把鎖)

二. 事務
    事務是為了保證原子操作，但本身有1...N statement.是以肯定是有N個操作的。
    事務1，事務2時并行的，不是串行的接口
         事務1:                                              |事務2
             start                                          |start
             select * from student where name='zhang%'      |update student set name='abc' where name ='zhang%'
             other statements.                              |other statements.
             commit                                         |commit


二.事務與鎖
    1. 鎖與事務
        1) 事務中未必對每個statement(單步代碼涉及的表中的資料)加鎖，後面我們簡稱單步鎖，可能加也可能不加，這句isolation level有關
        2) 即使事務中對每個statement(單步代碼涉及的表中的資料)加鎖，鎖的生存時間也未必規定一定是整個事務中。
            即有些鎖會在事務中增加，在該事務未結束之前，會被釋放
            示例:
            假設表中有zhang1,zhang2.
            事務1:
                start
                select * from student where name='zhang%'表中的zhang1,zhang2資料加shared lock 行鎖。
                釋放表中的zhang1,zhang2資料加shared lock 行鎖
                other statements.
                commit

            假設表中有zhang1,zhang2.


    2. 鎖在事務中一直存在的作用:
    2.1 事務中exlusive locks産生到結束期間，exlusive locks 一直存在
        (即 exlusive locks are held until the transaction completes.)
        即對事務中已modify的資料，在該事務結束之前，在不允許其他事務去讀。

        示例1:exclusive locks 是如何解決事務中已 modify 的資料的可讀性的。
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2
        事務1:                                               |事務2
                                                             |start
            start                                            |update student set name='abc' where id=9
            select * from student where id=9(被阻塞)          |

                                                             |other statements.
                                                             |commit
            select * from student where id=9(重新開機)           |
            //we get:{9}                                     |
            other statements.                                |
            commit                                           |


        示例2:exclusive locks 是擋不住已讀資料被更改的
                      ------- nonrepeatable read(shared locks or MVCC(後續有詳細說明) 才可以解決)
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2(id=9)
        事務1                                                |事務2
            start                                            |start
            select * from student where id=9//we get{zhang2} |
                                                             |update student set name='abc' where id=9
            select * from student where id=9(被阻塞)          |
                                                             |other statements.
                                                             |commit
            select * from student where id=9(重新開機)           |
            //we get:{abc}                                   |
            other statements.                                |
            commit                                           |
        說明:
            我們兩個同樣的select結果不一樣了。這就是“nonrepeatable read”現象.
        nonrepeatable read:
            定義:
            原文:A non-repeatable read occurs, when during the course of a transaction, a row is
            retrieved twice and the values within the row differ between reads.
            翻譯:non-repeatable read發生在一個事務中，需要檢索某一行資料兩次。然而兩次的結果卻不一緻的現象
            原因:事務1沒有獲得shared locks .locks被其他事務搶奪（這裡是事務2的update)
                即[shared locks沒有一直存在直到事務結束]。
            解決政策：
                1).推遲事務2的執行，先執行完事務1.
                2).MVCC(後續有詳細說明)。 事務2先執行，再執行事務1.如果事務1，事務送出時，會檢查他的初始條件是否
                都每有變化，如果變化就rallback,否則就執行


        示例3:exclusive locks 是擋不住phantom data 的
                       ------ nonrepeatable read 之 phantom 現象
        第一種場景[first scenario] 事務内部完成另一個事務的(關于該事務的search query的)modify
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2
            事務1                                                |事務2
                start                                            |start
                select * from student where name="zhang%"        |
                //we get:{zhang1,zhang2}                         |
                                                                 |update student set name='abc' where id=9
                select * from student where name="zhang%"(被阻塞) |other statements.
                                                                 |commit
                select * from student where name="zhang%"(重新開機)   |
                //we get:{zhang1}                                |
                other statements.                                |
                commit                                           |
            分析：
                看到了麼這裡的事務1//we get:{zhang1,zhang2,zhang3},要知道zhang3可是事務2 not commited的資料哦.
                這就是 phantom 現象。 phantom 現象說的就是鬼魅一樣，事務中有兩次search query，兩次結果是不一樣的。
        phantom 現象
            定義：
                A phantom read occurs when, in the course of a transaction, two identical queries are
                executed, and the collection of rows returned by the second query is different from the
                first. The phantom reads anomaly is a special case of Non-repeatable reads
                ”phantom read“發生在一個事務中兩個同樣的查詢，得到的行的集合的結果。兩個結果集合不一緻的情況。
                ”phantom read“ 現象也是特殊的nonrepeatable現象
            原因：目前事務内部完成另一個事務的(關于該事務的search query的條件内的)modify并在目前事務完成之前完成。
                    即沒有“事務開始 shared locks 産生到結束期間，shared locks 一直存在”
            解決：這種場景是“事務開始 shared locks 産生到結束期間，shared locks 一直存在”
                或 MVCC(後續有詳細說明) 可以解決的。

        -------------------------------------------------------------------------------------
        第二種場景[second scenario] 事務内部或非内部完成另一個事務的[關于該事務search query的]insert
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2

            事務内部完成另一個事務的[關于該事務search query的]insert
            事務1:                                               |事務2
                select * from student where name='zhang%'        |
                //we get:{zhang1,zhang2}                         |
                                                                 |start
                                                                 |insert into  student(name) values(zhang3)
                                                                 |other statements.
                                                                 |commit
                select * from student where name='zhang%'        |
                //we get:{zhang1,zhang2,zhang3}                  |
                other statements.                                |
                commit                                           |
            分析：
                看到了麼這裡的事務1兩次的結果分别為{zhang1,zhang2}和{zhang1,zhang2,zhang3},
                這裡後面無論事務2是否送出完成，隻要 insert 這句statement完成，事務1的第二個查詢就得到
                {zhang1,zhang2,zhang3}的結果了。

            事務非内部完成另一個事務的[關于該事務search query的]insert
            事務1:                                               |事務2
                select * from student where name='zhang%'        |
                //we get:{zhang1,zhang2}                         |
                                                                 |start
                                                                 |insert into  student(name) values(zhang3)
                select * from student where name='zhang%'        |
                //we get:{zhang1,zhang2,zhang3}                  |
                                                                 |other statements.
                other statements.                                |commit
                commit                                           |

            這就是 phantom 現象。 phantom 現象說的就是鬼魅一樣，事務中有兩次search query，兩次結果是不一樣的。
        phantom 現象
            定義：
                A phantom read occurs when, in the course of a transaction, two identical queries are
                executed, and the collection of rows returned by the second query is different from the
                 first. The phantom reads anomaly is a special case of Non-repeatable reads
                ”phantom read“發生在一個事務中兩個同樣的查詢，得到的行的集合的結果。兩個結果集合不一緻的情況。
                ”phantom read“ 現象也是特殊的nonrepeatable現象
            原因：
                事務中另一事務插入(關于該事務的search query的條件内的)資料。必須加range鎖，即insert時候生成
                exclusive locks并在事務完成後才釋放。
            解決：這種場景是“事務開始 shared locks 産生到結束期間，shared locks 一直存在”不可以解決的。
                必須range locks or MVCC(後續有詳細說明) 才可以解決的
        -------------------------------------------------------------------------------------
        phantom 現象
            定義：
                A phantom read occurs when, in the course of a transaction, two identical queries
                 are executed, and the collection of rows returned by the second query is different
                from the first. The phantom reads anomaly is a special case of Non-repeatable reads
                ”phantom read“發生在一個事務中兩個同樣的查詢，得到的行的集合的結果。兩個結果集合不一緻的情況。
                ”phantom read“ 現象也是特殊的nonrepeatable現象
            原因：(以下條件之一就會造成phantom data)
                1.事務内部完成另一個事務的(關于該事務的search query的)modify。“事務開始 shared locks
                産生到結束期間，shared locks 一直存在”可以解決
                2.事務中另一事務插入(關于該事務的search query的條件内的)資料。必須加range鎖才可以解決。


    2.2 事務開始 shared locks 産生到結束期間，shared locks 一直存在
        (即 shared locks are held until the transaction completes.)
        即對事務中已讀的資料，在該事務結束之前，不允許其他事務去 modify(包括update,delete).
        [特别注意]但是可以insert!

        示例1: shared locks 是如何解決事務中已 read 的資料的 modify。
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2
        事務1:                                         |事務2
             start                                    |start
             select * from student where name='zhang%'|
             //we get:{zhang1,zhang2}                 |update student set name='abc' where name ='zhang%'(阻塞）
             other statements.                        |
             commit                                   |
                                                      |update student set name='abc' where name ='zhang%'(重新啟動)
                                                      |other statements.
                                                      |commit

        當執行事務1“select...”後,得到結果{zhangone,zhangtwo}兩行并加shared lock，
        這時候事務2開始，執行到“update...”時，阻塞。這句不能成功執行，
        隻能wait事務1把{zhangone,zhangtwo}shared locks釋放後，事務2才能執行“update...”

        示例2: shared locks 是如何擋不住事務中已 read 條件的範圍内的 insert。
            我們給事務加了 exlusive locks.
            假設表中有zhang1,zhang2
        事務1:                                        |事務2
             start                                    |start
             select * from student where name='zhang%'|
             //we get:{zhang1,zhang2}                 |insert into  student(name) values(zhang3)
             select * from student where name='zhang%'|other statements.
             //we get:{zhang1,zhang2,zhang3}          |commit
             other statements.                        |
             commit                                   |


    2.3 事務開始 range locks 産生到結束期間，range locks 一直存在？
        range locks是給你select的條件裡的情況都加鎖，不允許該條件内的 [ update and insert ]。
        定義:
        Range locks are placed in the range of key values that match the search conditions of
        each statement executed in a transaction. This blocks other transactions from updating
        or inserting any rows that would qualify for any of the statements executed by the current
        transaction.

             Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement 
             while using the serializable transaction isolation level. The serializable isolation level requires that any query executed 
             during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock 
             protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range 
             of keys read by the serializable transaction.        

        譬如事務中有"select * from student where name='zhang%' ",我們給事務加了range locks.
        那麼另個事務中你要insert一個學生名字等于zhang3都是不允許的.你要update zhang2為zhang22也是不允許的

        示例:我們給事務加了 range locks.
        事務1:                                         |事務2
             start                                     |
             select * from student where name='zhang%' |
             //we get {zhang1,zhang2}                  |
                                                       |start
                                                       |insert into  student(name) values(zhang3)(被阻塞)
                                                       |or
                                                       |update student set name='abc' where name ='zhang%'(被阻塞)
             other statements.                         |
             commit                                    |
                                                       |insert into  student(name) values(zhang3)(重新啟動)
                                                       |or
                                                       |update student set name='abc' where name ='zhang%'(重新啟動)
                                                       |other statements.
                                                       |commit


三.事務隔離級别(TRANSACTION ISOLATION LEVEL)
    SET TRANSACTION ISOLATION LEVEL
        { READ UNCOMMITTED
        | READ COMMITTED
        | REPEATABLE READ
        | SNAPSHOT
        | SERIALIZABLE
        }
    [ ; ]

    設定和檢視目前資料庫isolation level的指令
    //檢視目前會話隔離級别
    select @@tx_isolation;
    //檢視系統目前隔離級别
    select @@global.tx_isolation;
    //設定目前會話隔離級别
    set session transaction isolation level repeatable read;
    //1.read uncommitted 2.read committed  3.repeatable read(MySQL預設隔離級别)  4.serializable
    //設定系統目前隔離級别
    set global transaction isolation level repeatable read;

    1.READ UNCOMMITTED
    定義:
        Specifies that statements can read rows that have been modified by other transactions
        but not yet committed.
    翻譯:事務可以讀其他事務中被更改還沒有送出的資料。
    缺點:是以我們會讀到髒資料 dirty read.即未必會在資料庫存在的資料，如果事務被rallback而沒有commied的話

    如果看成是鎖實作的話:
        “事務中exlusive locks産生到結束期間，exlusive locks 一直存在”    NO
        “事務開始 shared locks 産生到結束期間，shared locks 一直存在”   NO
        “事務開始 range locks 産生到結束期間，range locks 一直存在”   NO

    示例1:不解決事務中已 modify 的資料的可讀性的。 ------dirty read
        假設表中有zhang1,zhang2
    事務1:                                                |事務2
                                                         |start
       start                                             |update student set name='abc' where name ='zhang%'
       select * from student where name='zhang%'(不被阻塞)|
       //we get:{}                                       |other statements.
                                                         |commit
                                                         |rallback //事實上表中還是有zhang1,zhang2
       other statements.                                 |
       commit                                            |

    反例:事務中exlusive locks産生到結束期間，exlusive locks 一直存在 ------not dirty read
    示例2:exclusive locks 是如何解決事務中已 modify 的資料的可讀性的。
    我們給事務加了 exlusive locks.
    假設表中有zhang1,zhang2
    事務1:                                               |事務2
                                                         |start
        start                                            |update student set name='abc' where name ='zhang%'
        select * from student where name='zhang%'(被阻塞) |
                                                         |other statements.
                                                         |commit
                                                         |rallback  //事實上表中還是有zhang1,zhang2
        select * from student where name='zhang%'        |
        //we get:{zhang1,zhang2}                         |
        other statements.                                |
        commit                                           |

    2.READ COMMITTED
    定義:
        Specifies that statements cannot read data that has been modified but not committed
        by other transactions.
    翻譯:事務不會讀其他事務中更改但沒有送出的資料

    如果看成是鎖實作的話:
        “事務中exlusive locks産生到結束期間，exlusive locks 一直存在“   YES
        “事務開始 shared locks 産生到結束期間，shared locks 一直存在”    NO
        “事務開始 range locks 産生到結束期間，range locks 一直存在” NO
    參照以上的“2.1 事務中exlusive locks産生到結束期間，exlusive locks 一直存在”

    缺點:在事務中modify的資料不可讀了.nonrepeatable reads or phantom date。
    示例請看
    "二.事務與鎖"\"2. 鎖在事務中一直存在的作用:"\"2.1 事務中exlusive locks産生到結束期間，exlusive locks 一直存在"
    中三個示例


    3.REPEATABLE READ
    定義:Specifies that statements cannot read data that has been modified but not yet committed by
        other transactions and that no other transactions can modify data that has been read by
        the current transaction until the current transaction completes.
    翻譯：目前事務不能讀其他事務中被更改的但未送出的資料。其他事務不能modify目前事務中的已讀但未送出的資料。

    如果看成是鎖實作的話:
        “事務中exlusive locks産生到結束期間，exlusive locks 一直存在“   YES
        “事務開始 shared locks 産生到結束期間，shared locks 一直存在”    YES
        “事務開始 range locks 産生到結束期間，range locks 一直存在”  NO
    參照以上的“2.1 事務中exlusive locks産生到結束期間，exlusive locks 一直存在”
    參照以上的“2.2 事務開始 shared locks 産生到結束期間，shared locks 一直存在”

    缺點:phantom data。
    示例請看
    "二.事務與鎖"\"2. 鎖在事務中一直存在的作用:"\"2.1 事務中exlusive..."\
    下的
    "示例1:exclusive locks 是如何解決事務中已 modify 的資料的可讀性的。"
    "示例3:exclusive locks 是擋不住phantom data 的"\"第一種場景[first scenario] 事務内部完成另一個事務的
    (關于該事務的search query的)modify"
    
    "二.事務與鎖"\"2. 鎖在事務中一直存在的作用:"\"2.2 事務開始 shared locks ..."

    4.SERIALIZABLE
    定義：
        Specifies that data read by any statement in a transaction will be the transactionally
        consistent version of the data that existed at the start of the transaction
    翻譯：目前事務讀的所有資料要與事務開始時候一緻。

    如果看成是鎖實作的話:
        “事務中exlusive locks産生到結束期間，exlusive locks 一直存在“   YES
        “事務開始 shared locks 産生到結束期間，shared locks 一直存在”    YES
        “事務開始 range locks 産生到結束期間，range locks 一直存在”  YES
    這就解決了 phantom data