Re: NextNumbers Tables
Date: Fri, 14 Feb 2003 09:14:54 +0100
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
>>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:
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.
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.
Heinz Received on Fri Feb 14 2003 - 09:14:54 CET