Thanks for the reply Snake. We aren't specifying the isolation level, but doesn't that leave it up to the database default? The odd bit is that there are 162 tables in this system and thousands of queries. There are only two queries ever having any problem and the only thing they share is that they are updating/inserting into one specific table. After I posted the other day, I removed a subquery from the update query and placed it in a separate query which runs before the update. Now I'm not sure if it has actually eliminated the issue and it certainly doesn't seem like it should have done, but I haven't gotten another one of those error emails since I did that.
Thanks, Ferg Snake wrote: > 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:239606 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
