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  |
 |
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 #549956 is a reply to message #549950] |
Wed, 04 April 2012 21:46  |
|
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 #549962 is a reply to message #549956] |
Thu, 05 April 2012 00:06 |
John Watson
Messages: 8960
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 |
|
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 |
John Watson
Messages: 8960
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 #550057 is a reply to message #550053] |
Thu, 05 April 2012 11:51 |
John Watson
Messages: 8960
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.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 16:25:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
