View Single Post
 
Old July 28th, 2005, 04:37 PM
Ron Howerton Ron Howerton is offline
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default DEADLOCK issue - URGENT!

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.