Deadlock problem (.net code also provided)

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> Am I getting the deadlock because is a SQL Server on a WInXP Pro?
No - the problem is not related to your OS.
> Is my approach of handling the field values update in .net wrong?
Yes. The likely cause of your deadlocks is that 2 different connections
attempt to update the same row but in a different sequence. Consider the
following scenario:
Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: UPDATE id_Test 1
Connection 2: UPDATE id_Test 2
Connection 1: UPDATE id_Test 2 (waits for Connection 2 to COMMIT)
Connection 2: UPDATE id_Test 1 (waits for Connection 1 to COMMIT)
Since each connection is waiting on the other, neither can continue. SQL
Server detects this deadlock and aborts one of the transactions.
One method to address to problem is to perform updates in the same order:
Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: UPDATE id_Test 1
Connection 2: UPDATE id_Test 1 (waits for Connection 1 to COMMIT)
Connection 1: UPDATE id_Test 2
Connection 1: COMMIT
Connection 2: UPDATE id_Test 2
Connection 2: COMMIT
Other techniques:
- specify a table-level lock hint so that table access is serialized.
- redesign your application and/or schema to avoid this contention.
- implement deadlock retry logic in your application
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hugo Flores" <hugo.flores@.ge.com> wrote in message
news:1132767962.860383.88020@.g44g2000cwa.googlegroups.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("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
>|||Thanks for your answer Dan.
I see your points, but let me tell you that in your scenario that you
gave, Connection 1 would never try to update id_Test 2. Because a
TestField is based on a Test that a user is taking, therefore, two
different users can't update anybody else's TestFields. What do you
think about this, may be I'm still wrong?|||Please post your DDL (CREATE TABLE) for your TestFields table, including
constraints and indexes. Without this information, I can only speculate.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hugo Flores" <hugo.flores@.ge.com> wrote in message
news:1132777760.360696.272530@.o13g2000cwo.googlegroups.com...
> Thanks for your answer Dan.
> I see your points, but let me tell you that in your scenario that you
> gave, Connection 1 would never try to update id_Test 2. Because a
> TestField is based on a Test that a user is taking, therefore, two
> different users can't update anybody else's TestFields. What do you
> think about this, may be I'm still wrong?
>|||Here it is
CREATE TABLE [dbo].[TestFields] (
[id_TestField] [int] IDENTITY (1, 1) NOT NULL ,
[id_Test] [int] NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[lastModified] [datetime] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[TestFields] WITH NOCHECK ADD
CONSTRAINT [TestFields_PK] PRIMARY KEY CLUSTERED
(
[id_TestField]
) ON [PRIMARY]
ALTER TABLE [dbo].[TestFields] ADD
CONSTRAINT [Tests_TestFields_FK1] FOREIGN KEY
(
[id_Test]
) REFERENCES [dbo].[Tests] (
[id_Test]
)
Thanks|||On 25 Nov 2005 04:40:59 -0800, Hugo Flores wrote:
>Here it is
(snip)
Hi Hugo,
Your table has only one index on the id_TestField column. The update in
the stored procedure finds the row to be updated on two other columns:
>UPDATE TestFields
> SET value = @.value,
> lastModifiedBy = @.lastModifiedBy,
> lastModified = GETDATE()
>WHERE id_Test = @.id_Test
>AND name = @.name
This means that SQL Server has to scan the complete table to find the
(hopefully single) row to be updated. For this scan, SQL Server has to
get at least a shared lock on all rows. This means that you have way too
much potential for blocking and deadlocks.
Your deadlocks will probably go away if you add an index on (id_Test,
name). The update process will probably speed up as well (unless your
table has only a small amount of rows).
However, there are a few more fundamental problems with your design.
First, there's no real key. An IDENTITY column can never be the only key
of a table. A PRIMARY KEY or UNIQUE constraint is supposed to throw an
error if the same INSERT is accidentally repeated; your IDENTITY column
will happily increase and add the same row again if someone clicks the
"add as new" button twice.
Based on the UPDATE above, I'm willing to guess that (name, id_Test) is
the real key of this table. Feel free to add an extra IDENTITY columns
as a surrogate key if you have to refer to this table from other tables,
but never expose it to the end user, and never forget to declare either
a PRIMARY KEY or a UNIQUE constraint for the real key. (And you'll get
an index on those column thrown in for free).
Second, judging by the names and datatypes, it looks like you are
creating a single table to hold all different attributes - a design
pattern commonly called the EAV design (Entity Attribute Value). This
looks very flexible and easy when you start. But it'll bite you when you
have to write custom queries. And it's scalability is limited.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In an update, which happens before, the update of the data or the update of
the index (non-clustered)?
Is it possible to deadlock on this?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:t13fo1huq1p52ag314p9gbf02dahjdeeso@.4ax.com...
> On 25 Nov 2005 04:40:59 -0800, Hugo Flores wrote:
>>Here it is
> (snip)
> Hi Hugo,
> Your table has only one index on the id_TestField column. The update in
> the stored procedure finds the row to be updated on two other columns:
>>UPDATE TestFields
>> SET value = @.value,
>> lastModifiedBy = @.lastModifiedBy,
>> lastModified = GETDATE()
>>WHERE id_Test = @.id_Test
>>AND name = @.name
> This means that SQL Server has to scan the complete table to find the
> (hopefully single) row to be updated. For this scan, SQL Server has to
> get at least a shared lock on all rows. This means that you have way too
> much potential for blocking and deadlocks.
> Your deadlocks will probably go away if you add an index on (id_Test,
> name). The update process will probably speed up as well (unless your
> table has only a small amount of rows).
>
> However, there are a few more fundamental problems with your design.
> First, there's no real key. An IDENTITY column can never be the only key
> of a table. A PRIMARY KEY or UNIQUE constraint is supposed to throw an
> error if the same INSERT is accidentally repeated; your IDENTITY column
> will happily increase and add the same row again if someone clicks the
> "add as new" button twice.
> Based on the UPDATE above, I'm willing to guess that (name, id_Test) is
> the real key of this table. Feel free to add an extra IDENTITY columns
> as a surrogate key if you have to refer to this table from other tables,
> but never expose it to the end user, and never forget to declare either
> a PRIMARY KEY or a UNIQUE constraint for the real key. (And you'll get
> an index on those column thrown in for free).
> Second, judging by the names and datatypes, it looks like you are
> creating a single table to hold all different attributes - a design
> pattern commonly called the EAV design (Entity Attribute Value). This
> looks very flexible and easy when you start. But it'll bite you when you
> have to write custom queries. And it's scalability is limited.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 25 Nov 2005 16:52:04 -0700, Janos Horanszky wrote:
>In an update, which happens before, the update of the data or the update of
>the index (non-clustered)?
Hi Janos,
I must admit that I'm not privy on all the exact details of what happens
under the hood. But AFAIK, the first thing that happens is requesting
locks and waiting until they are granted. AFter that, the exact sequence
is not really relevant anymore.
>Is it possible to deadlock on this?
I'd be surprised if the MS engineers had overlooked this possiblity. I
expect that the internal engine will use a fixed order of acquiring
locks if both data and index pages need to be locked, to minimize the
chance of deadlocks.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the answer Hugo.
I think this is the most thorough explanation someone has ever given
me, based on my lack of experience in database design.