Home » Other » General » Asynchronous commit Oracle 10g X Delphi desktop (Oracle 10g)
Asynchronous commit Oracle 10g X Delphi desktop [message #549948] Wed, 04 April 2012 18:56 Go to next message
demetriusmds
Messages: 5
Registered: April 2012
Location: Brasil
Junior Member
We have a Delphi application (using dbexpress/dbexpora.dll) accessing a RAC 10g with five nodes. The clients machines use 9i clients.

We have clear evidences that a series of (simple) DML statements were done (one commit for each statement) a report was generated by the application showing the changes just done and hours later, queryng the database (tables with the original data and the auditing tables populated by triggers - application cannot change them), changes were not there anymore.

According to logs generated by Oracle, a series of rollbacks (around 70), with no other DMLS among them, were done quickly after a period of time that, we have clues to hint, was very stressfull to the RAC (database structure).

Everything points to a possible asynchronous commit, generated, either by a (possible?) driver configuration (changing session parameters) or by a failure in comunication between client's driver and the database itself due to (who knows?) incompatibilities. between client's driver versioin and oracle 10g database.

Database instance "commit_write" parameter is null (did not verify all nodes, but I can do it if necessary).


Thanks a lot if any of you can enlighten my search for an explication.
(ACID characteristics of Oracle, to us, seems not to be true anymore until we can identify the problem and reproduce it.)
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #549950 is a reply to message #549948] Wed, 04 April 2012 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

uncommitted DML changes can only be "seen" by the same session that issued them.

>Everything points to a possible asynchronous commit
I have NO idea what is an "asynchronous" COMMIT; as differentiated from a "normal" COMMIT;
as far as I know, only one flavor of COMMIT exists for/with/in Oracle.

DBMS_LOGMNR can reveal exact what DML changes were made & when.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #549956 is a reply to message #549950] Wed, 04 April 2012 21:46 Go to previous messageGo to next message
demetriusmds
Messages: 5
Registered: April 2012
Location: Brasil
Junior Member
"uncommitted DML changes can only be "seen" by the same session that issued them."

Ok. I know that.
In fact, the application sends a single DML then commit, then sends the next DML, then commit.

Thats exactly what happened: The user ordered four inserts and commited then. The window of the aplication does not allow the user to exit it before getting the database answer saying the commit was successfull. Then the user printed a report and the changes were there (there are documents proving it). Hours later the data were not there anymore, as if a rollback had been ordered.

In fact, the database log of DMLs shows that a rollback happened in a strange way :a lot of them at the same time. This king of thing could not happen in an application like that once that for each DML there was a commit linked to it (DML made manually by the programmer, which eliminates the possibility that a grid component, for example, could have cached the results).

I have already received the DMLS logged and, as I said, everything points to the idea that:
1) application requested the commit;
2) the database could not do it at that time and answered: "Ok. Keep going that I will commit it as soon as I can."
3) It happened many times, the report was printed.
4) database crashed someway, lost the scheduled commits and rolled back the DMLs (as the log shows).

