Re: Lock record

"Arnaud B." <balaya_at_club-internet.fr> a écrit dans le message news: 9k9a3u$hho$1_at_front6m.grolier.fr...
> > Answer embedded but one question:
> > why don't you use sequences?
>
> Because I haven't build the DB, I am just using it.
>
> > > Hi,
> > >
> > > This is surely a commun question, but I can't get any answer.
> > > A table on my DB contains all counters. One record of this table
 contains
> > > the current order number. What is the correct way to use and change the
> > > current order number ?
> > > Is this correct :
> > > -Lock the record (How ?)
> > > -Read the current order number
> >
> > 2 first steps in one statement:
> > select name, value from counters where name='<my counter>' for update of
> value;
>
> I guess "for update of value" will lock the record. Does it lock it until
> the commit appens or do we need to have the update first.

Record is locked until the commit or rollback. Remember that a DDL statement (create, drop, ...) implicitly commits.

>
> What happens if an error occurs between those statement ? Will the record
> stay locked ?
>

If it is a DML statement (insert, update, delete) error, record is still locked because only the current statement is rollbacked. If it is a session or an instance error, all the transaction is rollbacked and the lock is freed.

> >
> > > -Write the next one
> >
> > update counters set value=value+1 where name='<my counter>';
> >
> > > -Unlock the record (How ?)
> >
> > commit;
> >
> > > -Use the read order number to add a new order
>
> Where can I get help file about this ? online help ?

Oracle Concepts Part V "Data Access", chapter 16 "SQL and PL/SQL" and 17 "Transaction Management".
Online on technet at url:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/toc.htm You have to subscribe but it is free.

>
> Thanks for your help
>
> Arnaud
>
>

Michel Received on Thu Aug 02 2001 - 02:28:47 CDT