Re: Best approach for a "gap-less" sequence

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jorge Godoy wrote:
> Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
>
>> Pre-allocate records. The (primary key?) field would have the
>> numbers already filled in, but all the rest of the fields in each
>> record be NULL, blanks, zeros or indicator values ("~~~~~~~~~~",
>> -999999999, etc).
>>
>> Then create a single-field table called, for example, CUR_MAX_VALUE
>> that gets incremented as part of each transaction. To serialize
>> access, transactions would need an EXCLUSIVE lock on the table.
>
> What's the difference to having just the table with the sequence where I make
> an exclusive lock to get the value while inside the transaction? This
> approach seems more complicated since I'd have to exclude records that match
> the "not-used" pattern.

The use of CUR_MAX_VALUE "should" ensure that you never have gaps,
since a rollback or process death would not update CUR_MAX_VALUE.

Your WHERE clauses would *not* have
AND NAME <> "~~~~~~~~~~".
They would say
AND SEQ_NO <= (SELECT CUR_MAX_VALUE FROM CUR_MAX_VALUE).

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE34jdS9HxQb37XmcRArBMAJ9ZS3/daUhhKu5f22nfo2m2AlXRfgCg7IfG
amkfOOnaJ1UzKRdlyZfJlvE=
=KCnM
-----END PGP SIGNATURE-----