Asynchronous commit can be set in Oracle 10g changing the commit_write parameter to, for example, "BATCH,NOWAIT" (Cannot post the link here, but, if you don't mind, you can google "commit_write oracle" and you findout the desired documentation about it) .

Another possibility points to a possible problem in the comunication between oracle driver and oracle database itself:
1) application requested the commit;
2) the database could not do it at that time and answered: "Ok. I'm having problems here, wait a little more and I will commit. Don't give up!."
3) The drive understand it as an "Ok. Go ahead!", possibly because this is a warning that did not exist in older versions of Oracle.
4) Application keep inserting records and commiting the same way. (But nothing was commited, in fact.)
5) User prints the report (in the same session he can see the uncommited data) with all the changes.
6) User aborts the session cause to some network delay.
7) Database (hadn't commited yet; it had just asked the application to wait)sees thar the user aborted the session and rollsback all DMLs done.

I know that there is a lot of imagination in these sequences of events, but If I do not consider them I'll have to accept the hypoteses that oracle does not have the comfortable ACID properties we expect from a robust database.

Thanks a lot for your attention.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #549958 is a reply to message #549956] Wed, 04 April 2012 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/node/93

>Asynchronous commit can be set in Oracle 10g changing the commit_write parameter to, for example, "BATCH,NOWAIT"
If you orverride default behavior, you accept the risks that result.
"The obvious danger of asynchronous commits is that your DBMS is no longer ACID compliant."
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #549962 is a reply to message #549956] Thu, 05 April 2012 00:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your unsderstanding of te COMMIT_WRITE parameter is incorrect, it affects only recoverability afte a crash. I think you are misunderstanding what your application is doing. Neither you nor ORacle can rollback a committed transaction.

Quote:
According to logs generated by Oracle, a series of rollbacks (around 70), with no other DMLS among them, were done quickly after a period of time
What are these logs?
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550036 is a reply to message #549958] Thu, 05 April 2012 07:41 Go to previous messageGo to next message
demetriusmds
Messages: 5
Registered: April 2012
Location: Brasil
Junior Member
"The obvious danger of asynchronous commits is that your DBMS is no longer ACID compliant."

Yes, I do know its obvious.
Thats why I said:

"Database instance "commit_write" parameter is null (did not verify all nodes, but I can do it if necessary)."

So, if the value is null, Oracle assumes (must assume, according to documentation) the default behavior: "IMMEDIATE, WAIT".

"What are these logs?"
Exactly what you recommended:Miner Archive Archivelog (DBMS_LOGMNR).


"Your unsderstanding of te COMMIT_WRITE parameter is incorrect, it affects only recoverability afte a crash"

Maybe. But there are evidences that all the nodes work working at the edge when these thansactions occurred. So, I guessed that if database could not do the commit in real time and, before effectivelly starting doing them, the client station closed the session, the database may have rolledback, instead of commiting, all the DMLS.

But I agree that this possibility (like the other one) is very hard to consider in fact. I'm doing that only because I have a documental proof (in fact, many of them) in my hands, application is running with no problems for more then 4 years and database changed recently (changed three Itaniums nodes for 5 non-Itaniums some months ago).

I'm not a DBA. I'm an analist (did not develop the application) with a good knowlege in both sides (Oracle and software development). I'm just working trying to help the team.

Regards.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550042 is a reply to message #550036] Thu, 05 April 2012 08:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
When a session issues a COMMIT, it will hang until the commit is complete. If you are seeing rollbacks, then the transactions were not committed. You need to investigate your assertion that the transactions were committed. Your hypothesis that the database might say "Ok. Keep going that I will commit it as soon as I can." is incorrect.

The truth may be in your last post:
Quote:
the client station closed the session, the database may have rolledback, instead of commiting, all the DMLS.
Depending on how your application is written, it could be that exiting the application issues a rollback. A disorderly shutdown (of either client or server) will always trigger a rollback. But either way, if a COMMIT has been issued it cannot be rolled back.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550048 is a reply to message #550042] Thu, 05 April 2012 08:34 Go to previous messageGo to next message
demetriusmds
Messages: 5
Registered: April 2012
Location: Brasil
Junior Member
Hello, John.

Well, after the auditing tables, the application was really my (everyone's) first option to blame. But, as I said, I did no see (neither any of the other analysts) a single way that could allow application to roll back so many DMLs at the same time/transaction. But even if it could be possible, application user could never print a report with the uncommited (or rolled back) statements because he can't access any other funcionality without leaving the window that he used to insert/update/delete the records in the database.

Of course, I will return to the application, talk again to the users and search for other clues.

Without these clues I can only blame the comunication beween database and application. If you (or anyone) have any knowlege of violation of ACID principles due to some problem in layers between application and Oracle Database itself I'd be glad to know.

Thanks a lot for your attention.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550052 is a reply to message #550048] Thu, 05 April 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>"Database instance "commit_write" parameter is null (did not verify all nodes, but I can do it if necessary)."

can this be over rode at the SESSION level?
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550053 is a reply to message #550052] Thu, 05 April 2012 10:02 Go to previous messageGo to next message
demetriusmds
Messages: 5
Registered: April 2012
Location: Brasil
Junior Member
No.
As far as I know, if the session changes (via alter session) commit_write parameter, it will be used by the session, overriding database's default value.

Well, I requested the machines used to examine them and see if, for some reason, they (their driver or their application transaction component) are changing this parameter in session level (I'll probably have access to these workstations only on next Monday/Tuesday). Application's code itself is very simple and do not order an alter session commit_write parameter.
Re: Asynchronous commit Oracle 10g X Delphi desktop [message #550057 is a reply to message #550053] Thu, 05 April 2012 11:51 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are looking in the wrong place, man. If the transaction was rolled back, then it was never committed. That parameter is irrelevant.
Presumably, your application is not issuing COMMIT statements, no matter what you have been told. You say that you used the log miner: in that case, you can prove this.
Previous Topic: General question
Next Topic: CCNA dumps
Goto Forum:
  


Current Time: Thu Mar 28 17:49:40 CDT 2024