CODEWITHSUNDEEP
oracle-databasetransactionsoracle-call-interfaceocci
JackTheSkipper
JackTheSkipper

Reputation: 3

Resuming Oracle Database transactions after reconnecting

I was given the job of stabilizing part of a legacy application. It is a ~20 year old C++ application that now uses an Oracle 12.2 database for persistence (even for UI code). Interaction between the two is done using the Oracle Call Interface (OCI).
Currently I am trying to "fix" database transactions after an ungraceful disconnect (e.g. changing from one access point to another inside the same network). After reconnecting to the database I want the client to resume the transaction from the last session. That way the changes in that transaction are not rolled back.

I believe this would be beneficial, since I can't be sure what DML was executed and what was not when starting a new transaction. So resuming the old one would make it possible to see what was executed already.

Maybe I am completely off, but I am obviously open for better suggestions. Surely there is some kind of framework or pattern that I can't seem to find, because I am looking for the wrong keywords.

Best regards
Jan

Upvotes: 0

Views: 343

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10506

As I mentioned in my comment, DBMS_XA may be one way, but perhaps it's better to think of alternative architectures (or fixing the network?!) The proviso would be that you know when the connection is about to be disconnected.

See my old notes in slide 79 of https://www.oracle.com/technetwork/topics/php/highperf-php-preso-405765.pdf

Start with the schema:

drop table mytable;
create table mytable (id number, salary number);
insert into mytable values (1, 100);
insert into mytable values (2, 300);
commit;

Then try these three snippets in three different SQL*Plus sessions:

Session #1

Update the first salary:

var rc number
exec :rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMNOFLAGS);
update mytable set salary = salary * 1.1 where id = 1;
select * from mytable;
exec :rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);

select * from mytable;

The queries show that no change is visible after the transaction is suspended:

        ID     SALARY
---------- ----------
         1        110
         2        300

        ID     SALARY
---------- ----------
         1        100
         2        300

Session #2

Update the second salary:

var rc number
exec :rc := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMRESUME);
update mytable set salary = salary * 3 where id = 2;
select * from mytable;
exec :rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);

select * from mytable;

Output shows that the transaction can be resumed, and now both salaries have been updated in it. After the transaction is suspended again, no change is shown:

        ID     SALARY
---------- ----------
         1        110
         2        900

        ID     SALARY
---------- ----------
         1        100
         2        300

Session #3

var rc number
exec :rc := DBMS_XA.XA_COMMIT(DBMS_XA_XID(123), TRUE);

select * from mytable;

The transaction is committed. Output is:

        ID     SALARY
---------- ----------
         1        110
         2        900

Upvotes: 1

Related Questions