分类: Oracle
2009-09-07 22:06:45
Savepoint是在transaction层面定义的还原点,根据事务的运行机制,可以通过过rollback to 语句还原到某个点.依赖于undo segment.
SQL> ROLLBACK TO SAVEPOINT savepoint_name;
savepoint不受任何权限的限制,也不影响SCN的变化,只是对事务进行一个ROLLBACK操作.比如下面的操作
SQL> update test_histogram set sex='0' where id = 4 and sex = '1';
1 row updated.
SQL> savepoint a;
SQL> select id,name from test_null;
ID NAME ---------- ----------
1 a
2 b
c
3 d
10 ddfabbcd
SQL> update test_null set name='b' where id = 1;
1 row updated.
SQL> select id,sex from test_histogram where id = 4 and sex = '0';
ID S
--------- -
4 0
SQL> select id,name from test_null where id = 1;
ID NAME
---------- ----------
1 b
SQL> rollback to savepoint a;
Rollback complete.
SQL> select id,sex from test_histogram where id = 4 and sex = '0';
ID S
---------- -
4 0
SQL> select id,name from test_null where id = 1;
ID NAME
---------- ----------
1 a
SQL>Commit;
可以看出savepoint定义之前的update并没有受影响,rollback to savepoint只回滚了savepoint定义之后的update,但事务并没有真正的结束, 我们没有明确指定savepoint点之前的事务是回滚呢还是提交,所以rollback to savepoint之后,我们必须commit或rollback本次事务.才算事务真正意义上的结束.这些细小的知识看起来非常简单,在有些情况下必须自己测试了真正领会了,做到心里有底,才算完整.