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

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.

Billy  Verreynne  wrote:
... And one writer will only block another writer when both are writing the very same rows...

Not fully true...

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...

;-)

Billy  Verreynne  wrote:
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.

Oooh! steady on there with the lead pipes lads.

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...

Adrian wrote:

Autocommit, in a client-server context is just a performance optimisation that simply removes the need for an additional server round-trip.

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.

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.

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?

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

Adrian wrote:

Or perhaps you thought that when the OCI programming guide says:

Or perhaps I thought we were talking about autocommit.

www.google.com/search?q=autocommit

Toon Koppelaars wrote:
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

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.

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.

3360 wrote:

Adrian wrote:

Or perhaps you thought that when the OCI programming guide says:

Or perhaps I thought we were talking about autocommit.

www.google.com/search?q=autocommit

We are 3360. You seem strangely out of touch today.

Adrian wrote:

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.

The thin driver does not use OCI at all.

Adrian wrote:

3360 wrote:

Adrian wrote:

Or perhaps you thought that when the OCI programming guide says:

Or perhaps I thought we were talking about autocommit.

www.google.com/search?q=autocommit

We are 3360. You seem strangely out of touch today.

Yes, I am really not following why you are taking yet another thread down an irrelevant detour.

3360 wrote:

Adrian wrote:

3360 wrote:

Adrian wrote:

Or perhaps you thought that when the OCI programming guide says:

Or perhaps I thought we were talking about autocommit.

www.google.com/search?q=autocommit

We are 3360. You seem strangely out of touch today.

Yes, I am really not following why you are taking yet another thread down an irrelevant detour.

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.

I was merely responding, mostly to BV's "always manually commit" comment. Which is technology agnostic.

"autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency"

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.

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.

Adrian wrote:
3360 - why do you follow me around trying to flame me?

I don't see that 3360 was doing any flaming. Your the only person who's mentioned flaming in these threads.

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.

"autocommit is a hack used by some other databases because of their inferior design in dealing with locks and concurrency"

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.

Which still hasn't been shown to be proven, hence why 3360 challenged it.

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.