>Does anybody know if there is a function to retrieve the next unique ID
>number for a record to be made in the future?
I believe you can reliably allocate sequence numbers from a "sequence
table" (a table used only to dole out sequence numbers) like this:
create table sequence ( seq int not null auto_increment primary key);
(depending on intended use, you may need to change the size of
the integer type, or use an unsigned type).
When you wish to get a sequence number, issue these three queries:
#1: insert into sequence values (null);
#2: select last_insert_id();
Fetch the single-row result from this and put it in variable $s,
which gets substituted into the following query before sending it:
#3: delete from sequence where seq = $s;
Now use $s as your sequence number to insert into another table.
Note that this still works if several clients try this sequence of
queries on different connections, WITHOUT transactions and WITHOUT
any explicit locking between the queries, but with arbitrary
interleaving between queries by different clients. Query #1
establishes the sequence number (implicit locking during that query
prevents handing out duplicate numbers). Query #2 fetches it, and
since last_insert_id() is connection-specific, nothing another
connection does will affect the result. Query #3 cleans up the
sequence table. Since only one client was handed the sequence
number for this row, only one client will try to delete it. Normally
the table will only have entries for clients executing between
query #1 and query #3, or clients that bombed between query #1 and
query #3 (thus, during a lull in activity, the steady state of the
"sequence table" should be empty).
Gordon L. Burditt
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
