Re: autocommit ON but no commits visible in v$sqlarea

In a few words:

unlike most other lock-based RDBMSes, commits in Oracle do not release any scarce system resources, like locks, and thus their frequency should be dictated by application requirements. In Oracle, you commit when *all* work is done in a logical transaction, not after every single statement you need to issue during that transaction. If your transaction involves only one statement - so be it, commit right away. If it consists of several operations - commit only after *all* of them are successful, otherwise you risk leaving the system in logically inconsistent state if you commit part of the work and then something goes wrong. With autocommit you're out of control over this - the driver commits every single atomic operation within logical transaction independently. Imagine you are doing a transfer from one bank account to another (common example): you need to deduct some amount from source account (update 1), and add it to another account (update 2), possibly logging the whole operation (insert 1). Now imagine that update 1 was ok, but update 2 failed for some reason - with autocommit you have update 1 committed already, and source account lost some money, but target account didn't receive them and the whole operation was not logged because update 2 didn't work. Your database is now logically inconsistent (though it's fully consistent from RDBMS's point of view). If you commit only after all three operations were successful, you're pretty safe. And if anything goes wrong, you rollback the whole logical transaction, too.

Plus see what Jim Kennedy said about log sync. Not only this wastes log buffer space, it also causes too frequent physical I/O, which serializes all other processes while taking place (Oracle has to make sure that redo made it safely to the disk before it can allow anything else to happen.)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


<dev_at_null> wrote in message news:em3n0v8fvmf6msp5vp34l73m3k0pv3qbqq_at_4ax.com...


> On Wed, 25 Dec 2002 13:44:39 GMT, "Jim Kennedy"

> <kennedy-down_with_spammers_at_attbi.com> wrote:

> 

> > Try not using auto commit.  The waits will probably go away if you do.

> > Autocommit is not a good idea.

> > Jim

> 

> Could you expand on that for me.

> 

> I'll take a guess that one good reason is that it is a pretty d'uh approach to

> implementing business logic.

> 

> Are there also good technical reasons to avoid this? ...as in; what's the

> difference between autocommit and issuing an explicit commit on every statement?

> 

> TIA

> 

> Steve Roach