mysql交易處理學習筆記

什麼是事務

在MySQL環境中，一個事務由作為一個單獨單元的一個或者多個sql語句組成。這個單元中的每個sql語句是互相依賴的， 而且單元作為一個整體是不可分割的。如果單元中的一個語句不能成功完成，整個單元都會復原，所有影響到的資料將返回到 事務開始之前的狀態。因而，只有事務中的所有語句都被成功的執行才能說這個事務被成功執行。

事務的四個特性：

原子性，每個事務都必須被認為是一個不可分割的單元。

一致性，不管事務是完全成功還是中途失敗，當事務使系統處於一致的狀態時存在一致性。

孤立性，每個事務在它自己的空間發生，和其他發生在系統中的事務隔離，而且事務的結果只有在它完全被執行時才能看到。

持久性，即使系統崩潰，一個提交的事務扔在堅持。

生命週期

為了初始化一個事務，並告訴MySQL所有隨後的sql語句需要被認為是一個單元，MySQL提供了start transaction命令來標記 一個事務的開始。也可以使用begin或者begin work命令來初始化一個事務。通常情況下，start transction命令後跟隨的 是組成事務的sql語句。

一旦sql語句被執行，就可使用commit命令來把整個事務儲存在磁碟上，或者使用rollback命令來撤銷所有的變化。 如果事務包括事務表和非事務表的變化，非事務表的交易處理部分是不能使用rollback命令撤銷的。在這種情況下， MySQL將會返回一個錯誤，通知出現一個不完全撤銷。

commit命令標記了事務塊的結束。

控制事務行為

MySQL提供了兩個變數來控制事務行為：autocommit變數和transaction isolation level變數。

自動認可，預設情況下，MySQL的sql查詢一旦被執行，就會自動向資料庫提交結果。這種預設的行為可以通過特定的 autocommit變數來進行修改。設定set autocommit=0，隨後表的更新將不會被儲存，直到明確發出一個commit命令。

事務孤立級，MySQL預設為repeatable read孤立級，可以使用set來修改

事務和效能

因為支援事務的資料庫在保持不同使用者彼此孤立方面要比非交易資料庫難，所以自然的反應了系統的效能。

我們需要做一些事情來保證事務不會向系統添加不適當的負擔。

使用小事務，兩個普遍的策略

1：保證所有要求的使用者輸入在發出start transaction命令之前都是可行的

2：嘗試把大的事務分成小的事務然後分別執行。

選擇合適的孤立級，孤立級越高，效能越低，所以選擇合適的孤立級，有助於效能最佳化

避免死結，在一個事務環境中，當兩個或者多個處於不同序列的客戶同時想要更新相同的資料時，就會發生死結，我們應該 避免發生死結。

例子

交易處理在各種管理系統中都有著廣泛的應用，比如人員管理系統，很多同步資料庫操作大都需要用到交易處理。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫動作陳述式就構成一個事務！
     刪除的SQL語句
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~
~~
   如果沒有交易處理，在你刪除的過程中，假設出錯了，只執行了第一句，那麼其後果是難以想象的！
但用交易處理。如果刪除出錯，你只要rollback就可以取消刪除操作（其實是只要你沒有commit你就沒有確實的執行該刪除操作）

   一般來說，在商務級的應用中，都必須考慮交易處理的！

 

查看inodb資訊
      shell> /usr/local/mysql -u root -p
      mysql> show variables like "have_%"
系統會提示：
+------------------+-------+
| Variable_name     | Value |
+------------------+-------+
| have_bdb          | YES    |
| have_crypt        | YES    |
| have_innodb       | YES    |
| have_isam         | YES    |
| have_raid         | YES    |
| have_symlink      | YES    |
| have_openssl      | NO     |
| have_query_cache | YES    |
+------------------+-------+
8 rows in set (0.05 sec)
如果是這樣的，那麼我們就可以建立一張支援交易處理的表來試試了。

 

MYSQL的交易處理功能！

作者：Feifengxlq   Email:feifengxlq@sohu.com
一直以來我都以為MYSQL不支援交易處理，所以在處理多個資料表的資料時，一直都很麻煩（我是不得不將其寫入文字檔，在系統重新載入得時候才寫入資料庫以防出錯）～今天發現MYSQL資料庫從4.1就開始支援事務功能，據說5.0將引入預存程序^_^
      先簡單介紹一下事務吧！事務是DBMS得執行單位。它由有限得資料庫操作序列組成得。但不是任意得資料庫操作序列都能成為事務。一般來說，事務是必須滿足4個條件（ACID）
      原子性（Autmic）：事務在執行性，要做到“要麼不做，要麼全做！”，就是說不允許事務部分得執行。即使因為故障而使事務不能完成，在rollback時也要消除對資料庫得影響！
     一致性（Consistency）：事務得操作應該使使資料庫從一個一致狀態轉變倒另一個一致得狀態！就拿網上購物來說吧，你只有即讓商品出庫，又讓商品進入顧客得購物籃才能構成事務！
     隔離性（Isolation）：如果多個事務並發執行，應象各個事務獨立執行一樣！
     持久性（Durability）：一個成功執行得事務對資料庫得作用是持久得，即使資料庫應故障出錯，也應該能夠恢複！
  
   MYSQL的交易處理主要有兩種方法。
   1、用begin,rollback,commit來實現
        begin 開始一個事務
        rollback 交易回復
        commit  事務確認
    2、直接用set來改變mysql的自動認可模式
       MYSQL預設是自動認可的，也就是你提交一個QUERY，它就直接執行！我們可以通過
      set autocommit=0   禁止自動認可
      set autocommit=1 開啟自動認可
   來實現事務的處理。
但注意當你用 set autocommit=0 的時候，你以後所有的SQL都將做為交易處理，直到你用commit確認或rollback結束，注意當你結束這個事務的同時也開啟了個新的事務！按第一種方法只將當前的作為一個事務！
個人推薦使用第一種方法！
   MYSQL中只有INNODB和BDB類型的資料表才能支援交易處理！其他的類型是不支援的！（切記！）

下次有空說下MYSQL的資料表的鎖定和解鎖！

       MYSQL5.0 WINXP下測試通過～   ^_^

mysql> use test;
Database changed
mysql> CREATE TABLE `dbtest`(
     -> id int(4)
     -> ) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> select * from dbtest
     -> ;
Empty set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest value(5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into dbtest value(6);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;
+------+
| id    |
+------+
|     5 |
|     6 |
+------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest values(7);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;
+------+
| id    |
+------+
|     5 |
|     6 |
+------+
2 rows in set (0.00 sec)

mysql>

*******************************************************************************************************************

[PHP]
function Tran( $sql ) {
         $judge = 1;
         mysql_query('begin');
         foreach ($sql as $v) {
                 if ( !mysql_query($v) ) {
                         $judge = 0;
                 }
         }
         if ($judge == 0) {
                 mysql_query('rollback');
                 return false;
         }
         elseif ($judge == 1) {
                 mysql_query('commit');
                 return true;
         }
}
[/PHP]

************************************************

<?php
$handler=mysql_connect("localhost","root","");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");//設定為不自動認可，因為MYSQL預設立即執行
mysql_query("BEGIN");//開始事務定義
if(!mysql_query("insert into trans (id) values('2')"))
{
mysql_query("ROOLBACK");//判斷當執行失敗時復原
}
if(!mysql_query("insert into trans (id) values('4')"))
{
mysql_query("ROOLBACK");//判斷執行失敗復原
}
mysql_query("COMMIT");//執行事務
mysql_close($handler);
?>

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)以及鎖有關的都不能成為事務的一部分，如下面：

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()

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

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

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；