mysql事務，mysql交易處理

mysql事務，mysql交易處理
　介紹

　　mysql 從4.1 就開始支援交易處理，但是只有用 InnoDB /BDB 類型的引擎建立的資料庫才支援事務操作。

       查看mysql資料庫建立引擎類型：show create table table_name

    建立或修改指定類型資料庫：Create table .... type=InnoDB； Alter table table_name type=InnoDB;

       mysql 事務類型

       認為分為兩種：

        1、begin ，rollback,commit .當然有的人用begin /begin work .推薦用START TRANSACTION 是SQL-99標準啟動一個事務。

         

start transaction；update from account set money=money-100 where name='a';update from account set money=money+100 where name='b';commit；

解釋： 這樣start transaction 手動開啟事務，commit 手動關閉事務。

2、預設的時候autocommit=1 自動認可是開啟的，所以你可以理解為每條語句一輸入到mysql就commit 了。當你 set autocommit=0 時候，你可以這樣：

update from account set money=money-100 where name='a';update from account set money=money+100 where name='b';commit；

mysql事務層級
  在介紹事務層級之前需要瞭解常見在資料庫查詢和更新過程中出現的一些問題，簡單來講就是這些事務層級是為瞭解決什麼問題而存在的。

            1.不可重複讀取                    重複讀指的是我們在每次讀取的結果都需要是一致的。那麼不可重複讀取就是這個問題會出現每次讀取相關的資料出現不一致。出現這個的原因是讀取到了其他會話對這條記錄進行了修改。
            2.幻讀                           幻讀指的是當前會話來讀取相關記錄時，出現其他會話對這個記錄添加了一條記錄。為了避免幻讀通常將事務的隔離等級設定為 serializable  。但是隨之而來的問題就有了，當資料庫的事務層級設定為serializable層級，資料庫就變成了單線程訪問資料庫，導致效能下降非常大。
            3.髒讀                例如：使用者A\B同時向t_accout 表操作，A對t_accout做了這樣一個操作，update account set money=money+100 ；在沒有提交之前，B使用者去查詢到沒有提交的的money，而後A此時有了一個rollback，B使用者再查發現錢少了100.這時候為了避免這個情況，一般設定事務的層級為   read committed 。就是只能讀取提交後的東東 ；
        
        為瞭解決上述的問題出現了事務的層級，以下是資料庫中常見的事務層級。

        層級
            1.讀未提交（Read Uncommitted）：這種隔離等級可以讓當前事務讀取到其它事物還沒有提交的資料。這種讀取應該是       在復原段中完成的。通過上面的分析，這種隔離等級是最低的，會導致引發髒讀，不可重複讀取，和幻讀。            2.讀已提交（Read Committed）：這種隔離等級可以讓當前事務讀取到其它事物已經提交的資料。通過上面的分析，這種隔離等級會導致引發不可重複讀取，和幻讀。            3.可重複讀取（Repeatable Read）：這種隔離等級可以保證在一個事物中多次讀取特定記錄的時候都是一樣的。通過上面的分析，這種隔離等級會導致引發幻讀。            4.串列（Serializable）：這種隔離等級將事物放在一個隊列中，每個事物開始之後，別的事物被掛起。同一個時間點只能有一個事物能操作資料庫對象。這種隔離等級對於資料的完整性是最高的，但是同時大大降低了系統的可並發性。
   各個層級對應可能出現的問題


        
 鎖機制
                 除了事務來處理以上不可重複讀取、幻讀、髒讀等問題。還有鎖也能解決上述問題。                         在講解鎖之前，需要對常見的幾個鎖的名詞有一個感性的認識。                
     排它鎖：
    由寫表操作加上的鎖，加鎖後其他使用者不能擷取該表或行的任何鎖，典型是mysql事務中。也就是既不能對錶查詢，也不能對錶進行修改。
          例如：為user表加排它鎖            

start transaction; select * from user where userId = 1 for update;

     共用鎖定：
     由讀表操作加上的鎖，加鎖後其他使用者只能擷取該表或行的共用鎖定，不能擷取排它鎖，也就是說只能讀不能寫                 排它鎖和共用鎖定都是針對某個資料庫來講的，而通常我們聽到的還有例如悲觀鎖、樂觀鎖、行鎖、表鎖。
       悲觀鎖其實是針對於開發人員或者說是程式員來講的。悲觀鎖就是認為在操作資料庫時，悲觀的認為會在同一時間出現並發訪問問題。一般是更新多、查詢少的時候用。與之對應的是樂觀鎖，一般在更新少、查詢多的時候用。

