Re: Implicit Commit...pls remind me.

Thank you for the correction on autocommit. I always turn it off, so my memory was a little blurry. I did more research to correct my errors and expand on what Sybrand pointed out.

In Windows, if you close the SQL*PLUS gui program without entering an explicit EXIT command SQL*PLUS behaves like the crash condition, and any uncommitted transactions are rolled back by PMON. If you leave SQL*PLUS by typing EXIT, then SQL*PLUS issues a commit. I tested to verify. In Unix environments, the typical use of SQL*PLUS is command line and you have to kill the process to duplicate this behavior (I tested this too). In practice I always use explicit rollbacks and commits, so I have been running on autopilot with respect to this behavior.

Sorry for the miscues.

One clarification, if autocommit is set to Y then a stored procedure call or an anonymous block will be treated as a single statement, and autocommit will not affect the behavior until after the statement returns. Consequently, an anonymous block or stored procedure could have explicit rollback or commit behavior that would not be directly affected by autocommit. Similarly, there could be multiple SQL statements within the anonymous block or stored procedure, and they would be treated as a single transaction with respect to autocommit.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Weiss
mailto:chris_at_hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts


"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:ud7e7gssqe7ub6_at_corp.supernews.com...



> Sorry to say so, but your understanding of autocommit is incorrect.

> Autocommit on means *every* single statement is committed automatically.

> A client exiting sql*plus will *always* commit, unless it explicitly rolls

> back.

> A crash however will force pmon to roll the transaction back.

>

> Regards

>

>

> --

> Sybrand Bakker

> Senior Oracle DBA

>

> to reply remove '-verwijderdit' from my e-mail address

>

> "Chris Weiss" <chris_at_hpdbe.com> wrote in message

> news:aav05g$22g8$1_at_msunews.cl.msu.edu...

> > If your stored procedure code contains a commit, then your commit should

> be

> > applied successfully.  However, if you are relying on the client to

issue


> > the commit, your transaction should get rolled back *IF* the Windows

> client

> > crashed abnormally.

> >

> > If you need to commit regardless of what Windows does, then the PL/SQL

> > should contain an explicit commit or rollback on error.  Tools and

drivers


> > have configurable commit behavior.  In my SQL*PLUS for windows, the

> > autocommit is set to OFF/false.  If you set autocommit to ON/TRUE then

> your

> > client will issue a commit at the end of your session.

> >

> > So... The answer to your question is that it depends on how the client

is


> > configured, where the transaction was expecting the rollback or commit,

> and

> > how the client crashed - blue screen, lost channel, timeout, etc.

> >

> > Good Luck!

> >

> >

> > --

> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> > Chris Weiss

> > mailto:chris_at_hpdbe.com

> > www.hpdbe.com

> > High Performance Database Engineering

> > Available for long and short term contracts

> >

> >

> > "R Chin" <rchin_at_panix.com> wrote in message

> > news:aautpc$mr$1_at_reader1.panix.com...

> > > Someone please remind me.....

> > >

> > > 8.1.7/AIX

> > >

> > > If I am in the middle of a lengthy transaction (PL/SQL) like..

> > >

> > > procedure MAIN

> > > is

> > > begin

> > >   proc_1;

> > >   proc_2;

> > >   proc_3;

> > >   ........

> > > end;

> > >

> > > executing MAIN in SQL+...

> > > If the client (windoz) crashes on me....am I gonna get an implicit

> commit

> > ?

> > > What if I run each proc separately, one at a time....then crash....?

> > >

> > > Thanks

> > > Rob

> > >

> > >

> > >

> >

> >

>

>