Seriously Technical server Behavior question

[riluve]

Does ColdFusion maintain a single connection to datasources or does it create a seperate connect for each user?

Here is what I am trying to do:

I would like to generate a new record in a MySQL DB and retrieve the automatically generated index with a single query to prevent user collision.

So far, I have not been able to find a way to accoumplish this from the MySQL side (using SQL). It has been suggested, however, that I do an INSERT and follow that by a SELECT LAST_INSERT_ID() as this function preserves the last inserted ID for each client.

Here then is my ColdFusion concern - It will be the only actual MySQL client and that will work against the SELECT LAST_INSERT_ID()'s ability to descern between final users and re-introduce the possibility of user collision.

.

 

[jmille34]

Does mysql work with <cflock>?

 

[imstillatwork]

If you just did an insert, it is often easiest to simply SELECT the exact same data that was INSERTED, besides, you'll have those variables still handy from the insert anyways. This would, of course, require a unique set of data. - The reason why many DB guys hate auto increment indexes, and use UUID instead... The uuid can be created and known before the insert even takes place, making subsequent relational inserts or selects accurate every time.

CFLOCK does not care about databases. It locks the CFSERVER into single thread mode. If the SERVER is only running one thread at a time.





Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[jalpino]

If you need to 'lock' a set of sql commands in a single transaction, use <cftransaction>, it will provide the isolation levels that you need. However if the transaction spans multiple requests, insert\update a timestamp with the db entry and also persist that stamp in the users session. The next request to update verify that the timestamp on the db is not greater than the one in session. If the two don't match up, then you know the record was modified by another user.

As far as how CF handles connections to the DB, I think if you provide username and password attributes in your CF query tags, CF has to open a new connection rather than reuse an old one.

Hope this Helps,

JAlpino

 

[Wullie]

Does ColdFusion maintain a single connection to datasources or does it create a seperate connect for each user?

It depends on how you setup your datasource.

Go into the CFAdmin and open the DSN, then click on "Show Advanced Settings". In here, you will see a checkbox for "Maintain connections across client requests".

Hope this helps


Wullie

Fresh Look - Quality Coldfusion 7/Windows Hosting
YetiHost - Coming Soon

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell