Recently i was asked a nice question by one of my team mates which ultimately led to this post. It was a question from one of the certification exams and the question was nice because as a developer one has to know the distinction between SAVEPOINT and ROLLBACK.
Now lets get back to some Basics of transaction management.
What is a save point ? Loosely speaking, it is a kind of Milestone in a transaction where you tell Oracle "Hey, Please remember these results as of this time in this transaction as i might need to come back to these results in future BEFORE I COMMIT ". I can have multiple SAVE POINTS in a transaction.
Lets see with an example :
We create a table called my_table and insert some rows into it .
sess1>drop table my_table;
Table dropped.
sess1>create table my_table (x int primary key, y int);
Table created.
sess1>insert into my_table values (1,11);
1 row created.
sess1>insert into my_table values (2,22);
1 row created.
Lets create a savepoint a
sess1>savepoint a;
Savepoint created.
sess1>select * from my_table;
X Y
---------- ----------
1 11
2 22
Lets insert some more rows into the table and create one more savepoint
sess1>insert into my_table values (3,33);
1 row created.
sess1>savepoint b;
Savepoint created.
Now that we have created two logical milestones, lets see the impact the savepoints have of selecting the tables.
sess1>select * from my_table;
X Y
---------- ----------
1 11
2 22
3 33
Lets rollback to the first savepoint a and see the results
sess1>rollback to savepoint a;
Rollback complete.
sess1>select * from my_table;
X Y
---------- ----------
1 11
2 22
We cannot see the newly inserted row (3,33) as we have rolled back to the logical savepoint a.
Lets see the affect of a complete rollback without savepoint
sess1>rollback;
Rollback complete.
sess1>select * from my_table;
no rows selected
So a rollback has completely rolled back all the transactions irrespective of the savepoints.
Armed with these basics, let try to solve the question that was asked.
The question was which all rows would be inserted into the table my_table when the procedure proc_my_table would be executed .
sess1>drop table my_table;
Table dropped.
sess1>create table my_table (x int primary key, y varchar2(5));
Table created.
sess1>create or replace procedure proc_my_table as
2 lv_name VARCHAR2(5);
3 lv_cnt number ;
4 BEGIN
5 INSERT INTO my_table VALUES ( 1, 'Illi' );
6 SAVEPOINT A;
7 INSERT INTO my_table VALUES ( 2, 'vijay' );
8 SAVEPOINT B;
9 INSERT INTO my_table VALUES ( 3, 'fiat' );
10 SAVEPOINT C;
11 BEGIN
12 ROLLBACK;
13 INSERT INTO my_table VALUES ( 3, 'fiat' );
14 EXCEPTION
15 WHEN dup_val_on_index THEN
16 ROLLBACK TO B;
17 BEGIN
18 INSERT INTO my_table VALUES ( 2, 'palio' );
19 INSERT INTO my_table VALUES ( 4, 'punto' );
20 INSERT INTO my_table VALUES ( 5, 'AVeryVerynicecar' );
21 SAVEPOINT D;
22 EXCEPTION
23 WHEN dup_val_on_index THEN
24 ROLLBACK TO A;
25 WHEN others THEN
26 ROLLBACK TO D;
27 END;
28 END;
29 INSERT INTO my_table VALUES ( 5, 'linea' );
30 COMMIT;
31 END;
32 /
Procedure created.
Lets execute the procedure
sess1>exec proc_my_table;
PL/SQL procedure successfully completed.
Now lets check the data in the table my_table
sess1>select * from my_table ;
X Y
------- -----
3 fiat
5 linea
Weird that only 2 rows were inserted.
Lets try to understand what exactly transpired when the proc was executed.
Until line 11, everything goes normal and there are 3 rows inserted
Its the line 12, that deleted the previously inserted 3 rows.
So until now the total no.of rows in the table was ZERO.
Line 13 inserts one row into the table.
So until now the total no.of rows in the table is ONE.
Since the previous insert was successful lines 14 - 28 will not be executed.
Next only the line 29 will be executed and committed in line 30.
Hence there are only 2 rows in the table my_table.
What would happen if we slightly modify the procedure and instead of ROLLBACK on line 19, ROLLBACK to a savepoint.
sess1>create or replace procedure proc_my_table as
2 lv_name VARCHAR2(5);
3 lv_cnt number ;
4 BEGIN
5 INSERT INTO my_table VALUES ( 1, 'Illi' );
6 SAVEPOINT A;
7 INSERT INTO my_table VALUES ( 2, 'vijay' );
8 SAVEPOINT B;
9 INSERT INTO my_table VALUES ( 3, 'fiat' );
10 SAVEPOINT C;
11 BEGIN
12 ROLLBACK to c; -- this line is changed
13 INSERT INTO my_table VALUES ( 3, 'fiat' );
14 EXCEPTION
15 WHEN dup_val_on_index THEN
16 ROLLBACK TO B;
17 BEGIN
18 INSERT INTO my_table VALUES ( 2, 'palio' );
19 INSERT INTO my_table VALUES ( 4, 'punto' );
20 INSERT INTO my_table VALUES ( 5, 'AVeryVerynicecar' );
21 SAVEPOINT D;
22 EXCEPTION
23 WHEN dup_val_on_index THEN
24 ROLLBACK TO A;
25 WHEN others THEN
26 ROLLBACK TO D;
27 END;
28 END;
29 INSERT INTO my_table VALUES ( 5, 'linea' );
30 COMMIT;
31 END;
32 /
Procedure created.
sess1>exec proc_my_table;
PL/SQL procedure successfully completed.
sess1>select * from my_table ;
X Y
---------- -----
1 Illi
5 linea
SO what happened in the changed proc?
The exception on line 14 was invoked and transcation rolled back to savepoint B.
NOw the internal Begin is kicked off and due to duplicate insert on line 18, the exception on line 22 is invoked and hence the transcation is rolled back to savepoint A.
So as of now there is only one row (1,'Illi') in the table my_table.
Next line 29 is invoked and the row (5,'linea') is inserted and committed.
Hope this post helps some way in understanding of the savepoints and rollback.
Illiyaz
http://9im6jjs425770o267f7u4gprd0l4tbn2.a.blogger.gmodules.com/gadgets/ifr?v=fffbf927a01c1573ec0acdcadfbfe574&container=blogger&view=editor-sidebar&lang=en&url=http%3A%2F%2Fwms.assoc-amazon.com%2FGoogleGadgets%2Famzn_monetize.xml&country=ALL&libs=core%3Adynamic-height%3Agoogle.blog%3Agoogle.blog.editor%3Alocked-domain%3Arpc%3Asetprefs%3Asettitle%3Aviews&parent=http%3A%2F%2Fwww.blogger.com%2F&mid=1278534008228#
