Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
| Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 28th, 2005, 04:37 PM
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.
  #2 (permalink)  
Old July 28th, 2005, 04:58 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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:
Code:
Dim cmdResults As SqlClient.SqlCommand
cmdResults = New SqlClient.SqlCommand("SELECT COUNT(*) from SurveyResults where QuestionID=" & v_strQuestionID & " AND UserId='" & strUserID & "' AND SurveyTimeStamp='" & strTimeStamp & "'", m_conResults)

Dim rdrResults As SqlClient.SqlDataReader

Try
  rdrResults = cmdResults.ExecuteReader
  If rdrResults.Read Then
    CanFindSurveyResults  = (mySqlDataReader.GetInt32(0) > 0)
    ...


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?
  #3 (permalink)  
Old August 9th, 2005, 11:22 AM
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

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.
  #4 (permalink)  
Old August 9th, 2005, 04:33 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Com issue(really urgent) balesh.mind ASP.NET 2.0 Professional 2 May 14th, 2008 03:35 AM
jk connector URGENT ISSUE supreet Apache Tomcat 1 March 31st, 2007 11:56 AM
Urgent Issue to be sorted out mike_remember ASP.NET 1.0 and 1.1 Professional 5 November 1st, 2006 10:52 AM
V V V Urgent::Crystal report format issue narayanark2003 Crystal Reports 0 July 6th, 2006 07:58 AM
Continuing DEADLOCK Problem Ron Howerton SQL Server ASP 1 September 21st, 2005 02:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.