> I'd suggest changing your approach as I doubt the > problem is CF-related. Consult your DBA and figure out > why it's locking -- which query does the table lock > belong to, and why is it locking it? It may be poorly > written SQL that's the culprit, or some DB design flaw
> The above assumes you're not using Access as your DB. > If you're using Access, then the lock is because > someone has your database open in Design Mode. In that > case, either ask that person to get off the DB or > upgrade to a real DB. Well, they're replicating their sql servers for load-balancing purposes, so I'm sure it's not Access ... I was guessing it was MS SQL Server, but I didn't want to make the assumption. :) There are occasions on which poorly written CF can cause things like this -- at one of my previous jobs we had a problem with SQL Server deadlocking as a result of a situation where aggregate data was not being properly updated in the database when data was entered ( or removed ) and the end result was that rather than actually fixing the problem they decided to update the aggregated data on every page view with a query like: UPDATE tClass SET StudentCount = (SELECT COUNT(studentid) FROM tStudentEnroll WHERE classid = tClass.ClassID) WHERE tClass.ScheduleID = #scheduleid# This was on a page displaying all the classes during a day for all the rooms in the school. Because the query was so broad it wound up affecting most of the table and locking the entire table on every view of that CF page -- and because similar related pages also wanted to know the student count, the same was done on those related pages, accept that they usually didn't include the WHERE clause, so it would blanket update the _ENTIRE_ table, regardless of whether or not a given record needed to be viewed... The original intention had been to try and reduce load on the sql server by keeping track of the student count without having to select(count) every time they needed to know or compare to determine if there was an empty seat in a given class... The end result was both _increased_ sql server load and frequent sql server deadlocks thanks to poor CF design. I'm betting the problem here isn't as serious. :) Isaac Dealey www.turnkey.to 954-776-0046 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