這裡援引來自網上的一個經典的執行個體來講解事務的現實使用。        執行個體講解
        情境：老公去在 ATM 上取錢，老婆在櫃檯存錢，假設這個賬戶中有 1000 元。老公首先執行查詢操作，查詢到賬戶餘額為 1000 此時程式將 1000 拿到記憶體中，老公取了 200 元，程式就執行了更新操作將賬戶餘額改為 800，但是當老公的程式沒有 commit 的時候，老婆查詢賬戶，此時賬戶餘額還是 1000 元，老婆存入 200 元，程式執行了更新操作將賬戶餘額改為 1200，然後老公將更新語句提交，接著老婆也將更新語句提交。最後導致的結果就是該賬戶的餘額為 1200，這就是更新丟失的問題。引發更新丟失的根源就是查詢上，因為雙方都是根據從資料庫查詢到的資料再對資料庫中的資料進行更新的。解決更新丟失有三個方案：(1) 將交易隔離等級設定為最高，採用死結策略。(2)  採用悲觀鎖，悲觀鎖不是資料庫中真正的鎖，是人們看待事務的態度。(3)  採用樂觀鎖，樂觀鎖也不是資料庫中真正的鎖。

如果我們採用的是第一個方案時，老公進行查詢操作，資料庫為表增加了共用鎖定，老婆進行查詢操作時資料庫也增加了一個共用鎖定。但是當老公進行更新資料庫操作時，由於老婆拿著共用鎖定，導致老公不能增加排它鎖，老婆進行更新操作時，因為老公拿著共用鎖定，導致老婆也拿不到排它鎖，這就發生了死結現象，你等我，我等你。在 mysql 中，處理死結的方案是釋放掉一方的鎖。這樣就保證了一方更新成功，但是這種效能極低，因為資料庫頻繁在解決死結問題。

悲觀鎖（更新多，查詢少時用）

如果我們採用的是第二個方案時，即採用悲觀鎖。就是我們在操作資料庫時採用悲觀的態度，認為別人會在此時並發訪問資料庫。我們在查詢語句中 select * from account where name='aaa' for update; 等於加了排它鎖。當老公查詢餘額的時候，select money from account where name='aaa' for update; 增加了排它鎖，老婆查詢賬戶餘額的時候， select money from account where name='aaa' for update；也要求對資料庫加排它鎖，因為老公已經拿到了排它鎖，導致老婆不能加鎖，所以老婆只有等待老公執行完畢，釋放掉鎖以後才能繼續操作。

樂觀鎖（更新少，查詢多時用）

如果我們採用的是第三個方案時，即採用樂觀鎖，就是我們在操作資料庫的時候會認為沒有其它使用者並發訪問，但是樂觀鎖也不是完全樂觀的，樂觀鎖是採用版本號碼的方式進行控制的。在資料庫表中有一列版本號碼。從資料庫中查詢的時候，將版本號碼也查詢過來，在進行更新操作的時候，將版本號碼加1，查詢條件的版本號碼還是查詢過來的版本號碼。比如，老公執行查詢操作的時候，select money,version from account where name='aaa'; 假設此時查詢到的版本號碼為 0，老公在進行更新操作的時候 update account set money=money+100,version=version+1 where name='aaa' and version=0; 未提交時老婆來查詢，查詢到的版本號碼依然是 0，老婆也執行更新操作 update account set money=money+100,version=version+1 where name='aaa' and version=0; 現在老公提交了事務，老婆再提交事務的時候發現版本號碼為 0 的記錄沒有了，所以就避免了資料丟失的問題。不過這種情況也導致了多個使用者更新操作時，只有一個使用者的更新被執行。

附：各資料庫預設事務層級

1、mysql的預設事務的隔離等級：可重複讀取(repeatable read);

2、sqlserver的預設事務的隔離等級：提交讀取(read committed);

3、oracle的預設事務的隔離等級：提交讀取(read committed).

 資料來源：http://www.cnblogs.com/wenfeng762/archive/2011/11/06/2237253.html                http://blog.chinaunix.net/uid-22606185-id-3252443.html