This content has been marked as final.
Show 40 replies
-
1. Re: How to verify the data got committed
32685 Nov 21, 2011 11:04 AM (in response to 793663)Personally I would say don't rely on autocommit as it's not a feature of the database. The best way to make sure something is committed is to explicitly issue the COMMIT. So put one in your script where it makes sense to i.e. when you reach the end of a logical transaction. Don't just issue commits after each DML statement for the sake of it. If you have 1 delete statement and 5 inserts that replace some existing data (for example) commit after all of those statements have completed rather than after each one.
HTH
David -
2. Re: How to verify the data got committed
Billy~Verreynne Nov 21, 2011 11:32 AM (in response to 793663)Concur with David.
Autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency. A billion locks in Oracle has the same overhead as 1 lock - none. Nothing. No overheads.
In Oracle, readers do not block writers. Writers do not block readers. And one writer will only block another writer when both are writing the very same rows.
Thus no need to autocommit in order to reduce lock resources and prevent blocking of readers by writers.
Prepare a COMMIT statement at the same time than you prepare the other cursors in your code. Call the DML cursors. When the business transaction has completed, call the COMMIT cursor.
That simple. -
3. Re: How to verify the data got committed
Toon Koppelaars Nov 21, 2011 1:30 PM (in response to Billy~Verreynne)Billy Verreynne wrote:
Not fully true...
... And one writer will only block another writer when both are writing the very same rows...
Scenario: parent-table and child-table and enabled FK between them. Only one row in parent-table, no rows in child table.
Writer one deletes parent-row, but doesn't commit yet.
Writer two inserts child for that parent-row and will block...
;-) -
4. Re: How to verify the data got committed
894085 Nov 21, 2011 2:10 PM (in response to Billy~Verreynne)Billy Verreynne wrote:
Oooh! steady on there with the lead pipes lads.
Concur with David.
Autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency. A billion locks in Oracle has the same overhead as 1 lock - none. Nothing. No overheads.
In Oracle, readers do not block writers. Writers do not block readers. And one writer will only block another writer when both are writing the very same rows.
Thus no need to autocommit in order to reduce lock resources and prevent blocking of readers by writers.
Prepare a COMMIT statement at the same time than you prepare the other cursors in your code. Call the DML cursors. When the business transaction has completed, call the COMMIT cursor.
That simple.
Autocommit, in a client-server context is just a performance optimisation that simply removes the need for an additional server round-trip.
In very high performance systems it's usual to execute your transaction and switch on autocommit for the final action so that you don't incur an extra server round-trip and the obvious network waits.
As far as the OP's question goes, functionally there is obviously no difference between an "auto" committed transaction and a "manually" committed transaction. Any commit time exceptions that occur (e.g. from deferred constraints or whatever) will be propagated back in the same way.
If your client return code is success and whatever error structures you have bound show no error or warning conditions, then it's safe to trust the database to have done the commit properly, any client side library bugs notwithstanding.
As far as monitoring, Mr OP, you can view current transaction info in v$transaction... -
5. Re: How to verify the data got committed
6363 Nov 21, 2011 2:06 PM (in response to 894085)Adrian wrote:
Nonsense. Autocommit is about transactions only, and it is a client setting that causes the client to automatically issue a commit after each statement. Additional commits do not optimize anything, there are the same number of instructions from client to database and forcing a commit after everything only results in performance degradation if anything, not optimization. And automatic commits do nothing but compromise transactional integrity.
Autocommit, in a client-server context is just a performance optimisation that simply removes the need for an additional server round-trip.
In very high performance systems it's usual to execute your transaction and switch on autocommit for the final action so that you don't incur an extra server round-trip and the obvious network waits.
This is known as making stuff up.
As far as the OP's question goes, functionally there is obviously no difference between an "auto" committed transaction and a "manually" committed transaction.
Correct. Thereby negating any optimization benefits claimed for auto commits. -
6. Re: How to verify the data got committed
894085 Nov 21, 2011 2:23 PM (in response to 6363)Calm down 3360 - I'm not suggesting "additional commits". I'm suggesting that using autocommit on your last call in a transaction is a performance optimisation. Perhaps you didn't read what I wrote fully, and just jumped in to flame someone?
Please tell me what you think I am making up? perhaps you could explain how avoiding a network round trip is not a performance optimisation?
Or perhaps you thought that when the OCI programming guide says:
"By using the OCI_COMMIT_ON_SUCCESS mode of the OCIStmtExecute() call, the application can selectively commit transactions after each statement execution, saving an extra round-trip."
It is "making things up" too? -
7. Re: How to verify the data got committed
Toon Koppelaars Nov 21, 2011 2:36 PM (in response to 894085)The question is, does the JDBC driver (assuming that's what the OP is using), make use of this OCI setting?
The proof is in the pudding, by doing a sql-trace and establishing that JDBC does not send and automatic seperate COMMIT, but instead uses that setting.
Many other 'middleware/client' layers do the automatic separate commit.
Edited by: Toon Koppelaars on Nov 21, 2011 3:35 PM -
8. Re: How to verify the data got committed
6363 Nov 21, 2011 2:36 PM (in response to 894085)Adrian wrote:
Or perhaps I thought we were talking about autocommit.
Or perhaps you thought that when the OCI programming guide says:
www.google.com/search?q=autocommit -
9. Re: How to verify the data got committed
894085 Nov 21, 2011 2:48 PM (in response to Toon Koppelaars)Toon Koppelaars wrote:
The OP unfortunately doesn't say. I was more replying to the sentiment that "autocommit" as a concept had no value and was merely for those "other inferior" databases. As a concept, it's useful for this optimisation.
The question is, does the JDBC driver (assuming that's what the OP is using), make use of this OCI setting?
The proof is in the pudding, by doing a sql-trace and establishing that JDBC does not send and automatic seperate COMMIT, but instead uses that setting.
Many other 'middleware/client' layers do the automatic separate commit.
Edited by: Toon Koppelaars on Nov 21, 2011 3:35 PM
However, that doesn't make a great deal of sense to me (at least in all cases). How would JDBC know implcitly that it is your last statement before you were about to COMMIT? What if you have a conditional commit?
I'd love to see your trace file proving that though, along with a code example. Could you post it up here?
If they have found an optimising path where this is possible it would certainly prove my point.
EDIT: Wait, I missed your point. I thought you were saying you had done a sql-trace and shown that JDBC would automatically switch on commit on success on the final call where an execute() commitrans() could be optimised. Yes, you make a good point. However if JDBC/OCI does not use OCI effectively, I'd be pretty disappointed. Let me check and get back on that. -
10. Re: How to verify the data got committed
894085 Nov 21, 2011 2:42 PM (in response to 6363)3360 wrote:
We are 3360. You seem strangely out of touch today.Adrian wrote:
Or perhaps I thought we were talking about autocommit.
Or perhaps you thought that when the OCI programming guide says:
www.google.com/search?q=autocommit -
11. Re: How to verify the data got committed
6363 Nov 21, 2011 2:48 PM (in response to 894085)Adrian wrote:
The thin driver does not use OCI at all.
EDIT: Wait, I missed your point. I thought you were saying you had done a sql-trace and shown that JDBC would automatically switch on commit on success on the final call where an execute() commitrans() could be optimised. Yes, you make a good point. However if JDBC/OCI does not use OCI effectively, I'd be pretty disappointed. Let me check and get back on that. -
12. Re: How to verify the data got committed
6363 Nov 21, 2011 2:49 PM (in response to 894085)Adrian wrote:
Yes, I am really not following why you are taking yet another thread down an irrelevant detour.3360 wrote:
We are 3360. You seem strangely out of touch today.Adrian wrote:
Or perhaps I thought we were talking about autocommit.
Or perhaps you thought that when the OCI programming guide says:
www.google.com/search?q=autocommit -
13. Re: How to verify the data got committed
894085 Nov 21, 2011 2:55 PM (in response to 6363)3360 wrote:
3360 - why do you follow me around trying to flame me?Adrian wrote:
Yes, I am really not following why you are taking yet another thread down an irrelevant detour.3360 wrote:
We are 3360. You seem strangely out of touch today.Adrian wrote:
Or perhaps I thought we were talking about autocommit.
Or perhaps you thought that when the OCI programming guide says:
www.google.com/search?q=autocommit
I never claimed that the JDBC thin driver uses OCI. The OP never stated whether he used JDBC at all, OCI or thin.
I was merely responding, mostly to BV's "always manually commit" comment. Which is technology agnostic.
I merely commented that autocommit is an important high performance optimisation when used correctly, and as such we shouldn't discredit it as merely a tool for inferior lock-resource databases."autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency"
Nothing irrelevant there at all. Clearly you are still upset about a discussion on a previous thread, and I encourage you to let it lie. It is now you disrupting this thread. -
14. Re: How to verify the data got committed
BluShadow Nov 21, 2011 3:03 PM (in response to 894085)Adrian wrote:
I don't see that 3360 was doing any flaming. Your the only person who's mentioned flaming in these threads.
3360 - why do you follow me around trying to flame me?
I never claimed that the JDBC thin driver uses OCI. The OP never stated whether he used JDBC at all, OCI or thin.
It was your post above that brought OCI into the discussion to justify what you were saying that had been challenged. Yet the OP made no mention of OCI.
I was merely responding, mostly to BV's "always manually commit" comment. Which is technology agnostic.
Which still hasn't been shown to be proven, hence why 3360 challenged it.
I merely commented that autocommit is an important high performance optimisation when used correctly, and as such we shouldn't discredit it as merely a tool for inferior lock-resource databases."autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency"
Nothing irrelevant there at all. Clearly you are still upset about a discussion on a previous thread, and I encourage you to let it lie. It is now you disrupting this thread.
No, I don't think it was anything to do with a previous thread, more to do with generalized statements that 3360 believed were not correct. Nothing wrong with challenging such statements, especially if they could mislead the OP.
It's fair enough 3360 has asked for clarification, so why not give clarification as to what is meant by your original response and in what context it fits with the OP's question.