SQL基礎15——資料庫事務(TRANSACTION)

一、資料庫事務

    資料庫事務是指作為單個邏輯工作單元執行的一系列操作，可以認為事務就是一組不可分割的SQL語句

二、資料庫事務的ACID屬性

    原子性（atomic）

　　     事務必須是原子工作單元；對于其資料修改，要麼全都執行，要麼全都不執行。

    一緻性（consistent）

　　     事務在完成時，必須使所有的資料都保持一緻狀态。在相關資料庫中，所有規則都必須應用于事務

        的修改，以保持所有資料的完整性。事務結束時，所有的内部資料結構（如B 樹索引或雙向鍊

        表）都必須是正确的。

    隔離性（insulation）

　　     由并發事務所作的修改必須與任何其它并發事務所作的修改隔離。事務檢視資料時資料所處的狀

        态，要麼是另一并發事務修改它之前的狀态，要麼是另一事務修改它之後的狀态，事務不會檢視中

        間狀态的資料。

    持久性（Duration）

　　     事務完成之後，它對于系統的影響是永久性的。

三、事務的組成

    一個資料庫事務可由：

       一個或多個DML語句組成

       一個DDL語句組成

       一個DCL語句組成

    一個資料事務由：

       以第一個DML語句的執行作為開始

       以下面的其中之一作為結束

           commit     （送出）

           rollback   （復原）

           ddl或dcl語句   （自動送出）

           使用者會話正常結束（自動送出） 

           系統異常終止 （自動復原）

           系統崩潰 （自動復原）

四、事務的送出或復原(COMMIT、ROLLBACK)       

    --使用COMMIT事務，robinson記錄被插入到表

       SQL> INSERT INTO scott.emp(empno,ename,job,salary)

         2  VALUES(9999,'Robinson','DBA',3500);

       1 row created.

       SQL> COMMIT;

       Commit complete.

       SQL> UPDATE scott.emp SET salary = 4000 WHERE ename = 'Robinson';

       1 row updated.

    --使用ROLLBACK復原，更新将失效，最終結果如下面的查詢

       SQL> ROLLBACK;

       Rollback complete.

       SQL> SELECT * FROM scott.emp WHERE ename = 'Robinson';

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             9999 Robinson                       DBA                                  3500 

五、儲存點

    設定儲存點：

       SAVEPOINT NAME

    恢複至儲存點：

       ROLLBACK TO NAME

    --檢視empno為，的記錄  

       SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       3100         20

    --首先更新empno 為的salary ，并設定了一個儲存點tran1     

       SQL> UPDATE scott.emp SET salary = salary * 1.5 WHERE empno = 1236;

       SQL> SAVEPOINT tran1;

       Savepoint created.

    --更新empno 為的salary

       SQL> UPDATE scott.emp SET salary = salary * 0.8 WHERE empno = 1235;

    --檢視剛剛更新的兩條記錄

             1235 Tony                           boy                  28-JUN-10       5680

             1236 Ben                            IT                   28-JUN-10       4650         20

    --将事務復原到儲存點tran1

       SQL> ROLLBACK TO SAVEPOINT tran1;

    --可以看到儲存點之後的修改被復原，而儲存點之前的修改則不受影響

    --對所作的修改全部ROLLBACK，此時僅僅包含了empno 為的記錄,因為已經被復原

       SQL>  SELECT * FROM scott.emp WHERE empno IN (1235,1236);

六、事務的開始與結束及不同時刻的狀态

    事務的開始

       連接配接到資料并執行了一條DML語句(INSERT ,UPDATE,DELETE)

       前一條事務結束後，又執行了另外一條DML語句

    事務的結束

       執行COMMIT 或ROLLBACK

       執行DDL語句，則自動送出并結束事務

       執行DCL語句，則自動送出并結束事務

       斷開與資料庫的連接配接，如退出SQL Plus。

           Windows下SQL Plus正常退出将執行COMMIT，如點選關閉視窗直接關閉則復原

           X-Window下SQL Plus正常退出将執行COMMIT，如點選關閉視窗直接關閉則復原(筆者在RHEL5下測試如此)

       DML語句執行失敗則自動復原

    送出或復原前的資料狀态

       改變前的資料狀态是可以恢複的

       執行DML 操作的使用者可以通過SELECT 語句查詢之前的修正

       其他使用者不能看到目前使用者所做的改變，直到目前使用者結束事務。

       DML語句所涉及到的行被鎖定，其他使用者不能操作

    送出後的資料狀态

       資料的改變已經被儲存到資料庫中。

       改變前的資料已經丢失。

       所有使用者可以看到結果。

       鎖被釋放，其他使用者可以操作涉及到的資料。

       所有儲存點被釋放。

七、并發事務

    多個使用者同時與資料庫互動，且每個使用者都可以同時通路自己的事物，這種事務稱為并發事務

    對于同一個對象上運作的多個事務，僅當執行commit時才對彼此的查詢産生影響

下表中示範了并發事務的處理：

Trans1

Trans2

T1

SQL> SELECT COUNT(1) FROM emp;

  COUNT(1)

----------

        16

SQL> SELECT COUNT(1) FROM scott.emp;

T2

SQL> INSERT INTO emp(empno,ename,salary)

  2  SELECT 6666,'Jenney',3000 FROM DUAL;

T3

SQL> UPDATE emp

  2  SET salary = salary + 200 WHERE ename = 'SCOTT';

T4

        17

T5

SQL> COMMIT;

T6

    示例中顯示了兩個不同的事務交叉執行的順序，可以看出，僅當事務執行COMMIT後，相關事務才産生影響

八、利用AUTOCOMMIT進行事務控制

    SET AUTOCOMMIT ON

    設定自動送出，每執行一條語句，就送出。将autocommit設成ON時，在進行DML操作時似乎很友善，

    但在實際應用中有時可能會出現問題，如，在有些應用中要同時對幾個表進行操作，對于這些表建立

    了外鍵聯系，如果一旦操作失敗另一個表，就很麻煩了。

       關于鎖及事物的隔離級别請關注後續文章