Occasional Deadlock.

Question

I'm having trouble with a web application that will deadlock occasionally

There are 3 queries involved. 2 are trying to update a table

UPDATE AttendanceRoll
SET ErrorFlag = 0
WHERE ContractID = @ContractID
AND DATEPART(month,AttendanceDate) = DATEPART(month,@Month_Beginning)
AND DATEPART(year,AttendanceDate) = DATEPART(year,@Month_Beginning)

and one is trying to insert into the table

INSERT INTO AttendanceRoll 
    (AttendanceDate, ContractID, PersonID, 
    StartTime, 
    EndTime, 
    Hours, AbsenceReason,
    UpdateCount, SplitShiftID, ModifiedBy, ModifiedDate)
SELECT   
    @P33,  @P34,  @P35,
    CONVERT(datetime,REPLACE( @P36, '.', ':')),
    CONVERT(datetime,REPLACE( @P37, '.', ':')),
    @P38,  @P39,  
    @P40, 1,  @P41, GETDATE()

The deadlock graph shows a kind of circular arangement of page locks and an exchange event and the 2 update queries have the same server process id.

If anyone has any ideas about how I should go about solving this issue it would be most appreciated.

I have the deadlock graph that I can post if anybody needs to see it.

Thanks Carl R

Answer 1

Is the Update query taking a significant time to execute (say, more than second)? If so, try optimizing the query (i.e. by putting indexes on the contractid column etc)

The first thing I always like to do when fixing deadlocks is tune the queries involved. If you can get a good performance improvement then you get the benefit of the faster query, as well as greatly reducing the chance of deadlocks occurring.

Answer 2

--After jorgeburgos' suggestions, then...

If its a deadlock, then, assuming there are only 2 contestants, one should be the victim and one will complete.

You could build in deadlock detection into the data layer, and have it retry operations that fail for deadlocks.

Use different table hint locking mechanisims to minimize the range of resources contested. I dont know which apply to the sql version you are using, but here is from the MSDN. http://msdn.microsoft.com/en-us/library/ms187373.aspx

Answer 3

Without looking at the deadlock graphs, and the workloads involved, and based on what you say about there being an exchange you are probally running into a deadlock involving parralleism. http://msdn.microsoft.com/en-us/library/aa937571(SQL.80).aspx Does your deadlock graph look like the one in the previous article?

Can you disable parralesim for the query? There is a hint OPTION (MAXDOP 1) which will disable it on a particular query. Turn this on and see if it helps. http://msdn.microsoft.com/en-us/library/ms181714.aspx

Optimizing the indexes might also relieve the deadlock by removing the need for parallesim in the first place.

Answer 4

i would guess that your updates are doing table scans, and end up escalating row locks into page locks into table locks, which prevents the insert from ever completing

i think there is a setting to tell it not to escalate beyond row locking, you might try that first

failing that, you might have to separate out and index the month data to make the updates more efficient

Answer 5

I usually wrap all my calls to SQL server inside something like (exactly this won't compile, but you'll get the idea):

for (;;) {
    try {
        using (var t = BeginTransaction()) {
            DoTheCall();
            t.Commit();
            return;
        }
    }
    catch (SqlException ex) {
        if (ex.Number != 1205 && ex.Number != 601 && ex.Number != 605)
            throw;
    }
}