Re: Correct locking / cftransaction
Matthew Chambers wrote: I've just checked and the DB has been create with MyISAM. Am I in trouble? Not with regard to this specific problem. Just use cftransaction and the MySQL last_inserted_id() function. In general though, don't expect any other benefits from transactions as long as you use MySQL/MyISAM. That you can rely on it in this instance to give you back the right ID should not be seen as an indication that MyISAM is suitable for any purpose at all, let alone the reliable storage of your data. Out of interest: I've just been reading the CF livedocs and it says; serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. The effects of a serializable transaction are defined in terms of operations that can not happen. Any side-effects, such as the locking of tables, are implementation defined. I believe the CF manual should not make claims about things that are outside the control of CF and I will file a bug against the documentation this weekend. So would you still say that SERIALIZABLE is the way to go? It is not required for your application. And if you want to really use transactions you need at the very least a new table handler (InnoDB) and perhaps even a new database first. And if your new table handler / database uses some form of multiversioning it is quite likely that a serializable transaction does not exclusively lock tables. I've been trying to encourage the guys to upgrade to SQLsever this will be a good argument!!! I would be very careful making that argument. If you happen to run into somebody who understands the transaction locking models of MySQL/InnoDB and MS SQL Server she will turn that argument around on you. Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274175 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Correct locking / cftransaction
Hi guys, I'm trying to find some up to date (MX7) info on how to correctly use CFTRANSACTION when writing to a DB (MySQL). All I am doing is one CFQUERY for the INSERT statement and then a second CFQUERY for the last_insert_id(). I think MySQL v5 allows you to have more than one SQL statement per CFQUERY however I'm using v4. Cheers ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274058 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Hi guys, I'm trying to find some up to date (MX7) info on how to correctly use CFTRANSACTION when writing to a DB (MySQL). All I am doing is one CFQUERY for the INSERT statement and then a second CFQUERY for the last_insert_id(). I think MySQL v5 allows you to have more than one SQL statement per CFQUERY however I'm using v4. Cheers ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274060 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274153 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274160 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274162 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
ahhh..true...i shouldn't be responding to posts while working. Thread from January about the same concepts... http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:44021 On 3/29/07, James Holmes [EMAIL PROTECTED] wrote: If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274165 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
Hi James, I've just checked and the DB has been create with MyISAM. Am I in trouble? Out of interest: I've just been reading the CF livedocs and it says; serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. So would you still say that SERIALIZABLE is the way to go? I've been trying to encourage the guys to upgrade to SQLsever this will be a good argument!!! By the way: if we did upgrade to SQLserver would I still need to use SERIALIZABLE? Cheers Matthew If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274167 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
