Subject SQL-SELECT/DELETE unique value from table in multiuser app
Author Dale Mullins
Hello everyone. I am writing an application using Firebird and Delphi
6 using the EasySoft ODBC driver.

This is probably more of a basic SQL question, but any help would be

I have two tables, DOWNTIME_ACCOUNTS and VISITS.

The DOWNTIME_ACCOUNTS table will have 1 field, ACCOUNT_NUMBER which
will be populated perodically by another application. It will be a
unique number which can be used only once.

Multiple users can be accessing the database at one time and when they
need to insert a new record into the VISITS table, I need to "select"
the next available ACCOUNT_NUMBER then "delete" it.

How do I guarantee that an ACCOUNT_NUMBER does not get used more than
once if two or more users are inserting at the same time? I think I
want to lock the DOWNTIME_ACCOUNTS table, get the first record, delete
the record, then unlock the table. Am I thinking correctly? :) If so,
how is this implemented via Delphi & ODBC?