Oracle "Database PL/SQL Language Reference" Section: "Retrying Transactions After Handling Exceptions" said:
To retry a transaction after handling an exception that it raised, use this technique:
......
If the transaction fails, control transfers to the exception-handling part of the sub-block, and after the exception handler runs, the loop repeats.
which interprets the exception-handling as Transaction-Level. Over there the example contains two DML statements (delete and insert), and rollback is controlled by a savepoint.
At first pick the test code from Book Oracle PL/SQL Programming (5th edition, Steven Feuerstein) Chapter 14: Section: DML and Exception Handling (Page 446), and add some extra lines:
drop table books;
create table books (book_id integer);
create or replace function tabcount return pls_integer is
l_return pls_integer;
begin
select count (*) into l_return from books;
return l_return;
end tabcount;
/
create or replace procedure empty_library (pre_empty_count out pls_integer) is
begin
pre_empty_count := tabcount ();
dbms_output.put_line ('empty_library pre_empty_count='||pre_empty_count);
dbms_output.put_line ('empty_library tabcount S1='||tabcount());
delete from books where book_id=1;
dbms_output.put_line ('empty_library tabcount S2='||tabcount());
raise no_data_found;
end;
/
set serveroutput on;
truncate table books;
declare
table_count number := -1;
begin
insert into books (book_id) values (1);
insert into books (book_id) values (2);
dbms_output.put_line ('tabcount S1='||tabcount());
empty_library (table_count);
exception when others then
dbms_output.put_line ('tabcount S2='||tabcount());
dbms_output.put_line ('table_count S3='||table_count);
end;
/
select * from books;
tabcount S1=2
empty_library pre_empty_count=2
empty_library tabcount S1=2
empty_library tabcount S2=1
tabcount S2=1
table_count S3=-1
SQL> select * from books;
BOOK_ID
----------
2
set serveroutput on;
truncate table books;
declare
table_count number := -1;
begin
insert into books (book_id) values (1);
insert into books (book_id) values (2);
dbms_output.put_line ('tabcount S1='||tabcount());
empty_library (table_count);
end;
/
select * from books;
tabcount S1=2
empty_library pre_empty_count=2
empty_library tabcount S1=2
empty_library tabcount S2=1
ORA-01403: no data found
ORA-06512: at "K.EMPTY_LIBRARY", line 8
SQL> select * from books;
no rows selected
When an exception occurs in a PL/SQL block, the Oracle database does not roll back
any of the changes made by DML statements in that block.
and Page 447:
If an exception propagates past the outermost block (i.e., it goes “unhandled”),
then in most host execution environments for PL/SQL like SQL*Plus, a rollback is
automatically executed, reversing any outstanding changes.
Crosschecking above two tests, we can see SQL*Plus does not roll back if there is an error handler,
and does a rollback to the beginning of block if there is no error handler (Unhandled Exceptions).
Relevant information can also be found in Page 145 about Unhandled Exceptions:
If an exception is raised in your program, and it is not handled by an exception section
in either the current or enclosing PL/SQL blocks, that exception is unhandled. PL/SQL
returns the error that raised the unhandled exception all the way back to the application
environment from which PL/SQL was run. That environment (a tool like SQL*Plus,
Oracle Forms, or a Java program) then takes an action appropriate to the situation; in
the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level
block’s logic is automatically performed.
and Page 137,
When this procedure(RAISE_APPLICATION_ERROR) is run, execution of the current PL/SQL block halts immediately,
and any changes made to OUT or IN OUT arguments (if present and without the NOCOPY hint) will be reversed.
Book Expert Oracle Database Architecture (3rd Edition, Thomas Kyte, Darl Kuhn) - Chapter 8, Section: Atomicity (Page 277-283) explains the principle of Statement-Level Atomicity, and mimicks the work Oracle normally does with the SAVEPOINT:
Savepoint sp;
statement;
If error then rollback to sp;
It further shows that Oracle considers Procedure-Level Atomicity (PL/SQL anonymous blocks) to be statements as well.
This Blog will try to show how to apply above principle to PL/SQL blocks with Exception Handlers in order to keep application not interrupted.
We will run 5 test cases to demonstrate such rollbacks in Statement-Level. When error occurs, it is not "transaction fails", but only "statement fails".
All Testcode is appended at the end of Blog.
1. undo_tbs_test_1
Run following code:
set serveroutput on lines=200
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
select id, ts, step from test_t1 where id <= 2;
exec undo_tbs_test_1;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
select id, ts, step from test_t1 where id <= 2;
The output look as follows:
NAME VALUE
------------------------------------------- ----------
user commits 685
rollback changes - undo records applied 147854
transaction rollbacks 126
ID TS STEP
-- -------------------------------- ----------
1 22-SEP-2015 07:15:01 0
2 22-SEP-2015 07:15:01 0
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=1
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=2
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=3
End committed, at Step=4
NAME VALUE
------------------------------------------ ----------
user commits 685
rollback changes - undo records applied 151098
transaction rollbacks 129
ID TS STEP
-- ------------------------------ ----------
1 22-SEP-2015 07:15:01 0
2 22-SEP-2015 07:15:01 0
we can see 3 (129-126) "transaction rollbacks", and no user commits (685-685), and hence no rows updated.
2. undo_tbs_test_2
Run following code:
update test_t1 set acc = null;
commit;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
select count(*) from test_t1 where acc is null;
exec undo_tbs_test_2;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
select count(*) from test_t1 where acc is null;
The output look as follows:
NAME VALUE
-------------------------------------------------- ----------
user commits 707
rollback changes - undo records applied 153199
transaction rollbacks 129
sql%rowcount=50 Updated_1, at Step=1
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=1
sql%rowcount=50 Updated_1, at Step=2
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=2
......
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=18
sql%rowcount=50 Updated_1, at Step=19
ORA-30036: unable to extend segment by 8 in undo tablespace 'TEMPUNDO', at Step=19
sql%rowcount=50 Updated_1, at Step=20
sql%rowcount=0 Updated_1, at Step=20
End committed, at Step=20
NAME VALUE
-------------------------------------------------- ----------
user commits 727
rollback changes - undo records applied 154923
transaction rollbacks 129
sql> select count(*) from test_t1 where acc is null;
COUNT(*)
----------
0
we can see 20 user commits (727-707), no "transaction rollbacks" (129-129), and all rows are updated. From application point of view, all the updates are successfully performed, even with a small UNDO Tablespace, which is not met one-run requirement.
This could be used as a workaround in case of UNDO Tablespace is limited.
3. unique_constraint_test_1
Run following code:
update test_t1 set id = rownum;
commit;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
exec unique_constraint_test_1;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
The output look as follows:
NAME VALUE
-------------------------------------------------- ----------
user commits 728
rollback changes - undo records applied 154923
transaction rollbacks 129
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=1
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=2
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=3
End committed, at Step=4
NAME VALUE
-------------------------------------------------- ----------
user commits 728
rollback changes - undo records applied 157962
transaction rollbacks 132
Again we see 3 (132-129) "transaction rollbacks", and no user commits (728-728), and hence no rows updated.
4. unique_constraint_test_2
Run following code:
update test_t1 set id = rownum, step = 0, acc = 0;
commit;
select id, ts, step, acc from test_t1
where id in (1, 900, 901, 1000, -1, -900, -901, 1000) order by step, acc, id;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
exec unique_constraint_test_2;
select n.name, s.value from v$mystat s, v$statname n
where s.statistic#=n.statistic#
and name in ('user commits', 'transaction rollbacks', 'rollback changes - undo records applied');
select id, ts, step, acc from test_t1
where id in (1, 900, 901, 1000, -1, -900, -901, 1000) order by step, acc, id;
The output look as follows:
ID TS STEP ACC
---- -------------------------- ---------- -------
1 22-SEP-2015 07:35:13 0 0
900 22-SEP-2015 07:35:13 0 0
901 22-SEP-2015 07:35:13 0 0
1000 22-SEP-2015 07:35:13 0 0
NAME VALUE
----------------------------------------- ----------
user commits 742
rollback changes - undo records applied 163307
transaction rollbacks 145
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
sql%rowcount=100 Updated_1, at Step=1
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=1
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=2
ORA-00001: unique constraint (K.TEST_T1_PK) violated, at Step=3
End committed, at Step=4
NAME VALUE
---------------------------------------- ----------
user commits 743
rollback changes - undo records applied 164201
transaction rollbacks 147
ID TS STEP ACC
---- ------------------------- ---------- -------
901 22-SEP-2015 07:35:13 0 0
1000 22-SEP-2015 07:35:13 0 0
-1 22-SEP-2015 07:35:13 1 1
-900 22-SEP-2015 07:35:13 1 9
we can see 1 user commits (743-742), 2 "transaction rollbacks" (147-145), 900 rows are updated, 100 rows not updated.
5. deadlock_test
Restore test table:
column name format a50
update test_t1 set id = rownum, name = null, acc = 0;
commit;
Open 3 Sessions, run following 3 scripts in 3 different sessions sequetially at time T1, T2, and T3:
exec deadlock_s1; --Session_1_T1
exec deadlock_s2; --Session_2_T2
exec deadlock_s3; --Session_3_T3
Check the updates by:
select id, ts, name, acc from test_t1 where id <= 3 order by id;
ID TS NAME ACC
-- -------------------------- -------------------------------- ----------
1 22-SEP-2015 07:35:13 Session_1_T1/Session_2_T2/ 2
2 22-SEP-2015 07:35:13 Session_2_T2/ 1
3 22-SEP-2015 07:35:13 Session_1_T1/Session_3_T3/ 2
All Sessions are terminated in about 120 seconds, and Session_1 throws Exeception:
ORA-00060: deadlock detected while waiting for resource, in Session_1_T1/
Row 1 updated twice (Session_1 and Session_2), Row 3 updated twice (Session_1 and Session_3),
Row 2 updated once (Session_2).
In fact, Row 2 was also updated once by Session_1, but it hit deadlock error, and the update is rollbacked. However, the other two row updates (Row 1 and 3) by Session_1 are still kept, thus committed.
Session_3 is blocked by Session_1, no transaction can be started (see v$transaction).
It will wait till Session_1's transaction terminated.
TestCode
--ensure no transaction alive on TEMPUNDO and all TEMPUNDO'terminated DML's undo_retention expired.
drop tablespace tempundo;
create undo tablespace tempundo
datafile '/testdb/undo/tempundo.dbf'
size 8m reuse autoextend off retention noguarantee
/
alter system set undo_tablespace = tempundo scope=both;
select name, value from v$parameter where name like '%undo%';
drop table test_t1;
create table test_t1
(id number, ts timestamp default systimestamp, step number, name varchar2(4000), acc number);
alter table test_t1 add constraint test_t1_pk primary key (id);
insert into test_t1 select level, systimestamp, 0, lpad('x', 4000, 'y'), null
from dual connect by level <= 1000;
commit;
select bytes from dba_segments where segment_name ='TEST_T1';
--8'388'608
create or replace procedure undo_tbs_test_1 as
l_step number := 0;
begin
loop
begin
l_step := l_step + 1;
exit when l_step > 3; -- limit the number of retries. without it, endless loop.
update test_t1 set step=l_step, name = lower(name) where id >= 2;
dbms_output.put_line('sql%rowcount='||sql%rowcount||' Updated_2, at Step='||l_step);
exit;
exception when others then
dbms_output.put_line(sqlerrm||', at Step='||l_step);
commit;
end;
end loop;
commit;
dbms_output.put_line('End committed'||', at Step='||l_step);
end;
/
create or replace procedure undo_tbs_test_2 as
l_step number := 0;
begin
loop
begin
l_step := l_step + 1;
exit when l_step > 20;
for i in 1..3 loop
update test_t1 set step=l_step, name = lower(name), acc=rownum
where acc is null and rownum <=50;
dbms_output.put_line('sql%rowcount='||sql%rowcount||' Updated_1, at Step='||l_step);
exit when sql%rowcount=0;
end loop;
exit;
exception when others then
dbms_output.put_line(sqlerrm||', at Step='||l_step);
commit;
end;
end loop;
commit;
dbms_output.put_line('End committed'||', at Step='||l_step);
end;
/
create or replace procedure unique_constraint_test_1 as
l_step number := 0;
begin
loop
begin
l_step := l_step + 1;
exit when l_step > 3; -- limit the number of retries. without it, endless loop.
update test_t1 set id = - mod(id, 998);
dbms_output.put_line('sql%rowcount='||sql%rowcount||' Updated_1, at Step='||l_step);
exit;
exception when others then
dbms_output.put_line(sqlerrm||', at Step='||l_step);
commit;
end;
end loop;
commit;
dbms_output.put_line('End committed'||', at Step='||l_step);
end;
/
create or replace procedure unique_constraint_test_2 as
l_step number := 0;
begin
loop
begin
l_step := l_step + 1;
exit when l_step > 3;
for i in 1..10 loop
update test_t1 set id = - mod(id, 998), step = l_step, acc = i where id > 0 and rownum <=100;
dbms_output.put_line('sql%rowcount='||sql%rowcount||' Updated_1, at Step='||l_step);
exit when sql%rowcount=0;
end loop;
exit;
exception when others then
dbms_output.put_line(sqlerrm||', at Step='||l_step);
commit;
end;
end loop;
commit;
dbms_output.put_line('End committed'||', at Step='||l_step);
end;
/
create or replace procedure deadlock_s1 as
begin
update test_t1 set name = name||'Session_1_T1/', acc = acc + 1 where id = 3;
-- updated, keep locked till committed
update test_t1 set name = name||'Session_1_T1/', acc = acc + 1 where id = 1;
-- updated, keep locked till committed
dbms_lock.sleep(60);
update test_t1 set name = name||'Session_1_T1/', acc = acc + 1 where id = 2;
-- rollback due to deadlock
commit;
exception when others then
dbms_output.put_line(sqlerrm||', in Session_1_T1/');
dbms_lock.sleep(60);
commit;
end;
/
create or replace procedure deadlock_s2 as
begin
update test_t1 set name = name||'Session_2_T2/', acc = acc + 1 where id = 2;
-- updated, keep locked till committed
dbms_lock.sleep(60);
update test_t1 set name = name||'Session_2_T2/', acc = acc + 1 where id = 1;
-- updated, keep locked till committed
commit;
exception when others then
dbms_output.put_line(sqlerrm||', in Session_2_T2/');
dbms_lock.sleep(60);
commit;
end;
/
create or replace procedure deadlock_s3 as
begin
update test_t1 set name = name||'Session_3_T3/', acc = acc + 1 where id = 3;
-- blocked 120 seconds by Session_1, no transaction started, see v$transaction
commit;
end;
/
