SQL Server deadlock and .net

Discussion in 'ASP .Net' started by Hugo Flores, Nov 23, 2005.

  1. Hugo Flores

    Hugo Flores Guest

    Hi,

    I'm getting a deadlock on my database.
    Let me first tell you that this is a test database on a Win XP
    Professional.

    The SP where I'm getting the deadlock is this:

    PROCEDURE UpdateTestFields
    @id_Test int,
    @name varchar(255),
    @value varchar(5000),
    @lastModifiedBy varchar(50)
    AS

    UPDATE TestFields
    SET value = @value,
    lastModifiedBy = @lastModifiedBy,
    lastModified = GETDATE()
    WHERE id_Test = @id_Test
    AND name = @name

    Simple, but I'm doing the transaction part in .net

    Here's the code:

    Public Sub UpdateTestAndTestFields(ByVal intTestId As Int32, ByVal
    oParent As Control, ByVal intApplicationNumber As Int32, _
    ByVal intCustomerId As Int32, ByVal strLastModifiedBy
    As String, ByVal strRemarks As String, _
    ByVal enStatus As TestStatus, ByVal blnBlockUser As
    Boolean, ByVal enBlockType As BlockType, _
    ByVal strUnitNumber As String, ByVal strStationNumber
    As String, ByVal strDistrictNumber As String, ByVal strDXName As
    String)

    Dim conn As New
    SqlConnection(ConfigurationSettings.AppSettings("Connectionstring"))
    Dim cmd As New SqlCommand

    Dim oTrans As SqlTransaction

    conn.Open()
    cmd.Connection = conn
    oTrans = conn.BeginTransaction
    cmd.Transaction = oTrans
    cmd.CommandType = CommandType.StoredProcedure

    Try
    For Each oControl As Control In oParent.Controls
    cmd.Parameters.Clear()
    Select Case oControl.GetType.Name
    Case "TextBox"
    Dim txtTemp As New TextBox

    txtTemp = oControl
    UpdateTestFieldsTrans(conn, cmd, intTestId,
    txtTemp.ID, txtTemp.Text, strLastModifiedBy)
    Case "RadioButtonList"
    Dim rdoTemp As New RadioButtonList

    rdoTemp = oControl
    UpdateTestFieldsTrans(conn, cmd, intTestId,
    rdoTemp.ID, rdoTemp.SelectedItem.Value, strLastModifiedBy)
    End If
    Case "CheckBox"
    Dim chkTemp As New CheckBox

    chkTemp = oControl
    UpdateTestFieldsTrans(conn, cmd, intTestId,
    chkTemp.ID, chkTemp.Checked, strLastModifiedBy)
    End Select
    Next
    cmd.Parameters.Clear()
    UpdateTestsTrans(conn, cmd, intCustomerId, intTestId,
    enStatus, strRemarks, strLastModifiedBy, blnBlockUser, enBlockType,
    strUnitNumber, strStationNumber, strDistrictNumber, strDXName)
    oTrans.Commit()
    Catch ex As Exception
    oTrans.Rollback()
    Finally
    conn.Close()
    End Try

    End Sub

    As you can see I have an ASPX page with either Textbox, RadioButtonList
    or CheckBox controls, those contrls' IDs are stored on my TestField
    table under the name field, and that's why I'm looping through my
    page's fields to update my table with their given value.
    The UpdateTestFieldsTrans Sub is only a call to the SP specified at the
    beginning, I'm only passing the connection and the command objects to
    persist the transaction, and UpdateTestsTrans Sub is a call to a bigger
    SP but since the deadlock is not happening there I don't see the use
    of making this post even bigger.

    Am I getting the deadlock because is a SQL Server on a WInXP Pro?
    Is my approach of handling the field values update in .net wrong?

    Any help is appreciated
     
    Hugo Flores, Nov 23, 2005
    #1
    1. Advertising

  2. Hugo Flores

    Bruce Barker Guest

    if you get a deadlock with a single user (its normal with multiple users and
    your code should handle it), then its a coding error. it happens when you
    update the database rows with two different connections, but they are not
    sharing the same transaction context. this is handled in ado.net with the
    transaction object. you probably have a command that is not using the
    transaction object your created.

    -- bruce (sqlwork.com)



    "Hugo Flores" <> wrote in message
    news:...
    > Hi,
    >
    > I'm getting a deadlock on my database.
    > Let me first tell you that this is a test database on a Win XP
    > Professional.
    >
    > The SP where I'm getting the deadlock is this:
    >
    > PROCEDURE UpdateTestFields
    > @id_Test int,
    > @name varchar(255),
    > @value varchar(5000),
    > @lastModifiedBy varchar(50)
    > AS
    >
    > UPDATE TestFields
    > SET value = @value,
    > lastModifiedBy = @lastModifiedBy,
    > lastModified = GETDATE()
    > WHERE id_Test = @id_Test
    > AND name = @name
    >
    > Simple, but I'm doing the transaction part in .net
    >
    > Here's the code:
    >
    > Public Sub UpdateTestAndTestFields(ByVal intTestId As Int32, ByVal
    > oParent As Control, ByVal intApplicationNumber As Int32, _
    > ByVal intCustomerId As Int32, ByVal strLastModifiedBy
    > As String, ByVal strRemarks As String, _
    > ByVal enStatus As TestStatus, ByVal blnBlockUser As
    > Boolean, ByVal enBlockType As BlockType, _
    > ByVal strUnitNumber As String, ByVal strStationNumber
    > As String, ByVal strDistrictNumber As String, ByVal strDXName As
    > String)
    >
    > Dim conn As New
    > SqlConnection(ConfigurationSettings.AppSettings("Connectionstring"))
    > Dim cmd As New SqlCommand
    >
    > Dim oTrans As SqlTransaction
    >
    > conn.Open()
    > cmd.Connection = conn
    > oTrans = conn.BeginTransaction
    > cmd.Transaction = oTrans
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Try
    > For Each oControl As Control In oParent.Controls
    > cmd.Parameters.Clear()
    > Select Case oControl.GetType.Name
    > Case "TextBox"
    > Dim txtTemp As New TextBox
    >
    > txtTemp = oControl
    > UpdateTestFieldsTrans(conn, cmd, intTestId,
    > txtTemp.ID, txtTemp.Text, strLastModifiedBy)
    > Case "RadioButtonList"
    > Dim rdoTemp As New RadioButtonList
    >
    > rdoTemp = oControl
    > UpdateTestFieldsTrans(conn, cmd, intTestId,
    > rdoTemp.ID, rdoTemp.SelectedItem.Value, strLastModifiedBy)
    > End If
    > Case "CheckBox"
    > Dim chkTemp As New CheckBox
    >
    > chkTemp = oControl
    > UpdateTestFieldsTrans(conn, cmd, intTestId,
    > chkTemp.ID, chkTemp.Checked, strLastModifiedBy)
    > End Select
    > Next
    > cmd.Parameters.Clear()
    > UpdateTestsTrans(conn, cmd, intCustomerId, intTestId,
    > enStatus, strRemarks, strLastModifiedBy, blnBlockUser, enBlockType,
    > strUnitNumber, strStationNumber, strDistrictNumber, strDXName)
    > oTrans.Commit()
    > Catch ex As Exception
    > oTrans.Rollback()
    > Finally
    > conn.Close()
    > End Try
    >
    > End Sub
    >
    > As you can see I have an ASPX page with either Textbox, RadioButtonList
    > or CheckBox controls, those contrls' IDs are stored on my TestField
    > table under the name field, and that's why I'm looping through my
    > page's fields to update my table with their given value.
    > The UpdateTestFieldsTrans Sub is only a call to the SP specified at the
    > beginning, I'm only passing the connection and the command objects to
    > persist the transaction, and UpdateTestsTrans Sub is a call to a bigger
    > SP but since the deadlock is not happening there I don't see the use
    > of making this post even bigger.
    >
    > Am I getting the deadlock because is a SQL Server on a WInXP Pro?
    > Is my approach of handling the field values update in .net wrong?
    >
    > Any help is appreciated
    >
     
    Bruce Barker, Nov 23, 2005
    #2
    1. Advertising

  3. Hugo Flores

    Hugo Flores Guest

    Thanks for your answer Bruce.
    I'm getting it with multiple users.
    Now, you say is normal, but there should be a reason why I'm getting
    this.
    I know about the scenarios where a connection tries to update a
    resource taking by another connection. But you'll see in my case a
    TestField is based on a Test that a user is taking, therefore, two
    different users can't update anybody else's TestFields.
     
    Hugo Flores, Nov 23, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kevin Jackson

    ASP.NET deadlock

    Kevin Jackson, Feb 5, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,286
    Saravana [MVP]
    Feb 5, 2004
  2. =?Utf-8?B?c2NvdHRybQ==?=

    sql session state for .net v1.1/.net v2.0 and sql server 2005

    =?Utf-8?B?c2NvdHRybQ==?=, Feb 13, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    2,818
    Steven Cheng[MSFT]
    Feb 16, 2006
  3. Marc ENGEL
    Replies:
    0
    Views:
    456
    Marc ENGEL
    Jul 30, 2003
  4. Ramon
    Replies:
    1
    Views:
    490
    Lothar Kimmeringer
    Feb 25, 2009
  5. peter pilsl
    Replies:
    5
    Views:
    144
Loading...

Share This Page