[thelist] coldfusion,mysql - record locking?

[rudy]

> just run the last_insert_id function() within a query: "SELECT
> last_insert_id()". It returns the last auto_increment value generated
> by a specific thread. Don't worry about the locking.

bingo

thanks, jay

there's a similar function for sql/server called @@identity

when you have something like this, use it -- locking is not necessary

but what do you do when there isn't a function available like this?  is it
still necessary to lock the pair of statements (insert, followed by select
to read back the key)?

no

using a numeric autonumber/identity/sequence key like this has many
advantages, but regardless of your reasons, this number becomes the
primary key used as the value of foreign keys in other tables linked to the
one you're inserting into

from a data analysis point of view, though, this is only a "surrogate"
key -- the real primary key is the column or columns that uniquely identify
the row

sure, there are tables where a real primary key does not exist, but those
are special cases and quite rare

for example, let's say you are adding names to a customer table -- the real
primary key would be lastname/firstname

what if you have two people with the same name?  then the real primary key
is lastname/firstname/address, and you keep doing this type of analysis
until you settle on a collection of columns that is guaranteed to be unique

(aside: a good database design will include a unique constraint to enforce
this choice)

so what you do is insert the record, then do a select using the values for
firstname/lastname/address/whatever that you just inserted

in other words, you can use a surrogate key and let it be assigned a number
automatically, and when you want to read back the value of the key you just
inserted, just issue a query using the "real" primary key values, which you
should still have

note: even if you do allow duplicate names, you can still do the select
with the added condition that you want the one with the latest autonumber


rudy