MYSQL事務處理之學習筆記

事務處理是mysql中一個非常好功能，如在刪除大量數據時如果不使用事務處理可能有些刪除失敗導致數據不完整，如果使用事務處理就不會出現此類問題，下面我們一起來看看mysql事務處理用法。

mysql事務處理的幾個步驟:
1.關閉自動提交
2.開啟事務處理
3.有異常就自動拋出異常提示再回滾
4.開啟自動提交

注意:mysql只有這個InnoDB驅動是支持事務處理的，默認MyIsAM驅動不支持.

由於項目設計裡面，牽扯到了金錢的轉移，於是就要用到MYSQL的事務處理，來保證一組處理結果的正確性。用了事務，就不可避免的要犧牲一部分速度，來保證數據的正確性。
只有InnoDB支持事務

事務 ACID Atomicity（原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

1、事務的原子性
一組事務，要麼成功；要麼撤回。

2、穩定性
有非法數據（外鍵約束之類），事務撤回。

3、隔離性
事務獨立運行。
一個事務處理後的結果，影響了其他事務，那麼其他事務會撤回。
事務的100%隔離，需要犧牲速度。

4、可靠性
軟、硬件崩潰後，InnoDB數據表驅動會利用日志文件重構修改。
可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項 決定什麼時候吧事務保存到日志裡。
開啟事務
START TRANSACTION 或 BEGIN

提交事務（關閉事務）
COMMIT

放棄事務（關閉事務）
ROLLBACK

折返點
SAVEPOINT adqoo_1
ROLLBACK TO SAVEPOINT adqoo_1
發生在折返點 adqoo_1 之前的事務被提交，之後的被忽略

事務的終止

設置“自動提交”模式
SET AUTOCOMMIT = 0
每條SQL都是同一個事務的不同命令，之間由 COMMIT 或 ROLLBACK隔開
掉線後，沒有 COMMIT 的事務都被放棄

事務鎖定模式

系統默認： 不需要等待某事務結束，可直接查詢到結果，但不能再進行修改、刪除。
缺點：查詢到的結果，可能是已經過期的。
優點：不需要等待某事務結束，可直接查詢到結果。

需要用以下模式來設定鎖定模式

1、SELECT …… LOCK IN SHARE MODE（共享鎖）
查詢到的數據，就是數據庫在這一時刻的數據（其他已commit事務的結果，已經反應到這裡了）
SELECT 必須等待，某個事務結束後才能執行

2、SELECT …… FOR UPDATE（排它鎖）
例如 SELECT * FROM tablename WHERE id<200
那麼id<200的數據，被查詢到的數據，都將不能再進行修改、刪除、SELECT …… LOCK IN SHARE MODE操作
一直到此事務結束

共享鎖 和 排它鎖 的區別：在於是否阻斷其他客戶發出的 SELECT …… LOCK IN SHARE MODE命令

3、INSERT / UPDATE / DELETE
所有關聯數據都會被鎖定，加上排它鎖

4、防插入鎖
例如 SELECT * FROM tablename WHERE id>200
那麼id>200的記錄無法被插入

5、死鎖
自動識別死鎖
先進來的進程被執行，後來的進程收到出錯消息，並按ROLLBACK方式回滾
innodb_lock_wait_timeout = n 來設置最長等待時間，默認是50秒

事務隔離模式

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
1、不帶SESSION、GLOBAL的SET命令
只對下一個事務有效
2、SET SESSION
為當前會話設置隔離模式
3、SET GLOBAL
為以後新建的所有MYSQL連接設置隔離模式（當前連接不包括在內）

隔離模式

   READ UNCOMMITTED
不隔離SELECT
其他事務未完成的修改（未COMMIT），其結果也考慮在內

   READ COMMITTED
把其他事務的 COMMIT 修改考慮在內
同一個事務中，同一 SELECT 可能返回不同結果

   REPEATABLE READ（默認）
不把其他事務的修改考慮在內，無論其他事務是否用COMMIT命令提交過
同一個事務中，同一 SELECT 返回同一結果（前提是本事務，不修改）

   SERIALIZABLE
和REPEATABLE READ類似，給所有的SELECT都加上了 共享鎖

出錯處理

根據出錯信息，執行相應的處理

 

事務處理在各種管理系統中都有著廣泛的應用，比如人員管理系統，很多同步數據庫操作大都需要用到事務處理。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！
刪除的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的事務處理功能！
一直以來我都以為MYSQL不支持事務處理，所以在處理多個數據表的數據時，一直都很麻煩（我是不得不將其寫入文本文件，在系統重新加載得時候才寫入數據庫以防出錯）～今天發現MYSQL數據庫從4.1就開始支持事務功能，5.0引入了存儲過程^_^

先簡單介紹一下事務吧！事務是DBMS得執行單位。它由有限得數據庫操作序列組成得。但不是任意得數據庫操作序列都能成為事務。

一般來說，事務是必須滿足4個條件（ACID）：

原子性（Autmic）：事務在執行性，要做到“要麼不做，要麼全做！”，就是說不允許事務部分得執行。即使因為故障而使事務不能完成，在rollback時也要消除對數據庫得影響！
一致性（Consistency）：事務得操作應該使使數據庫從一個一致狀態轉變倒另一個一致得狀態！就拿網上購物來說吧，你只有即讓商品出庫，又讓商品進入顧客得購物籃才能構成事務！
隔離性（Isolation）：如果多個事務並發執行，應象各個事務獨立執行一樣！
持久性（Durability）：一個成功執行得事務對數據庫得作用是持久得，即使數據庫應故障出錯，也應該能夠恢復！
MYSQL的事務處理主要有兩種方法：

用begin, rollback, commit來實現：
begin 開始一個事務
rollback 事務回滾
commit  事務確認
直接用set來改變mysql的自動提交模式：
MYSQL默認是自動提交的，也就是你提交一個QUERY，它就直接執行！我們可以通過

set autocommit=0   禁止自動提交
set autocommit=1 開啟自動提交
但注意當你用 set autocommit=0 的時候，你以後所有的SQL都將做為事務處理，直到你用commit確認或rollback結束，注意當你結束這個事務的同時也開啟了個新的事務！按第一種方法只將當前的作為一個事務！
個人推薦使用第一種方法！
MYSQL中只有INNODB和BDB類型的數據表才能支持事務處理！其他的類型是不支持的！（切記！）

測試：
SQL代碼：

 代碼如下 復制代碼 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` VALUES(5);
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO `dbtest` VALUES(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);
?>