LOCK TABLE with ColdFusion Front

[GnegDevelop]

I am running MySQL in junction with Cold Fusion on a Windows 2000 platform. In order to create an enviroment that assures that an INSERT followed by a SELECT will return the last created ID, I used a LOCK TABLE statement but now the server always returns that table as locked. I tried to run an UNLOCK TABLES command but it still returns the table as locked when I check the status of the thread. I reloaded the service and could again select from the table but again when I ran the code below the same problem occured. Please HELP!


<!--- LOCK the table --->
<cfquery datasource=&quot;#request.main_dsn#&quot; name=&quot;lockTables&quot;>
LOCK TABLES member WRITE, user WRITE
</cfquery>

<!-- Insert member -->
<cfquery datasource=&quot;#request.main_dsn#&quot; name=&quot;insertcust&quot;>
INSERT INTO member(
FirstName,
LastName,
Email,
member_type_fk
)
VALUES( '#form.firstname#',
'#form.lastname#',
'#form.email#',
1
)
</cfquery>

<!--- Insert new username --->
<cfquery datasource=&quot;#request.main_dsn#&quot; name=&quot;insertUser&quot;>
INSERT INTO User( UserName, Password, cust_fk )
VALUES( '#form.Login#',
'#form.Password#',
LAST_INSERT_ID() )
</cfquery>

<!--- UNLOCK the table --->
<cfquery datasource=&quot;#request.main_dsn#&quot; name=&quot;unlockTables&quot;>
UNLOCK TABLES
</cfquery>

 

[swampBoogie]

You don't need to lock the table to ensure that.

 

[sleipnir214]

This page of the MySQL online manual:

http://www.mysql.com/doc/en/Miscellaneous_functions.html

states that the last insert id returned by the last_insert_id() function is maintained on a per-connection basis. Table locking is unnecessary to maintain good returned IDs.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[GnegDevelop]

I've gotten rid of the LOCK TABLES but when I try to run the LAST_INSERT_ID through a <cfquery> I get a SYNTAX error. Can I use the LAST_INSERT_ID function with cold fusion?