九、oracle 事務

標籤：

一、什麼是事務
事務用於保證資料的一致性，它由一組相關的dml語句組成，該組的dml(資料操作語言，增刪改，沒有查詢)語句要麼全部成功，要麼全部失敗。
如：網上轉賬就是典型的要用事務來處理，用於保證資料的一致性。

    

二、事務和鎖
當執行事務操作時(dml語句)，oracle會在被作用的表上加鎖，防止其它使用者修改表的結構。這裡對我們的使用者來講是非常重要的。

    

三、提交事務
當用commit語句執行時可以提交事務。當執行了commit語句之後，會確認事務的變化、結束事務。刪除儲存點、釋放鎖，當使用commit語句結束事務之後，其它會話將可以查看到事務變化後的新資料。儲存點就是為復原做的。儲存點的個數沒有限制。

                

四、復原事務
在介紹復原事務前，我們先介紹一下儲存點(savepoint)的概念和作用。儲存點是事務中的一點。用於取消部分事務，當結束事務時，會自動的刪除該事務所定義的所有儲存點。當執行rollback 時，通過指定儲存點可以回退到指定的點，這裡我們作圖說明。

              

五、事務的幾個重要操作
1.設定儲存點 savepoint a
2.取消部分事務 rollback to a
3.取消全部事務 rollback
eg、
SQL> savepoint a; --建立儲存點a
Savepoint created
                   
SQL> delete from emp where empno=7782;
1 row deleted
                
SQL> savepoint b; --建立儲存到b
Savepoint created
                
SQL> delete from emp where empno=7934;
1 row deleted
                   
SQL> select * from emp where empno=7934; --無法查詢到empno為7934這條記錄，因為這條記錄已被刪除
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
                
SQL> rollback to b; --通過保持點來恢複這條記錄
Rollback complete
                   
SQL> select * from emp where empno=7934; 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
               
SQL> select * from emp where empno=7782; --無法查詢到empno為7982這條記錄，因為這條記錄已被刪除
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
                 
SQL> rollback to a; --通過保持點來恢複這條記錄
Rollback complete
               
SQL> select * from emp where empno=7782;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
                
SQL> 
注意：這個復原事務，必須是沒有commit前使用的；如果事務提交了，那麼無論你剛才做了多少個儲存點，都統統沒用。如果沒有手動執行commit,而是exit了，那麼會自動認可。
eg、
SQL> savepoint a;
Savepoint created
                
SQL> delete from emp where empno=7782;
1 row deleted
            
SQL> commit;
Commit complete
        
SQL> rollback to a;
rollback to a
ORA-01086: 從未建立儲存點 ‘A‘
SQL>

           

六、java程式中如何使用事務
在java操作資料庫時，為了保證資料的一致性，比如賬戶操作(1)從一個賬戶中減掉10$(2)在另一個賬戶上加入10$,我們看看如何使用事務？

java代碼

package junit.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransationTest {

    public static void main(String[] args) {

        Connection conn = null;
        try {
            // 1.載入驅動
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.得到串連
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
            Statement sm = conn.createStatement();
            // 從scott的sal中減去100
            sm.executeUpdate("update emp set sal=sal-100 where ename=‘SCOTT‘");
            int i = 7 / 0; //報java.lang.ArithmeticException: / by zero異常
            // 給smith的sal加上100
            sm.executeUpdate("update emp set sal=sal+100 where ename=‘SMITH‘");
            // 關閉開啟的資源
            sm.close();
            conn.close();
        } catch (Exception e) {
            // 如果發生異常，就復原
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }

    }

}

運行，會出現異常，查看資料庫，SCOTT 的sal 減了100，但是SMITH 的sal 卻不變，很可怕。。。
我們怎樣才能保證，這兩個操作要麼同時成功，要麼同時失敗呢？

            
Java 代碼

package junit.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransationTest {

    public static void main(String[] args) {

        Connection conn = null;
        try {
            // 1.載入驅動
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.得到串連
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
            // 加入交易處理
            conn.setAutoCommit(false);// 設定不能預設提交
            Statement sm = conn.createStatement();
            // 從scott的sal中減去100
            sm.executeUpdate("update emp set sal=sal-100 where ename=‘SCOTT‘");
            int i = 7 / 0;
            // 給smith的sal加上100
            sm.executeUpdate("update emp set sal=sal+100 where ename=‘SMITH‘");
            // 提交事務
            conn.commit();
            // 關閉開啟的資源
            sm.close();
            conn.close();
        } catch (Exception e) {
            // 如果發生異常，就復原
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }

    }

}

再運行一下，會出現異常，查看資料庫，資料沒變化。。

            

七、唯讀事務
唯讀事務是指只允許執行查詢的操作，而不允許執行任何其它dml操作的事務，使用唯讀事務可以確保使用者只能取得某時間點的資料。
假定機票代售點每天18點開始統計今天的銷售情況，這時可以使用唯讀事務。在設定了唯讀事務後，儘管其它會話可能會提交新的事務，但是唯讀事務將不會取得最新資料的變化，從而可以保證取得特定時間點的資料資訊。
設定唯讀事務: set transaction read only;

       

比如有兩個使用者system、scott各自用sqlplus登陸，操作如下：
第一步：用system使用者登陸sqlplus，設定唯讀事務。
SQL> set transaction read only;
交易處理集。
                        
第二步：用scott使用者登陸sqlplus，操作如下：
SQL> select count(*) from emp; --查詢emp表的總記錄數
  COUNT(*)
----------
        13

SQL> insert into emp values (7777, ‘zhangsan‘, ‘MANAGER‘, 7782, to_date(‘1988-02-18‘, ‘yyyy-mm-dd‘), 38.38, 45.45, 10); --插入一條記錄到emp表
1 row inserted
              
SQL> select count(*) from emp; --查詢emp表的總記錄數
  COUNT(*)
----------
        14
                  
SQL> commit; --提交
Commit complete

             

第三步：用system使用者查詢scott.emp表
SQL> select count(*) from scott.emp;
  COUNT(*)
----------
        13
SQL>

九、oracle 事務