ok, I will check if index is being utilized or not. The deadlock issue happens only in production. This started from last 8 months I would say. We have had several applications for last 4 years and only recently we have started to notice these deadlock errors.
Thanks, <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 Tue, Jan 26, 2010 at 11:06 PM, Greg McTure <gmct...@gmail.com> wrote: > You can copy one of the update statements that you are using inside the > sproc (stored procedure) into the query analyzer and see the execution plan > for the update query. If the columns of the WHERE clause is in an index see > if than index is being utilized in the execution plan. > > Also, do you get the deadlock issue in all of your environments (i.e. dev, > test, and prod) or just in prod? > > Greg McTure > ------------------------------ > *From: * Ajas Mohammed <ajash...@gmail.com> > *Date: *Tue, 26 Jan 2010 22:17:03 -0500 > *To: *<discussion@acfug.org> > *Subject: *Re: [ACFUG Discuss] Issue --- 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. > > Hi Greg, > > I will try to answer to the best of my knowledge. > > There are 407359 records in the table. There is only 1 record being > updated. > > Not sure if its full table is being locked or not. > > Do you have the ability to analyze the actual statement to get an execution > plan? > Well, let me see if I understand this first. Do you mean to say, I can copy > the stored proc code in sql query analyzer and run the stored procedure code > and see the execution plan perhaps? > > <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 Tue, Jan 26, 2010 at 9:45 PM, Greg McTure <gmct...@gmail.com> wrote: > >> 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. >>>>> >>>>> >>>> >>> >> >
