[thelist] coldfusion,mysql - record locking?

[Bimal Shah]

We have a record locking issue, we are using MySQL with
Coldfusion and have one or more queries on different pages
which are updating,adding or removing from a number of tables.

We have a query which inserts a record and then queries it
to find the record id which was inserted, obviously we wish
to make sure these SQL queries are run one after another without
another query running in between (which may be on another page).

We cannot use <CFTRANSACTION since MySQL does not support transactions.

We cannot use <CFLOCK since the other tables may be querying
one or more tables (and this table may be one of them)... using
multiple <CFLOCKs within <CFLOCKs is not a good idea.

We cannot use MySQL LOCK since if something goes wrong (page aborted etc)
the tables will remain locked.
Also the query (which checks the last inserted record) would need to 
release the previous lock to do the query and
thus defy the point of making the process sequential.

Example:

LOCK HERE

<cfquery.... INSERT RECORD HERE

<cfquery.... FIND OUT LAST INSERTED RECORD

UNLOCK HERE

Making sure no other query on any other page has access
to the table we are inserting in/querying back.
---
Bimal Shah (Senior Web Developer)
(T) 020 7240 5858 (F) 020 7240 5859
(E) bimal at venus.co.uk
Venus Internet Limited http://www.venus.co.uk