Hi Greg, I found the stored proc which results in deadlock situation. It has lets say about 10 updates like this
update tbl set col3 = someval where col1 = @col1 and col2 = @col2 update tbl set col4= someval where col1 = @col1 and col2 = @col2 update tbl set col5 = someval where col1 = @col1 and col2 = @col2 and so on for other cols 6-10. col1 and col2 are part of clustered index. I can share the code off the list. Thanks for your input. Makes lot of sense to get started with troubleshooting. <Ajas Mohammed /> http://ajashadi.blogspot.com We cannot become what we need to be, remaining what we are. No matter what, find a way. Because thats what winners do. You can't improve what you don't measure. Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives. On Fri, Oct 30, 2009 at 5:14 PM, Greg McTure <gmct...@gmail.com> wrote: > 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. >> >> >
