Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SQL Server deadlock and .net

Reply
Thread Tools

SQL Server deadlock and .net

 
 
Hugo Flores
Guest
Posts: n/a
 
      11-23-2005
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

 
Reply With Quote
 
 
 
 
Bruce Barker
Guest
Posts: n/a
 
      11-23-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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
>



 
Reply With Quote
 
 
 
 
Hugo Flores
Guest
Posts: n/a
 
      11-23-2005
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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SOCKET PROBLEM: Server and client "deadlock" Ramon Java 1 02-25-2009 09:58 AM
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
transfering a string from client to server without deadlock? peter pilsl Perl Misc 5 05-17-2006 07:10 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
pythoncom: STA python COM server randomly does not receive event from other objects : deadlock Marc ENGEL Python 0 07-30-2003 09:19 AM



Advertisments