EXPERT RESPONSE
I think I understand the question, and it is commonly
asked in the context of a column value that is automatically
generated by the database. In Microsoft Access, this type of column
is called an autonumber; in Microsoft SQL/Server, it's called
an identity column. In MySQL, it's an
auto_increment, and in Oracle, it's a sequence.
In PostgreSQL, you can use either a sequence or a serial.
The problem typically arises when inserting a parent record
(e.g. order) and then several child records (e.g. order items).
Each child record will need the value of the parent's
primary key to use as its foreign key.
There are two ways to go about this. The more efficient
method involves using a special database function which
will return the value that was assigned by the database.
In Microsoft Access and SQL/Server, you can use the @@IDENTITY
function, and in MySQL, you can use the mysql_insert_id()
function. Check your database documentation for details.
These functions, if properly invoked, always return
the correct value of the just-inserted record.
A strategy that you should be careful with is
to attempt to get the last value through the use
of a query such as SELECT MAX(ID) FROM...
This strategy fails in a multi-threaded environment,
because in between the time you do the INSERT, which assigns
the next autonumber, and the SELECT MAX(ID), which
gets the latest autonumber, some other
thread could have executed another INSERT.
Some people feel that this problem can be
side-stepped by enclosing the INSERT and SELECT MAX
statements in a transaction block. While
technically correct, this also means that the table
is locked longer than it would be for individual statements.
This goes against your requirement to retrieve
the value "while the insertion process continues."
Note that @@IDENTITY and mysql_insert_id() function
correctly in a multi-threaded environment.
An alternate strategy is to "query back" the row
using columns other than or in addition to the autonumber. Usually, an
autonumber column is a surrogate key and the table
will have other columns which might provide a way to determine
the just-inserted entry. If another column exists which is unique,
use it. If no unique column exists, then you can
use MAX(ID) in conjunction with other columns.
For example, let's assume that column1
is an autonumber, and that you don't need to mention
this column in the INSERT. (This syntax varies from
database to database -- for example, in Oracle, you must assign
the sequence's NEXTVAL.)
insert into theTable
(column2, column3)
values ('Joe Bfstplk', 'Dogpatch')
select max(column1)
from theTable
where column2 = 'Joe Bfstplk'
It doesn't matter that other threads have
inserted additional rows between your INSERT and your SELECT,
because you're not going after the latest overall, you're going after
the latest with that particular name, presumably the one you
just inserted. The benefit here is that you
don't need a transaction block.
If column2 is unique, you don't
need max(column1) -- just select column1. No subsequent
thread will insert another 'Joe Bfstplk' row, so just
go get it.
If column2 is not unique,
max(column1) along with the WHERE clause should get the
column1 value of the row you just inserted. Note that this strategy
does have a hole -- some other thread could have inserted
another 'Joe Bfstplk' right after yours; you will have
to decide if your design is such that you can live with the risk.
|
