| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Locking Table
I fell into a similair problem. But to my best knowlage I couldn't use
SEQUENCE. The problem is the following, I have:
table (
today DATE,
runno NUMBER
)
And I also have :
UNIQUE INDEX on table (today,runno)
The reason why I cannot use SEQUENCE is that I don't know how to reset it every day - atomicaly. The only way I actually was able to reset sequence was to drop it and create it again, but this unfortunately cannot be protected by transaction comit (as far as I know).
So I ended up in a small "increnent" program like in Martin example, with small disadvantage, that sometimes the comit may fail - and I have to rollback and start scenario again.
Anybody knows a better way?
-R
Martin Hepworth wrote:
> hi_wean_at_my-dejanews.com wrote:
> >
> > Hi there,
> >
> > I've a table, tbl_runno as following:
> >
> > appln_type VARCHAR2(10)
> > runno NUMBER(5)
> >
> > Here are the scenarios,
> > 1. Open a Oracle session, logon as 'user1'
> > 2. SELECT runno FROM tbl_runno; return as 5
> > 3. UPDATE tbl_runno SET runno = runno + 1; should be = 6
> > 4. Do not COMMIT till all the transactions are successfully executed.
> > 5. At the same time (1 second later), another Oracle session, logon as
> > 'user1' as well
> > 6. SELECT runno FROM tbl_runno; return as 5 still.
> >
> > Questions:
> > 1. How can the 2nd user get runno = 6, when the 1st user still has not
> > issue the COMMIT statement?
> > 2. Is it any LOCK available in Oracle 8.0.5?
> >
> > Can anyone help me on these? Thank ou very much!
> >
> > Best Regards
> > HW
> >
> > --== Sent via Deja.com http://www.deja.com/ ==--
> > ---Share what you know. Learn what you don't.---
>
 |
 |