Re: Locking Table

There are two horns to this dilema:

If you use the table approach, you must use FOR UPDATE in your select to lock the row and prevent another user from obtaining the same value. This keeps the row locked until commit or rollback. The second transaction now also tries to select the current value FOR UPDATE. The second transaction waits until the first commits or rolls back. Guaranteed not to lose a number, guaranteed not to duplicate a number.

If you use an Oracle 'sequence', the number is incremented outside the transaction! The next transaction does not have to wait for the first to commit or roll back. But, if the first DOES roll back, the number has been bypassed and is lost forever. Guaranteed not to duplicate a number, guaranteed not to slow down your transactions.

If you MUST have no skipped numbers, then you MUST SERIALIZE using a table and accept the contention.

If you can allow skipped numbers, then you can use the sequence and avoid the slow down.

If you MUST have no skipped numbers and cannot allow the serialization slow down, then 2 + 2 = 5!

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.---
Received on Tue May 25 1999 - 17:20:27 CDT