SQL Server deadlock and .net

[Hugo Flores]

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("C onnectionstring"))
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]

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" <hu*********@ge.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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("C onnectionstring"))
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]

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.