Thursday, June 26, 2008

Transactional processing in Messaging Systems

As With any other db/Persistence API , queueing/messaging API must(May
be It should be re-emphasized) support transactional API
and no wonder Oracle AQ supports this.
Set based processing , Enqueueing and Dequeueing array of messages, XML
based payloads
possible with AQ.
While Oracle AQ supports Transactional API in Persistent Queues which it
conveniently leverages the Oracle Database Tables (Queue Tables,IOTs to
be precise)
it was not supporting transactional API in Buffered messages.
EnQueueing and DeQueueing into Persistent Messages have the same
overhead as of doing Select,Insert and/or delete into IOT tables as the
case maybe.
Buffered Messages dont have this overhead with the downside of retention.

Tuesday, June 10, 2008

Question on "nested txns" vs "autonomous txns"]

For people who have requirements to code a Autonomous txn in Oracle which is called in general as a "Nested Top level (sub)transaction",
Things to watchout are esp in the context of temp tables,
Autonomous transactions just happen in another transaction but within the same session of parent/main txn and so your temp tables/gtt s created in main txn should be accessible from within autonomous txn except for the uncommitted changes done to it in main txn immediately (Infact if you cannot even populate a same gtt/temp table which has been already populated in main txn, you would get error in autonomous txn )

I did some study and found that use of autonmous txn is 99% for auditing/logging purposes and as Thomas Kyte(asktom) says any other use of them is sure a problem in design/code.You would need to really check your logic for such a use case before you decide some txn as a autonomous txn.


I would summarise the following for autonomous and nested txns:

Autonomous Txn:
  • Autonomous txn is just a different independent transaction from the main/parent txn but within the same session . Hence does not share transactional resources as that of main/parent txn.
  • Cannot see uncommitted changes in main/parent txn for consistency reasons. (Note: Consistency is always at transactional level and not at session level)
  • After a commit in autonomous txn you return immediately to the transactional context of main/parent transaction.i.e after a commit in autonomous txn you are back to parent txn.
  • Autonomous txn since they operate within the same session they can access the gtt/temp tables but cannot see data in them already populated by main/parent txn. Infact they get error when they try to populate them if done already in parent txn. But they can populate them if not already populated in parent/main txn which would be a rare case.
  • Changes made in autonomous txn are visible to parent/main txn based on isolation level set by main/parent transaction using the "set transaction isolation level .." statement in pl/sql. Oracle,by default makes committed changes visible(i,e "read committed" isolation level is default for any DML statement level) but "serializable" isolation level can be set for a transaction level using "set transaction isolation level serializable" statement for multi-statement read consistency. i.e the main/parent transaction would not see any committed changes made later by other transactions including autonomous txn which is also a different txn. Hence if your parent/main txn starts with a set transaction ..serializable it wont see any committed changes done in the autonomous child txn of it. Normally you would use this "serializable" isolation level for a short time OLTP txns and most often go with default "read committed" statement level.
  • Exceptions raised from within autonomous txn get rolled back to transaction level and not to statement level.
  • Use of Autonomous txn in XA/distributed env was not supported in 9i and not sure of complications in later releases.
  • Autonomous txn use cases are very rare and 99.99% they are for logging/auditing purposes.

Nested Txn:
  • For Oracle nested txn simple mean a transaction done from within a parent/main txn as i explained already.You can only set savepoints and rollback to them as you know already. You can use JDBC 3.0 standard savepoints interface for this in java or use pl/sql savepoints to rollback incrementally as you said.
  • Transaction isolation levels can also be set using jdbc APIs. Oracle as said above only support default "read committed" and "serialization" levels.
  • In Oracle Nested txns always see uncommitted changes in parent/main txn and changes in nested child txns are also always visible for parent txn.

Please see autonomous and nested transaction example.

Autonomous txn Example (In PL/SQL)
create table audit_test
(
name varchar2(20),
join_date date,
identifier varchar2(200),
log_id number
)
/
Truncate table audit_test
/
Create or replace procedure commit_test
is
pragma autonomous_transaction ;
v_nr number;
begin
select nvl(max(log_id),0) into v_nr from audit_test;
dbms_output.put_line('Maximum before autonomous txn In Child:'||v_nr); --(0)Autonomous Child txn wont see uncommitted changes in Parent.
insert into audit_test values('laksA',sysdate-1,'TestA',2);
commit; --Autonomous Child txn has to a commit/rollback always
end;
/
declare
v_nr number ;
begin
set transaction isolation level serializable name 'Parent'; -- named 'Parent' txn
-- This main/parent transaction sees db as of this time for multi -statement consistency
-- Without "serialization" isolation level this main txn would see the committed changes of autonomous child txn below

insert into audit_test values('laks',sysdate,'Test',1) ;
commit_test; -- calls autonomous child txn
select max(log_id) into v_nr from audit_test ;
dbms_output.put_line('Maximum after autonomous txn In Parent : '|| v_nr); --Output should be 1 with "serializable" and 2 without it in parent txn.
rollback ; -- Doesnt affect committed changes of the autonomous child transaction.
end;
/


Nested Transaction eg (In PL/SQL_)
create table audit_test
(
name varchar2(20),
join_date date,
identifier varchar2(200),
log_id number
)
/
Truncate table audit_test
/
Create or replace procedure commit_test
is
v_nr number;
begin
-- set transaction name 'Child' ; You cannot start a true nested txn like this.
select nvl(max(log_id),0) into v_nr from audit_test ;
dbms_output.put_line('Maximum before child txn in Child:'||v_nr); --(1) Nested Child txn always sees uncommitted changes in Parent txn
insert into audit_test values('laksA',sysdate-1,'TestA',2);
commit; --Everything done in Child as well as in Parent prior to callign Child gets committed.
end;
/
declare
v_nr number ;
begin
set transaction isolation level serializable name 'Parent'; -- named 'Parent' txn
-- This main/parent transaction sees db as of this time for multi -statement consistency
-- Parent would always sees uncommitted/committed changes in nested child txn.
insert into audit_test values('laks',sysdate,'Test',1) ;
commit_test; -- calls nested child txn named 'Child'
select max(log_id) into v_nr from audit_test ;
dbms_output.put_line('Maximum after Child Txn in Parent '|| v_nr); --Output should be 2 in parent txn always.
rollback ; --No Use at all .
end;
/


Looks like there is no true nested transaction API support in Oracle.

All these transaction concepts esp isolation level for a transaction implemented by DBMS vendors are requirements from TPC(Transaction Processing performance council) which sets few standards and req, to publish benchmark results.
JTA/JTS (java transaction API/java transaction service) is also a driving force for Transaction API provided by vendors. Oracle implementation of transaction API is much different from other DB vendors and also for performance/integrity reasons Oracle doesnt provide some features.