MySQL事務控制語句（學習筆記）

 MySQL事務控制語句 
        在mysql命令列的預設下，事務都是自動提交的，sql語句提交後馬上會執行commit操作。因此開啟一個事務必須使用begin，start transaction，或者執行 set autocommit=0;
 可以使用的事務控制語句

start transction | begin : 顯示的開啟一個事務
 commit （commit work）
    commit work與completion_type的關係，commit work是用來控制事務結束後的行為，是chain還是release的，可以透過引數completion_type來控制，預設為0（或者NO_CHAIN），表示沒有任何操作 與commit效果一樣。當completion_type=1的時候

 

 

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(a int, primary key (a))engine=innodb;
Query OK, 0 rows affected (0.29 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set @@completion_type=1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 2;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

# 回滾之後只有1這個記錄，而沒有2這個記錄
mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

 

測試中，將completion_type設定成1，第一次透過commit work來insert這條記錄。之後insert 2的時候並沒有啟用begin（start transaction）來開啟一個事務，之後再插入一條重複的記錄2，這時會丟擲異常rollback後，最後發現只有1這樣一條記錄，2並沒有被insert進去。因為completion_type為1的時候，commit work會開啟另外一個事務，因此2個insert語句是在同一個事務裡面的，所以回滾後就沒有insert進去。

引數completion_type為2時，commit work等同於commit and release。當事務提交時候會自動斷開與db的連線，如下：

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t select 3;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> select @@versison;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    205656
Current database: test

ERROR 1193 (HY000): Unknown system variable 'versison'
mysql> 

 

透過上面的測試發現，completion_type設定成2時，commit work之後，再透過select獲取db伺服器版本資訊的時候出現2006的error，說明以及斷開了與db的連線。

rollback，rollback work與commit，commit work的工作原理一樣。

 rollback（rollback work）
 savepoint identifier：在事務中建立一個儲存點，一個事務允許有多個儲存點
 release savepoint identifier：刪除事務中的儲存點，當時一個儲存點也沒有時執行這個命令，會報錯丟擲一個異常，如下所示：

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

mysql> rollback to savepoint t1;
ERROR 1305 (42000): SAVEPOINT t1 does not exist
mysql> 

innodb儲存引擎中的事務都是原子性的，說明以下2種情況：
構成事務的每條語句都會commit，否則事務的每條語句都會rollback，這種保護還會涉及到單調的語句。一條語句要不完成成功要麼完全回滾，
但是一條語句失敗並不會導致前一條執行的語句自動回滾，他們的工作會保留，需要你手動commit或者rollback。如下：

mysql>  create table t(a int, primary key (a))engine=innodb;
Query OK, 0 rows affected (0.24 sec)

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

mysql> insert into t select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> 
可以看到，插入第二條記錄的時候，db丟擲了1062錯誤，但是並沒有自動回滾，能查出前一條insert的記錄，這個時候需要我們手動commit或者rollback

 

 rollback to [savepoint] identifier：與savepoint一起使用，可以把事務回滾到標記點，而不回滾在此標記點之前的任何工作。
 
 set transaction：設定事務的隔離級別，4種事務隔離級別：read uncommitted，read committed，repeatable read，serializable。
 start transaction與begin都可以在mysql命令列下顯示的開啟一個事務，但是在儲存過程中MySQL會自動將begin識別成begin ... end。
 因此在儲存過程中，只能用start transaction。