Mysql交易處理問題

 　　今天和同學討論起資料庫交易處理的問題，感覺討論中明白了一些，有些知識看過了，但是沒有實際使用還是不理解。

　　交易處理就是將一系列操作當做一個原子操作，要麼全部執行成功，如果執行失敗則保留執行期的狀態。通過提交和復原機制來實現操作，如果全部執行成功通過提交執行commit結果就會記錄到資料庫中，如果執行失敗通過復原操作rollback將發生錯誤之前的所有錯誤消除，回退到原來狀態。

　　事務都應該具備ACID特徵。所謂ACID是Atomic(原子性)，Consistent(一致性)，Isolated(隔離性)，Durable(持久性)四個詞的首字母所寫，下面以“銀行轉帳”為例來分別說明一下它們的含義：

　　原子性：組成交易處理的語句形成了一個邏輯單元，不能只執行其中的一部分。換句話說，事務是不可分割的最小單元。比如：銀行轉帳過程中，必須同時從一個帳戶減去轉帳金額，並加到另一個帳戶中，只改變一個帳戶是不合理的。

　　一致性：在交易處理執行前後，資料庫是一致的。也就是說，事務應該正確的轉換系統狀態。比如：銀行轉帳過程中，要麼轉帳金額從一個帳戶轉入另一個帳戶，要麼兩個帳戶都不變，沒有其他的情況。

　　隔離性：一個交易處理對另一個交易處理沒有影響。就是說任何事務都不可能看到一個處在不完整狀態下的事務。比如說，銀行轉帳過程中，在轉帳事務沒有提交之前，另一個轉帳事務只能處於等待狀態。

　　持久性：交易處理的效果能夠被永久儲存下來。反過來說，事務應當能夠承受所有的失敗，包括伺服器、進程、通訊以及媒體失敗等等。比如：銀行轉帳過程中，轉帳後帳戶的狀態要能被儲存下來。

　　注意Mysql支援的儲存引擎中，預設為MyISAM，是不支援交易處理的，一般都有InnoDB，是支援事務型的。

　　(1)如果對一個表進行操作的時候需要事務支援，需要配置儲存引擎為InnoDB等支援事務型的。

　　create table XX() engine=InnoDB;

　　(2)預設情況下，mysql是自動認可模式(autocommit=1)，此時會在每一條語句執行完畢後將所做修改立即提交，此時的commit相當於沒用的，rollback只對前一句語句起作用，其實也沒用，一條mysql語句預設也是原子操作，沒必要。

　　如果設定預設交易處理，需要將自動認可模式關閉即將autocommit設定為0.

　　set autocommit=0; 設定模式為關閉

　　select @@autocommit; 查看值是否已經改變

　　注意，如果在用戶端設定的話，設定完，之後斷掉串連後再重連又恢複預設設定。每個用戶端只能設定客戶自己的。

　　(3)如果自動認可模式是開啟的，則需使用語句：

　　start transaction; 開始交易處理

　　XX1;

　　XX2;

　　commit; / rollback;

　　來開始交易處理;而如果設定為關閉，則無需使用start transaction，連續語句就為事務指導rollback或者commit。

　　(4)注意建立、改變、刪除資料庫或者其中的資料定義語言 (Data Definition Language)以及鎖有關的都不能成為事務的一部分，如下面：

?

1 2 3 4 5

start transaction; insert into test1 values("8"); create table test2(i int); insert into test1 values("8"); rollback;

　　html" name="code">執行一個交易處理，當執行到要建立表時，mysql會自動認可，然後再執行建立語句。如果test1的i為主鍵，則第三條語句出錯，復原時test1還是插入成功，且建立了表test2.

　　(5)python中使用資料庫，最好採用這種形式，

?

1 2 3 4 5 6 7 8 9

import MySQLdb   try:     conn = MySQLdb.connect(host="localhost",user="root",passwd="your passwd",db="dbName") except MySQLdb.Error,e:     print "Mysql Error %d: %s" % (e.args[0], e.args[1])   else:     pass  #conn.close()

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

try:     cur=conn.cursor()     cur.execute('set autocommit=0') #cur.execute('start transaction')     cur.execute('insert into test1 values("8")')     cur.execute('insert into test1 values("8")')    except MySQLdb.Error,e:     conn.rollback()      print "Mysql Error %d: %s" % (e.args[0], e.args[1])   else:     conn.commit()     cur.close()     conn.close()

　　(6)平行處理問題

　　Mysql是一個多使用者的系統，有多使用者在同一時間訪問統一資料表，MySIAM採用的是資料表級的鎖定 Token，來保證同一時間只有一個使用者訪問此表;Innodb採用了資料行級的訪問機制，即兩個使用者可以對同一個表中不同行的資料同時進行修改，而如果是同一行，則先來的使用者先鎖住此行，操作結束釋放鎖後，下一個使用者才能操作。

　　(7)交易處理的隔離性問題

　　InnoDB預設的隔離等級是repeatable read，如果某個使用者兩次執行同一個select語句，其結果是可重複的，如果在事務期間有使用者對所要讀取的資料進行了操作，那麼也不會有顯示，比如一個儲存引擎為innodb的表，如果有一個客戶用事務來select讀取表資料，另一個使用者此時對錶做了一個插入之類的操作，第一個使用者再進行同樣的select讀取時，顯示資料是沒有變化的。

　　(8)多語句操作非原子操作

　　如上面(6)中會出現一個問題，如果是一個事務操作，讀取資料後，想對資料進行操作，但是可能有另外一個人對此做了操作，那再對此資料進行操作就不對了。

　　此時需要明確加鎖來鎖住表，防止別人更改資料，執行結束後釋放鎖。

　　lock tables XX write;

　　XXXXXX;

　　unlock tables;

　　也可以使用相對更新代替絕對更新，相對於當前值進行更新，不根據上次的值算出一個絕對值進行更新。這樣避免了多條語句的非原子操作。

　　set a = a - 3 XXXXXXXXXXX;