Wednesday, August 4, 2010

DDL s - Autocommit and Autonomous transactions

So finally convinced myself to overcome my laziness and write the second post ..and here you go...

Lets get started with understanding some discusson on DDL s and the affect that they can have on the uncommitted work in Oracle

Lets create a table in session1 called sess1
session1>create table sess1 (x int, y int);
Table created.

Now lets insert some data into the table
session1>insert into sess1 values (1,11);
1 row created.
session1>insert into sess1 values (2,22);
1 row created.
session1>insert into sess1 values (3,33);
1 row created.


session1>select * from sess1;
--------- ----------
         1         11
         2         22
         3         33

Lets create one more session and check for the data

session2>select * from sess1;

No rows selected

So far so good...Expected result...Excellent .
Lets commit in session1 and see the results
session1>commit;
Now lets check the data from session2
session2>select * from sess1;
---------- ----------
         1         11
         2         22
         3         33
Still expected behaviour from Oracle.
Lets insert some data into session1
session1>insert into sess1 values (4,44);

1 row created

Now do a DDL by trying to create a table in session1

session1> create table sess11 (x int, y intt);
 create table sess11 (x int, y intt)
                               *
ERROR at line 1:
ORA-00902: invalid datatype



OK, the create table failed because the data type was invalid


Lets go ahead and check the data in session2



session2>select * from sess1;


---------- ----------
         1         11
         2         22
         3         33
         4         44 --> newly inserted row in session1 


Oh, how are we seeing the new row (4,44) data in session2 even though we have not committed in session1. 
This is due to the fact that we tried doing a DDL in session1 and hence Oracle implicitly committed on our behalf and that was the reason that we were seeing the newly inserted data in session2.



   
So one has to be careful while doing DDL s so that they donot commit all the uncommitted work done in the session.

Lets see what happens when a successful DDL is run in session1.

session1>insert into sess1 values (5,55);

1 row created.


session1>create global temporary table gtt (x int, y int);


Table created.



session2>select * from gtt;


no rows selected


session2>select * from sess1;


         X          Y

---------- ----------
         1         11
         2         22
         3         33
         4         44
         5         55  --> newly inserted row


So the new table that we created and the new data (5,55) that we inserted in session1 are seen in session2. There need not be any explicit commit in session1 to see the newly created objects in session2. 

So why does Oracle commit on DDL ?


Because DDL must have been executed by Oracle (though the exact internal workings are not revealed) as

commit (implicit - part of DDL statement) ;
DDL statement
commit  
(implicit - part of DDL statement) ;

Since there must have been a commit before the DDL was executed, inspite of the failed DDL, we were still able to see the newly inserted data in the other session as in the first case and since there must have been a commit after the DDL, the newly created objects are seen in the second session as in the second case.


But why is this important to us developers :

Because not being able to commit /rollback at will is considered a bad transaction habit.

Many at times we keep working on a single session and keep inserting/deleting a lot of data from tables for our testing purposes and one DDL attempt will autocommit all the transactions and there is no ROLLBACK after that.


So how do we prevent DDL s affecting the un committed DML s.

Enter Autonomous Transactions :

Lets try to understand what is the difference in behaviour between Normal transaction and Autonomous transaction.


session1>insert into sess1 values (8,88) ;

1 row created.




session1>declare



  2  pragma autonomous_transaction;
  3  begin
  4  execute immediate 'create table sess11 as select * from sess1' ;
  5  end;
  6  /
PL/SQL procedure successfully completed.



session1>desc sess11;

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 X                                                              NUMBER(38)
 Y                                                              NUMBER(38)

So the new table sess11 has been succcessfully created via the anonymous procedure.

Now lets check if the newly inserted data (8,88) is seen in session2 


session2>select  * from sess1 ;



         X          Y

---------- ----------
         1         11
         2         22
         3         33
         4         44
         5         55
         6         66
         7         77


Wow, not seen in the session2 . The difference lies in the declare section of the anonymous procedure 
pragma autonomous_transaction



So how does a Autonomous transaction work and what are the implications of it for us developers??
I will write more about it in my next post (hopefully i will do it soon)  and until then happy reading.


Illiyaz Mohammad


























No comments:

Post a Comment