We are getting a large number of deadlocks on updates on a system that's been running for several weeks now with no apparent peroblems. As time passes, the number of deadlocks seems to be increasing.

Here's the code that's gettign the deadlock:

OpenConnection()

        Dim sqlUpdate As SqlClient.SqlCommand

        m_strSQL = "Update SurveyResults set Answer='" & v_strAnswer & "', LastUpdated='" & v_strCurrentTimeStamp & "', " & _
        "LastUpdateUserID='" & v_strCurrentUserID & _
        "' where QuestionID=" & v_intQuestionID & " AND UserID='" & strUserID & "' AND SurveyTimeStamp='" & strTimeStamp & "'"

        sqlUpdate = New SqlClient.SqlCommand(m_strSQL, m_conResults)

        Try
            sqlUpdate.ExecuteNonQuery()

            UpdateResult = "Updating"
        Catch
            m_strErrorMessage = Err.Description

            UpdateResult = ""
        End Try

        sqlUpdate.Dispose()

        sqlUpdate = Nothing

        CloseConnection()

This code is embedded in a loop that submits several updates in a row. Although there shouldn't be a situation where more than one person is performing an update to the same record at the same time, I am most easily able to recreate the condition by submitting parallel updates.

The only other code referencing these records uses a reader:

        OpenConnection()

        Dim cmdResults As SqlClient.SqlCommand
        cmdResults = New SqlClient.SqlCommand("SELECT * from SurveyResults where QuestionID=" & v_strQuestionID & " AND UserId='" & strUserID & "' AND SurveyTimeStamp='" & strTimeStamp & "'", m_conResults)

        Dim rdrResults As SqlClient.SqlDataReader

        Try
            rdrResults = cmdResults.ExecuteReader
            'debug.WriteLine(" clsSurveyResults.CanFindSurveyResults.rdrResults.O pen")

            If rdrResults.Read() = True Then
                CanFindSurveyResults = True
            Else
                CanFindSurveyResults = False
            End If
        Catch
            CanFindSurveyResults = False
        Finally
            rdrResults.Close()
            'debug.WriteLine(" clsSurveyResults.CanFindSurveyResults.rdrResults.C lose")

            rdrResults = Nothing

            cmdResults = Nothing

            CloseConnection()
        End Try

I've tried resubmitting the update, like good code should do, and it fails 10 times in a row over the course of a minute. I don't understand how the server could be locking a record for over a minute.

Does anybody have any clues on modifications to the code above to eliminate or even reduce the frequency of this problem? Is there something on the Server itself that could be causing this? Could SQL be locking an entire page rather than a single record, to account for the fact that no two users should be updating the same record at the same time? We're stumped.

I don't really have an answer to this, but to rule out the reader code (and increase performance) you could use a SELECT COUNT (combined with an output parameter if you're using stored procedure) instead of an entire SELECT.

Right now, you do a full SELECT, only to see if the Reader can read.

Try something like this instead:

Not sure if it makes a difference, but it at least speeds up the operation by not selecting all the records if you only need a simple count.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Moon Over Marin by Dead Kennedys (Track 13 from the album: Plastic Surgery Disasters + In God We Trust) What's This?

The intent was to determine whether I should perform an insert or update depending, of course, on whether the record already exists. But in the interest of ruling out one more possible cause, I'll make the recommended change. Thanks.

Yeah, I understand that. However, creating an entire DataReader with a full SELECT statement is just overkill. The output param with a COUNT gives you the same information and will run faster.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.