Hi Ajas. Do you have any way of monitoring the SQL that is being passed across from CF to MSSQL ( preferably something better than SQL profiler but that will do in a pinch).
Once you verify the SQL that is being sent to MSQL from CF, analyze the SQL to see if the expected index is actually hit when the query is ran through the SQL engine. That may identify a bottleneck or unexpected behavior right there. If the expected index is not being "hit" then you can revise the SQL to include a SQL to force the index usage. Also, if you have a monitoring tool that is able to capture the SQL in real time when being passed see if there are concurrent SQL threads running that are causing deadlocks, especially "blocking" locks. This would indicate multiple sql threading executing simultaneously and competing for the same SQL resources. I would also begin to check if the tables that the SQL is running against has "row level" or "table level" locking. I have seen cases before when somehow the tables unknowingly became configured for "table" level locking and that caused the deadlock. Hopefully, these checks can get you started with trying to isolate the cause of the deadlock. On Fri, Oct 30, 2009 at 4:55 PM, Ajas Mohammed <ajash...@gmail.com> wrote: > Hi, > > Just wondering if anyone faced the SQL Server deadlock error returned by > ColdFusion. WE are using SQL Server 2000 and ColdFusion 7. The error > returned is > > Error Executing Database Query. [Macromedia][SQLServer JDBC > Driver][SQLServer]Transaction (Process ID 136) was deadlocked on lock | > communication buffer resources with another process and has been chosen as > the deadlock victim. Rerun the transaction. > > Now, I do understand( from google search) that this could be because of bad > indexing or long processing update insert select at same time etc. Can > someone share their experience if any with this kind of issue. It would be > nice to know how to tackle this issue because we are seeing this frequently > in several applications. > > thanks, > > Ajas Mohammed. > >
