Hi Ajas: It may be helpful to see a section of your stored procedure code that is doing the updates. Also, approximately how many records are in each of the tables being updated and approximately how many records are being updated? The SQL engine or optimizer may behave differently depending on the database brand (i.e. Oracle, MSSQL, Sybase, MySQL, etc); however, generally speaking if the predicate of your WHERE clause covers most of the records in your table then the optimizer will complete a full table scan even with the presence of the indices.
I would also consider looking to see if the full table is being locked on the updates. This should not be happening but it is just another angle to check out. I would not think this would not be an issue since I know in Oracle even if you are updating an entire table only the current row will be locked during the update. I don't see anything off-hand with your update statements. One can expect the best performance by updating the table with a single statement instead of multiple statements unless of'course you have so many records that your rollback segment becomes too small to support the number of records to be updated. Do you have the ability to analyze the actual statement to get an execution plan? On Tue, Jan 26, 2010 at 11:30 AM, Ajas Mohammed <ajash...@gmail.com> wrote: > 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. >>> >>> >> >
