什么是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將一個(gè)長(zhǎng)事務(wù)分隔為較小的部分。
使用savepoint，你可以在長(zhǎng)事務(wù)中任何點(diǎn)任意標(biāo)記你的操作。然后你可以選擇回滾在事務(wù)中當(dāng)前點(diǎn)之前、聲明的savepoint之后執(zhí)行的操作。比如，你可以在一長(zhǎng)段復(fù)雜的更新中使用savepoint，如果犯了個(gè)錯(cuò)，你不需要重新提交所有語(yǔ)句。
Savepoints在應(yīng)用程序中同樣有用。如果一個(gè)過(guò)程包含幾個(gè)函數(shù)，那可以在每個(gè)函數(shù)前創(chuàng)建一個(gè)savepoint。如果一個(gè)函數(shù)失敗，返回?cái)?shù)據(jù)到函數(shù)開始前的狀態(tài)并在修改參數(shù)或執(zhí)行一個(gè)恢復(fù)操作后重新運(yùn)行函數(shù)就非常容易。
在回滾到一個(gè)savepoint后，Oracle釋放由被回滾的語(yǔ)句持有的鎖。其他等待之前被鎖資源的事務(wù)可以進(jìn)行了。其他要更新之前被鎖行的事務(wù)也可以執(zhí)行。
當(dāng)一個(gè)事務(wù)回滾到一個(gè)savepoint，發(fā)生下列事件：
1. Oracle僅回滾savepoint之后的語(yǔ)句。
2. Oracle保留這一savepoint，但所有建立于此后的savepoints丟失。
3. Oracle釋放在該savepoint后獲得的所有表、行鎖，但保留之前獲得的所有鎖。
事務(wù)保持活動(dòng)并可繼續(xù)。
無(wú)論何時(shí)一個(gè)會(huì)話在等待事務(wù)，到savepoint的回滾不會(huì)釋放行鎖。為了確保事務(wù)如果無(wú)法獲得鎖也不會(huì)懸掛（hang），在執(zhí)行UPDATE或DELETE前使用FOR UPDATE ... NOWAIT。（這里指回滾的savepoint之前獲得的鎖。該savepoint后獲得的行鎖會(huì)被釋放，之后執(zhí)行的語(yǔ)句也會(huì)被徹底回滾。）
注意：
1.savepoint 名字保持唯一
2.如果后面新設(shè)置的一個(gè)savepoint的名字和前面的一個(gè)savepoint名字重復(fù)，前一個(gè)savepoint將被取消
3.設(shè)置savepoint后，事務(wù)可以繼續(xù)commit,全部回退或者回退到具體一個(gè)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)建保留點(diǎn) 'A'
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING a
20 RESEARCH b
30 SALES CHICAGO
40 OPERATIONS BOSTON