You can declare intermediate markers called savepoints within the context of a
transaction. Savepoints divide a long transaction into smaller parts.
Using savepoints, you can arbitrarily mark your work at any point within a long
transaction. You then have the option later of rolling back work performed
before the current point in the transaction but after a declared savepoint
within the transaction. For example, you can use savepoints throughout a long
complex series of updates,soif you make an error, you do not need to resubmit
Savepoints are similarly useful in application programs. If a procedure contains
several functions, then you can create a savepoint before each function begins.
Then, if a function fails, it is easy to return the data to its state before the
function began and re-run the function with revised parameters or perform a
After a rollback to a savepoint, Oracle releases the data locks obtained by
rolled back statements. Other transactions that were waiting for the previously
locked resources can proceed. Other transactions that want to update previously
locked rows can do so.
When a transaction is rolled back to a savepoint, the following occurs:
1. Oracle rolls back only the statements run after the savepoint.
2. Oracle preserves the specified savepoint, but all savepoints that were
established after the specified one are lost.
3. Oracle releases all table and row locks acquired since that savepoint but
retains all data locks acquired previous to the savepoint.
The transaction remains active and can be continued.
Whenever a session is waiting on a transaction, a rollback to savepoint does not
free row locks. To make sure a transaction does not hang if it cannot obtain a
lock,use FOR UPDATE ... NOWAIT before issuing UPDATE or DELETE statements. (This
refers to locks obtained before the savepoint to which has been rolled back. Row
locks obtained after this savepoint are released, as the statements executed
after the savepoint have been rolled back completely.
3. 在应用程序中使用保存点也是很有用的 , 假设一个过程包含多个函数 , 那么可以在
每个函数开始之前创建一个保存点 , 如果某个函数执行失败 , 就很容易将数据数据恢复到
4. 当事务被回滚到某个保存点之后 , Oracle将释放由被回滚语句使用的锁.其他等待
被锁资源的事务就可以继续执行 . 其他事务也可以更新以前被锁定的行
5. 将事务回滚到某个保存点时 , 发生了以下盛情
a. Oracle 回滚指定保存点之后的语句
b. Oracle 保留指定的保存点，但其后创建的保存点都将被无用
c. Oracle 释放此保存点后获得的表级锁与行级锁 ，但之前的数据锁依然保留.
为了避免事务因为不能获得锁而被挂起，应在执行 UPDATE 或 DELETE 操作前使用
FOR UPDATE ... NOWAIT 语句.
同时保存点之后执行的 SQL 语句也会被完全回滚
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-980933/，如需转载，请注明出处，否则将追究法律责任。