Re: NextNumbers Tables

From: Heinz Huber <XhhuberX_at_no-racon-linz.at-no>
Date: Fri, 14 Feb 2003 09:14:54 +0100
Message-ID: <3e4ca57e$0$23508$91cee783@newsreader01.highway.telekom.at>

Jason W. Paul wrote:

>>If you want to do it that way, I'd go for the first approach.
>>Just on caveat: The way to go is first increment the number then select

>
> it.
>

>>Otherwise, you'll run into trouble in a multiuser environment where two
>>users want to insert at the same time.

>
> How does doing one "first" solve this problem. The *only* way around
> it is to make it atomic -- where they both (increment & select) appear to
> happen at the same time. This will depend on your DBMS's transaction
> support. Doing either one "first" will result in the same issue! Think
> about it.

Using transactions, you reach atomicity. If you have autocommit on, then of course there is no difference what you do first. On the other hand, then you don't have any atomic multi statements.

The reason for the difference is the way locking normally works unless you have a very high isolation level (which leads to other lockouts): - Select places a shared lock on a row
- Update places an exclusive lock on a row

Now picture two connection trying to get the next number simultanously:

Select first:

c1: Select current number -> shared lock
c2: Select current number (still the same) -> shared lock
c1: Update -> exclusive lock (might not even be possible!)
c1: Commit -> release locks
c2: Update -> exclusive lock
c2: Commit -> release locks

Both connections get the same number or you might even get a deadlock.

Update first:
c1: Update number -> exclusive lock
c2: Update number -> wait for exclusive lock to be cleared, then exclusive lock

c1: Select current number
c1: Commit -> release locks
c2: Now exclusive lock can be made; do update -> exclusive lock
c2: Select current number
c2: Commit -> release locks

This way, the second connection has to wait for the first to clear the exclusive lock.

Regards,
Heinz Received on Fri Feb 14 2003 - 02:14:54 CST