What type of isolation are you using in your CFTRANSACTION, if the answer is none, then you are using SERIALIZABLE by default, which is the highest level and slowest performance. This means nothing else can read or write the locked table or rows until the transaction block has finished and can thus result in the problem you are having. If you have other areas of the site that need to read that that data, then you should use "READ_COMMITTED" isloation. I would suggest you read the cfdocs and fully understand the use of cftransaction and isolation levels.
-- Snake -----Original Message----- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: 03 May 2006 20:54 To: CF-Talk Subject: MySQL deadlocks and timeouts I'm getting a deadlock error quite frequently on one specific page in my app. I'm on the latest MX 7 with MySQL 5. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.12a-nt-log]Deadlock found when trying to get lock; try restarting transaction <br>The error occurred on line 732. I'm also getting another Lock error on the same page, though not quite as frequently. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.12a-nt-log]Lock wait timeout exceeded; try restarting transaction <br>The error occurred on line 799. Now the thing is that these are just really simple queries in a cftransaction (one insert and one update). However, these tables are accessed from all over the app and I'm sure that's where the problem's actually coming from. There is some traffic load on the site, but it's nothing overwhelming. All I'm really wondering is if anyone else has had any strange locking troubles with MySQL and if so, what you did to work it out. I'm getting the first one approximately 10 times a day and the second one maybe half that. These queries are each running maybe 250-300 times a day, while theses tables are accessed a few thousand times a day. Thanks, --Ferg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:239474 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
