Deadlock issue

  • sqlenthu 89358

    SSCrazy

    Points: 2726

    Hi All,
    I am going through a strange issue. We have a very simple file processing system with users submitting reports everytime. There is one part of process which deletes the data from intermediate table based on userid once processing is complete. In system usually the traffic is less with around 5-10 people submitting at a time. A file can have number of records between 1-2000. We have now started seeing deadlock at that delete process. Strangely coming at particular time during week but no process running. What can be the issue ? I know its open ended but its causing pain now.

  • Sue_H

    SSC Guru

    Points: 89885

    sqlenthu 89358 - Monday, February 11, 2019 4:16 PM

    Hi All,
    I am going through a strange issue. We have a very simple file processing system with users submitting reports everytime. There is one part of process which deletes the data from intermediate table based on userid once processing is complete. In system usually the traffic is less with around 5-10 people submitting at a time. A file can have number of records between 1-2000. We have now started seeing deadlock at that delete process. Strangely coming at particular time during week but no process running. What can be the issue ? I know its open ended but its causing pain now.

    Have you looked into tracing the deadlock? This article can walk you through some options:
    Tracing a SQL Server Deadlock

    Sue

  • sqlenthu 89358

    SSCrazy

    Points: 2726

    Thanks for the information. This was helpful and u have gone through this page already. Got the objects involved in deadlock. My concern is why it may come at same time of week. If there is no process running.

  • Sue_H

    SSC Guru

    Points: 89885

    sqlenthu 89358 - Monday, February 11, 2019 10:14 PM

    Thanks for the information. This was helpful and u have gone through this page already. Got the objects involved in deadlock. My concern is why it may come at same time of week. If there is no process running.

     Whether it's your process or not, something is running that results in the deadlocks.If it comes at the same time, try setting up a trace to see more of what is going on when this happens.

    Sue

  • sqlenthu 89358

    SSCrazy

    Points: 2726

    Sue_H - Tuesday, February 12, 2019 6:27 AM

    sqlenthu 89358 - Monday, February 11, 2019 10:14 PM

    Thanks for the information. This was helpful and u have gone through this page already. Got the objects involved in deadlock. My concern is why it may come at same time of week. If there is no process running.

     Whether it's your process or not, something is running that results in the deadlocks.If it comes at the same time, try setting up a trace to see more of what is going on when this happens.

    Sue

    my table has data like following:

    UserId DeptId ClassId StudentId StudentName TotalMarks ReceivedMarks CreateTime
    1 101 1A S101 Jacob 100 78 2018-01-01 10:30:25
    1 101 1A S102 Raymond 100 82 2018-01-01 10:30:25
    2 101 1B S085 Keisha 100 88 2018-01-02 09:20:30
    and so on...

    UserId contains clustered index. Users keep submitting the files regularly and at the end of data validation and processing data gets removed by following command:

    Delete table where UserId = @userid

    We are facing issues deadlock issues between the two delete statements all of sudden. The data load reminds same. At Max, there might be 10 users submitting file in parallel. The file submission might contain records from 1 to 2000 (don't go into data as the one provided is sample). The table is not huge as there is constant data clearance happening. There might be some stalk data because of some failed submissions and data not getting deleted because process never reaches that point but still table never had data more than few thousands. We are getting page locks. i am not able to understand why we have started getting deadlocks in a process which ran fine for almost a year with no increase in users or data volume and underline data more or less remaining same. Deadlock graph always capture these delete statement only. Also why one delete statement should look for resources of another when the deletes happening clearly on userid and are not overlapping.

  • mig28mx

    SSC Eights!

    Points: 933

    Hi,
    Dealing with deadlocks is allways different on each case.
    I recommend you to strat to analyze the execution plan of the querys involved.
    Usually, a change on the select statement, tunning or adding some indexes will solve the most of the cases.
    For example, if you detect an ugly sentence like "select * from TABLE" is the first thing you have to change. Or if your index fragmentation is high, or the table statistics are out of date, you need to update them.
    At last, but not the least, if you see on the execution plan, a index scan instead of index seek, you can identify if adding some index will help on the query.

    Hope this helps.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply