I run into this issue occasionally, and always find some kind of work-around,
but I’ve finally decided to address it head-on. The scenario is this:
- You insert
a record into the database.
- You need the ID of the row you just inserted so you can insert additional
rows in other tables referencing the first row you inserted.
- You’re worried about your code being thread-safe, so you consider
using locks, transactions, UUIDs, etc. But what is the best way?
The easiest and most database-independent way of doing this is to generate a UUID
and use it for the primary key for the first insert, and the foreign key for any
subsequent inserts. That’s fine for certain apps, but for the app I’m working on
now, I need to use sequential, auto incremented integers as keys. I’m using the
MySQL function LAST_INSERT_ID() to get the last inserted ID, however the ID’s scope
is the current connection, so if I use two CFQUERY tags (one to insert the row,
and one to get the last inserted ID), I believe it’s possible I could get the wrong
ID if another row is inserted between the time I do my insert and the time I select
the last inserted ID. So I’m going to try:
- Sending two queries to MySQL at the same time. One to do the insert, and one
to do the select. Both statements will use the same connection, and problem
solved. I haven’t had any luck with this so far, though. I’m not sure MySQL supports
this as I keep getting SQL syntax errors.
- Putting both CFQUERY tags in a transaction (they actually already are in a
transaction), and using the isolation level of serializable. I’m potentially
sacrificing some performance, but it should be miniscule. This is a very
good solution if it works, but I’m not 100% positive that it will work, though
I am hopeful.
- Using an exclusive lock. If all else fails, this will definitely work, although
the disadvantage is that I will only be taking database operations that
originate from one CF server into account. In other words, another server that
is unaffected by the lock can slip a new row into the database at just the right
moment and trip me up. I would much rather the lock occur at a lower level — actually
in the database, if possible.
I’ll post my results. In the meantime, anyone have any additional insight?