Re: NextNumbers Tables

From: Pablo Sanchez <pablo_at_dev.null>
Date: Tue, 11 Feb 2003 22:15:07 -0600
Message-ID: <Xns931FD830522C9pingottpingottbah_at_216.166.71.233>

"Jason W. Paul" <nobody_at_nowhere.com> wrote in news:3e49a868$0$99433$a04e5680_at_nnrp.fuse.net:

>

>>
>> 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.

Which DBMS's are you thinking about?

For Oracle, Sybase ASE and SQL Server, the UPDATE in tx #1 will block the UPDATE in tx #2

With Oracle, you can do it one of two ways:

1. Use the RETURNING clause to UPDATE/SELECT atomically, or
2. Use transaction control. The UPDATE will block all others until a COMMIT so you'll be able to SELECT the value.

With Sybase ASE and SQL Server you can do it one of two ways as well:

1. start a transaction and SELECT the value after the UPDATE, then COMMIT to release locks, or
2. Set a local variable within the UPDATE:

   UPDATE mytable

      set _at_my_next_val = next_val,
          next_val     = next_val + 1
      ...

   COMMIT;    SELECT _at_my_next_val + 1 -- return the next value

> FYI -- I disagree with this method entirely as it will, in high volume
> environments, create severe "hotspots" on these tables. If you don't
> have high volume, it might not be an issue.

To some extent I agree. With Oracle, I'd rather use SEQUENCES and with Sybase ASE I'd rather use the IDENTITY data type and the equivalent with SQL Server.

Alternatively, one can implement a next_val table and have batches of values for each 'SPID' within ASE and SQL Server. This way you avoid the hotspot issue.

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com

Received on Wed Feb 12 2003 - 05:15:07 CET