什么是savepoint?
Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.
例如：
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE SCOTT.DEPT SET loc ='a' WHERE loc='NEW YORK';
1 row updated
SQL> SAVEPOINT a;
Savepoint created
SQL> UPDATE SCOTT.DEPT SET loc ='b' WHERE loc='DALLAS';
1 row updated
SQL> SAVEPOINT b;
Savepoint created
SQL> ROLLBACK TO SAVEPOINT a;
Rollback complete
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING a
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
事務(wù)中的Savepoints
你可以在事務(wù)上下文中聲明稱為savepoint的中間標(biāo)記。Savepoint將一個長事務(wù)分隔為較小的部分。
使用savepoint，你可以在長事務(wù)中任何點任意標(biāo)記你的操作。然后你可以選擇回滾在事務(wù)中當(dāng)前點之前、聲明的savepoint之后執(zhí)行的操作。比如，你可以在一長段復(fù)雜的更新中使用savepoint，如果犯了個錯，你不需要重新提交所有語句。
Savepoints在應(yīng)用程序中同樣有用。如果一個過程包含幾個函數(shù)，那可以在每個函數(shù)前創(chuàng)建一個savepoint。如果一個函數(shù)失敗，返回數(shù)據(jù)到函數(shù)開始前的狀態(tài)并在修改參數(shù)或執(zhí)行一個恢復(fù)操作后重新運行函數(shù)就非常容易。
在回滾到一個savepoint后，Oracle釋放由被回滾的語句持有的鎖。其他等待之前被鎖資源的事務(wù)可以進行了。其他要更新之前被鎖行的事務(wù)也可以執(zhí)行。
當(dāng)一個事務(wù)回滾到一個savepoint，發(fā)生下列事件：
1. Oracle僅回滾savepoint之后的語句。
2. Oracle保留這一savepoint，但所有建立于此后的savepoints丟失。
3. Oracle釋放在該savepoint后獲得的所有表、行鎖，但保留之前獲得的所有鎖。
事務(wù)保持活動并可繼續(xù)。
無論何時一個會話在等待事務(wù)，到savepoint的回滾不會釋放行鎖。為了確保事務(wù)如果無法獲得鎖也不會懸掛（hang），在執(zhí)行UPDATE或DELETE前使用FOR UPDATE ... NOWAIT。（這里指回滾的savepoint之前獲得的鎖。該savepoint后獲得的行鎖會被釋放，之后執(zhí)行的語句也會被徹底回滾。）
注意：
1.savepoint 名字保持唯一
2.如果后面新設(shè)置的一個savepoint的名字和前面的一個savepoint名字重復(fù)，前一個savepoint將被取消
3.設(shè)置savepoint后，事務(wù)可以繼續(xù)commit,全部回退或者回退到具體一個savepoints
(Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.)
4.撤銷的處理必須是在沒有發(fā)出commit命令的前提下才能有效。
如下：在commit;后執(zhí)行rollback to savepoint失敗
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE SCOTT.DEPT SET loc ='a' WHERE loc='NEW YORK';
1 row updated
SQL> SAVEPOINT a;
Savepoint created
SQL> UPDATE SCOTT.DEPT SET loc ='b' WHERE loc='DALLAS';
1 row updated
SQL> SAVEPOINT b;
Savepoint created
SQL> COMMIT;
Commit complete
SQL> ROLLBACK TO SAVEPOINT a;
ROLLBACK TO SAVEPOINT a
ORA-01086: 從未創(chuàng)建保留點 'A'
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING a
20 RESEARCH b
30 SALES CHICAGO
40 OPERATIONS BOSTON