You need to check what the default is for your database then. If you have multiple queries trying to insert/update at the same time, then you may want to use REPEATABLE_READ, as long as you are sure that simultanious updates/inserts will not affect the integrety.
-----Original Message----- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: 05 May 2006 15:28 To: CF-Talk Subject: Re: MySQL deadlocks and timeouts 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:239608 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
