SAVEPOINT

goal: How to control transaction in Oracle Forms ?
fact: Oracle Forms Developer
fact: DEV2K

fix:

Using rollback and savepoint commands to control transaction in Oracle Forms
should be avoided from the following reasons.

- Savepoints are a crucial part of an Oracle transaction
- And they are especially important for a Forms transaction
- The reason is Forms uses savepoints for it's internal processing
- And the Forms transaction must never be desynchronized with the database
transaction
To guarantee the consistency of the Forms processing don't try to set a
savepoint of your own. It won't work anyway or it will break your application.
.
Here are some examples:
.
1) If you do this in PL/SQL in Forms:
    SAVE_POINT my_savepoint;
     ...
     ROLLBACK to my_savepoint;
  Forms will not rollback to my_savepoint.
  It will rollback to it's own savepoint.
  This could be the savepoint defined when the starting the app,
  or before starting the commit processing. When calling a new form
  with CALL_FORM a savepoint is set too. This savepoint is not set
  with OPEN_FORM. So a ROLLBACK TO <savepoint> in this case will
  be a rollback of the entire application.
.
2) If you use a ROLLBACK TO <savepoint> in a stored program
   unit, Forms doesn't see this. So you could rollback a part
   of the database transaction without rolling back the
   correlated part of the Forms transaction. Forms transaction
   processing has very closely tied to the database transaction
   processing. So be always very careful in this area.
.
3) The example for ISSUE_SAVEPOINT is in the Forms 6.0 Reference
   Manuel, page 275. It shows an ON-SAVEPOINT trigger used for
   running against a third party database without savepoints or
   with a savepoint processing different from the Oracle
   processing. If Forms runs with an Oracle database it uses
   the default processing, if not is uses a user exit.
.
Here some rules we should follow:
.
- Don't use the SAVEPOINT or ROLLBACK TO <savepoint> commands
  in Forms. You won't achieve what you want.
.
- Don't use SAVEPOINT, ROLLBACK TO <savepoint>, ROLLBACK or
  COMMIT in stored program units. You'll desynchronize Forms
  and the database. There is an exception to this rule
  which will be explained later.
.
- Don't use ISSUE_SAVEPOINT and ISSUE_ROLLBACK other than
  the documented way.
.
Here is the exception to the second rule which might provide
a solution for your problem. If you really need
a ROLLBACK or a COMMIT in a stored program unit, because
you cannot have it in local PL/SQL, do this:
.
- call a new Form with OPEN_FORM(...,SESSION)
- from this form call the stored program unit
.
So you have a different transaction. Everything you do there
doesn't hurt the existing Forms transaction. Just ensure
that you don't use any Forms default transaction processing
in this second transaction.
.
But in most cases you could do the same thing with a second
transaction and Forms default processing. What was designed
to be a rollback to savepoint first, could here be designed
as a full rollback of the second transaction. This is another
rule you always should keep in mind when programming with Forms:
.
Always try to use Forms default processing:
- otherwise you have to do much more coding
- in most cases the default processing is faster than programmed
  solutions.

---

 

ORACLE SAVEPOINT使用解析

 

事务中的Savepoints
你可以在事务上下文中声明称为savepoint的中间标记。Savepoint将一个长事务分隔为较小的部分。
使用savepoint，你可以在长事务中任何点任意标记你的操作。然后你可以选择回滚在事务中当前点之前、声明的 savepoint之后执行的操作。比如，你可以在一长段复杂的更新中使用savepoint，如果犯了个错，你不需要重新提交所有语句。
Savepoints 在应用程序中同样有用。如果一个过程包含几个函数，那可以在每个函数前创建一个savepoint。如果一个函数失败，返回数据到函数开始前的状态并在修改参数或执行一个恢复操作后重新运行函数就非常容易。
在回滚到一个savepoint后，Oracle释放由被回滚的语句持有的锁。其他等待之前被锁资源的事务可以进行了。其他要更新之前被锁行的事务也可以执行。
当一个事务回滚到一个savepoint，发生下列事件：
1. Oracle仅回滚savepoint之后的语句。
2. Oracle保留这一savepoint，但所有建立于此后的savepoints丢失。
3. Oracle释放在该savepoint后获得的所有表、行锁，但保留之前获得的所有锁。
事务保持活动并可继续。
无论何时一个会话在等待事务，到savepoint的回滚不会释放行锁。为了确保事务如果无法获得锁也不会悬挂（hang），在执行UPDATE或DELETE前使用FOR UPDATE ... NOWAIT。（这里指回滚的savepoint之前获得的锁。该savepoint后获得的行锁会被释放，之后执行的语句也会被彻底回滚。）

注意：
1.savepoint 名字保持唯一
2.如果后面新设置的一个savepoint的名字和前面的一个 savepoint名字重复，前一个savepoint将被取消
3.设置savepoint后，事务可以继续commit,全部回退或者回退到具体一个savepoints
4.撤销的处理必须是在没有发出commit命令的前提下才能有效。

---

 PL/SQL中savepoint和rollback的用法

 

先说一下这两个命令的用法格式：

--起一个名字为A的savepoion
savepoint A(这个A是savepoint的名字)
--跳转到savepoint A处
rollback to A

一旦执行了rollback那么savepoint的操作都将撤消，当然最后一定执行一次commit，否则所有的操作都是在缓存中进行的，不会真正的写入数据库中，写个例子

 

DECLARE
     v_number number;
BEGIN
     v_number := 1;
     insert into DEPT values(deptno_seq.nextval,v_number,'');
     savepoint A;
     insert into DEPT values(deptno_seq.nextval,v_number+1,'');
     savepoint B;
     insert into DEPT values(deptno_seq.nextval,v_number+2,'');
     savepoint C;
     rollback to A;

     --如果commit不加，那么数据并没有写入到数据库中，只是写到了缓存中，直接查询数据库是没有插入的值的
     commit;
END;

deptno_seq.nextval

这个是事先在数据中生成好的序列，代码为

CREATE SEQUENCE "SUCRE"."DEPTNO_SEQ" MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 10 START WITH 201 CACHE 20 NOORDER NOCYCLE ;