SQL Server Performance

Transaction (Process ID 151) was deadlocked on lock resources with another process and has been chos

Discussion in 'ALL SQL SERVER QUESTIONS' started by yatin baraiya, Apr 19, 2012.

  1. yatin baraiya New Member

    hi sqlExpertMember

    Description of the Issue:

    Purpose of the xyz.sql stored procedure is,
    We have one folder and having many child folders having many child documents , around folder tree structure having more than 15000 objects.
    now from this sp xyz.sql , we update "pqrs" column data in "test" table , this sp will update all the data like rootparent-->childOfrootparent---> childs of childOfrootparent-->as on......

    While this data is updating , at the same time "DataTest,sql" sp will call and it will execute the select data query on the same table., this will cause the below deadlock situation, Here i can not prevent the DataTest,sql sp execution in this scenario for the solution approach.

    When the execution of "DataTest,sql" stored procedure from the java. Following deadlock situation occurs.
    i will get the following deadlock situation . I am new in this type of error.Right now i am finding definite solution for this.

    com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 151) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
    at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)

    i want the general root cause for why this deadlock situation occurs?How to fix this issue and how to prevent this?
    is there any specific sql transaction parameter for the prevent above deadlock situation?

    if any suggestion for the handle above situation at java side will be appreciate.

    if i get any specific solution for it, i will definitely share it in this thread.

    we use the SQL SERVER 2005 Database server.
    if anybody have face this issue and have solve the above issue, pls share it at here?

    Thanks in advance
    Yatin Baraiya
    yatin baraiya, Apr 19, 2012
    #1

  2. Luis Martin Moderator

    Did you have all maintenance plan?.
    I mean, one of the reason is lack of performance, poor indexes, old statistics, etc.
    Luis Martin, May 1, 2012
    #2

  3. Daxesh New Member

    If you have,
    - hierarchical data model,
    - updating many rows at a time in a table in single transaction and those rows are scattered across many pages (e.g. Clustered index on GUID column)
    - and you have more than few such transactions happening at a time

    If above is true then there is a possibility that deadlock can occur due to lock on the resources (pages/extents) of the same table

    Using a higher level of isolation can resolve the issue
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    This can increase blocking, but you can avoid deadlock situation

    If this is not the case, then yes you have to investigate and resolve performance issues first. Usually lack of performance is the main reason for dead lock

    Daxesh
    Daxesh, May 1, 2012
    #3

  4. Shehap Moderator

    For such these deadlocks , you have a variety of workable solutions that I am going to enumerate them along with your 2 SPs:

    · DataTest.sql

    1. It is OLAP transaction and you might use with (nolock) hint if business accept since dirty data (Uncommitted data ) would be selected, but indeed this one would cut off relevant deadlocks

    2. If inapplicable , you may alter your DB to use READ_COMMITTED_SNAPSHOTisolation level using ALTERDATABASE DBName SETREAD_COMMITTED_SNAPSHOTON, but please look after TempDB

    Overload might exists there

    3. You might also use setdeadlock_priority low/High/Medium to segregate between priorities of this OLAP transaction and that OTLP transaction below

    4. If none of above is applicable , you have to keep more insight on index optimization for this OLAP transaction , you could check my blog for this regard

    5. Conducting all possible T-SQL performance enhancements in order to reduce its execution time , you could check my blog series for this regard starting from

    6. The symptom here is that the optimal performance reads while stress workload could minimize chances of deadlocks occurrence

    · xyz.sql

    You have to work the same approaches above plus the below one also :

    Look after clustered index update cost inside your query execution plan and try to reduce it as possible by seeking big non clustered indexes in order to :

    · Remove them
    · Or reduce their size by apply some data compression techniques of 2008 (Mostly probable page compression)

    Let me know if any furhter help needed
    Shehap, May 4, 2012
    #4

Share This Page