mysql事務處理學習筆記

事務處理對於高負載的應用來講是非常常用的一個功能了，下文我們來學習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:[email protected]
一直以來我都以為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）注意創建、改變、刪除數據庫或者其中的數據定義語言以及鎖有關的都不能成為事務的一部分，如下面：

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采用的是數據表級的鎖定標記，來保證同一時間只有一個用戶訪問此表；Innodb采用了數據行級的訪問機制，即兩個用戶可以對同一個表中不同行的數據同時進行修改，而如果是同一行，則先來的用戶先鎖住此行，操作結束釋放鎖後，下一個用戶才能操作。

（7）事務處理的隔離性問題

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

（8）多語句操作非原子操作

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

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

lock tables XX write;

XXXXXX;

unlock tables;

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

set a = a - 3 XXXXXXXXXXX；