SQL基礎-->資料庫事務(TRANSACTION)

--======================

--SQL基礎--&gt資料庫事務

--======================

 

一、資料庫事務

    資料庫事務是指作為單個邏輯工作單元執行的一系列操作，可以認為事務就是一組不可分割的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);

 

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

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

             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;

 

       1 row updated.

 

       SQL> SAVEPOINT tran1;

 

       Savepoint created.

 

    --更新empno 為的salary

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

 

       1 row updated.

 

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

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

 

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

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

             1235 Tony                           boy                  28-JUN-10       5680

             1236 Ben                            IT                   28-JUN-10       4650         20

 

    --將事務回滾到儲存點tran1

       SQL> ROLLBACK TO SAVEPOINT tran1;

 

       Rollback complete.

 

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

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

 

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

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

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       4650         20

 

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

       SQL> ROLLBACK;

 

       Rollback complete.

 

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

 

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

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

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       3100         20

     

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

    事務的開始

       連線到資料並執行了一條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;     COUNT(1) ----------         16
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	SQL> SELECT COUNT(1) FROM emp;     COUNT(1) ----------         17	SQL> SELECT COUNT(1) FROM scott.emp;     COUNT(1) ----------         16
T5	SQL> COMMIT;
T6		SQL> SELECT COUNT(1) FROM scott.emp;     COUNT(1) ----------         17

 

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

 

 

 

八、利用AUTOCOMMIT進行事務控制

    SET AUTOCOMMIT ON

   

    設定自動提交，每執行一條語句，就提交。將autocommit設成ON時，在進行DML操作時似乎很方便，

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

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

 

       關於鎖及事物的隔離級別請關注後續文章