deAttach database: code

Showing posts with label code. Show all posts

Thursday, March 22, 2012

Deadlock question

We are getting deadlocks when running this code from a stored procedure many
times simultaneously with 30 concurrent requests. From our understanding,
repeatable read in this case should lock the single row returned from the
SELECT TOP 1 statement for the length of this transaction and not allow othe
r
requesters to read it or update it. Can you tell us why this is deadlocking
and advise us of a better way to do this update?
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
where Pins.PinID = (
SELECT TOP 1 PinID FROM PINS
WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
and HeldDate is NULL
ORDER BY CreationDate, PinID
)
COMMIT TRANSACTIONHas PinID got a clustered index on it?
Is CreationDate indexed?
Your select might need to lock more rows than necessary due to the way it
accesses the data. Verify that your select is as optimal as possible WRT
index utilization. It may lock the single row, or the page the row is on,
or even extents of pages.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
> many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
> other
> requesters to read it or update it. Can you tell us why this is
> deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION|||REPEATABLE READ places a shared lock on the resource, not an exclusive lock.
That's probably why you're getting deadlocks.
Change your logic:
DECLARE @.PinID int
BEGIN TRANSACTION
SELECT @.PinID = TOP 1 PinID FROM Pins WITH(UPDLOCK) WHERE...
UPDATE Pins ... WHERE PinID = @.PinID
COMMIT TRANSACTION
You don't need to set the transaction isolation level in this case.
"Larry Herbinaux" <Larry Herbinaux@.discussions.microsoft.com> wrote in
message news:044C513A-A65C-4F56-AA88-C91266B00A25@.microsoft.com...
> We are getting deadlocks when running this code from a stored procedure
many
> times simultaneously with 30 concurrent requests. From our understanding,
> repeatable read in this case should lock the single row returned from the
> SELECT TOP 1 statement for the length of this transaction and not allow
other
> requesters to read it or update it. Can you tell us why this is
deadlocking
> and advise us of a better way to do this update?
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
> BEGIN TRANSACTION
> UPDATE Pins SET PinStatus = 'RESE', HeldDate = getdate()
> where Pins.PinID = (
> SELECT TOP 1 PinID FROM PINS
> WHERE CardTypeID = @.CardTypeID AND PinStatus = 'AVAI' AND OrderID is NULL
> and HeldDate is NULL
> ORDER BY CreationDate, PinID
> )
> COMMIT TRANSACTION

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:
>
> (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:
>
> 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.

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("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
> 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.googlegro ups.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
>
|||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.googlegr oups.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:
> (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:
>
> 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.

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.

Wednesday, March 21, 2012

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.

Sunday, March 11, 2012

deadlock and error code returned

The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ? be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ? --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNum
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:

> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ? be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ? --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>
|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-536X45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
> - Show quoted text -
thankyou

deadlock and error code returned

The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
> > The question is: if the below sproc execution transaction becomes a
> > deadlock victim, would it give a error? If yes, what kind of error
> > would it give.
> > In the below example, what should ' be?
> > /*
> > Sample Call:
> > declare @.ret int
> > @.ret =3D exec usp_testSproc
> > if @.ret =3D ' --
> > Print "The sproc is a deadlock victim"
> > */
> > create proc usp_testSproc
> > as
> > declare @.errNum int
> > set @.errNum =3D 0
> > select name, dept
> > from department
> > where name =3D"xyz"
> > set @.errNum =3D @.@.ERROR
> > if @.errNum <> 0
> > print cast(@.errNum as varchar(10) + ": Error occured"
> > return @.errNum- Hide quoted text -
> - Show quoted text -
thankyou

deadlock and error code returned

The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5364
5181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:

> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=
36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
>
>
>
> - Show quoted text -
thankyou

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

In SQL Server 2000 you could run the piece of code below, to enable the logging of a deadlock in the SQL Server error log. Which could then be used to fire an alert, and then kick of an Agent job to send an SMTP email alert.

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now I work for a managed SQL Server company, and a large number of our clients used our alerting for deadlocking to tune their applications, or at a minimum to show them when something was wrong with the database due to sudden rise in the number of deadlocks.

However, in SQL Server 2005, the functionality for the sp_alertmessage procedure has been changed so that you can't update any message id less than 50,000. Which comes inline with the secure engine that Microsoft have designed.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

You can still log information by enabling the necessary trace flags, however that logs very verbose information about the deadlocking chain, which in turn can quickly blow the size of the error logs out.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

This isn't the case, unless you enable some trace flags, which as mentioned will give you a whole lot of information, which although is valuable, isn't ideal if you're wanting day to day deadlock tracking.

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

We experienced the same frustration for deadlocks, primary key violations, login failures and permission denied. These alerts are needed on several of our SQL 2000 servers, but can no longer be implemented on SQL 2005. We find this very frustrating and do not understand the logic behind the decision to remove this functionality.

Dave

|||

completely agree with you and I can't understand why this is so. I did try via the dedicated admin connection but still couldn't appear to get around this. I used to set alerts on various errors in sql 2000 for all types of errors as part of debugging production software releases and such. Not needed to do this for last couple of years but now want to do this with sql 2005 and I can't. What a pain!

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

Yes I know about and use the trace flags - but what I really wanted was an alert on the deadlock so I could grab a snapshot of all actiivty, and know when the deadlock occurred without trawling the error log.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

I have also listed this on the BETA site for 2008, as something that should be added back into the engine. But as yet I haven't had any feedback from Microsoft on whether it's going to make it on the to-do list or not.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

Dave

|||

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

Dave

|||

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

Dave

|||

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

Dave

|||

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Hi all,

Exec sp_altermessage 1205, 'WITH_LOG', 'true'

The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:

"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.”

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,

Reece.

Does anyone have any info or opinions on this?

Cheers,

Reece.

|||

Dave

|||

|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .

DBCC TRACEON

Trace flag 1204

This trace flag returns the type of locks participating in a deadlock and the current command affected.

Trace flag 1205

This trace flag returns more detailed information about the command being executed at the time of a deadlock.

|||

I'm glad I'm not the only one experiencing this frustrating change in functionality.

We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.

Cheers.

deadlock - retrying the transaction

Hi,
The bigger my C# web-application gets, the more places I need to put in the
tedious retrying block of code to make sure operations that can run into
database deadlocks are re-run (retried) 3-4 times and give up if after that
it's still in deadlock. I'm very sure that many experienced people out
there already deal with this issue somehow. Is there an alternative to it?
Thanks for your comments and suggestions.
Most deadlocks are due to poor indexing and inconsistent updating in the
order of the tables. You may want to concentrate more on the database
schema and code vs. the C# code.
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%230DDr25yFHA.1040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The bigger my C# web-application gets, the more places I need to put in
> the
> tedious retrying block of code to make sure operations that can run into
> database deadlocks are re-run (retried) 3-4 times and give up if after
> that
> it's still in deadlock. I'm very sure that many experienced people out
> there already deal with this issue somehow. Is there an alternative to it?
> Thanks for your comments and suggestions.
>
>
|||Thanks for the advice. I understand that indexing and update order of the
tables contribute to deadlocks. My question is is it possible to make a
large application deadlock free? If yes, please share the tips with me how
to ensure that; what type of guidelines/disciplines to follow to ensure
tables are always updated in correct order when there are storeprocedures,
triggers, and direct queries can hit the db at the same time.
If deadlock free is not guaranteed, then it sounds to me that we would need
to put retrying blocks into the code - no other way around.
Hope to hear back from you, thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Most deadlocks are due to poor indexing and inconsistent updating in the
> order of the tables. You may want to concentrate more on the database
> schema and code vs. the C# code.
> --
> Andrew J. Kelly SQL MVP
>
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%230DDr25yFHA.1040@.TK2MSFTNGP14.phx.gbl...
it?
>
|||I don't think there is ever a way to make an app deadlock free but you can
certainly do some things to prevent most occurrences of them. Maybe these
will help:
http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.co...ql_locking.asp
http://support.microsoft.com/kb/q169960/
http://www.codeproject.com/cs/database/sqldodont.asp
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eG6qrXCzFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Thanks for the advice. I understand that indexing and update order of the
> tables contribute to deadlocks. My question is is it possible to make a
> large application deadlock free? If yes, please share the tips with me
> how
> to ensure that; what type of guidelines/disciplines to follow to ensure
> tables are always updated in correct order when there are storeprocedures,
> triggers, and direct queries can hit the db at the same time.
> If deadlock free is not guaranteed, then it sounds to me that we would
> need
> to put retrying blocks into the code - no other way around.
> Hope to hear back from you, thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> it?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0osvSDzFHA.2652@.TK2MSFTNGP14.phx.gbl...
>I don't think there is ever a way to make an app deadlock free but you can
>certainly do some things to prevent most occurrences of them. ...
Just a note: there is help on the way for deadlocks. Running on SQL Server
2005 with Read Commited Snapshot Isolation should automatically eliminate
the vast majority of deadlocks.
David
|||Why do you say that? It basically stops readers from blocking writers and
visa versa. It does nothing to prevent writers from blocking writers in the
reverse order.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmRErFFzFHA.1256@.TK2MSFTNGP09.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0osvSDzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> Just a note: there is help on the way for deadlocks. Running on SQL
> Server 2005 with Read Commited Snapshot Isolation should automatically
> eliminate the vast majority of deadlocks.
> David
>
>
|||If you have more than one connection to the database, then deadlocks can
happen. Deadlocks can occur on resources other than just locks. Threads
and memory are some examples. Sometimes parallelism can cause deadlocks.
These other kinds of deadlocks are rare, and usually occur on servers with
very heavy loads, but they can occur. Therefore, unless you take no pride
in your work, you should take into account that they can occur in your code.
Retry blocks are one possible solution, and can also be used to recover from
optimistic concurrency collisions. Another would be to return an error
message to the user, and let them resubmit the transaction.
Most deadlocks occur because resources aren't updated in the same order.
This problem is amplified by frequent scans due to a lack of indexes. In my
experience, the best way to mitigate the effect of deadlocks is to execute
transactions within stored procedures. If the transaction is enclosed in a
stored procedure, then most often it's a simple matter to fix the problem.
Usually rearranging statements or adding a simple select statement can be
used to alter the order in which locks are obtained. That's a lot more
difficult to do in client code that needs to be redeployed to tens or
hundreds of workstations. There are several other reasons to issue updates
within stored procedures. It's easier to issue set-based operations within
a procedure--just send the changes in temp tables or (frown) block the
changes in a large varchar parameter that can be parsed within the procedure
and then issue set-based statements to commit the changes. Using stored
procedures makes it easier to wait until the last possible instant to start
the transaction. Set-based operations are much faster and more scalable
than row-based operations because triggers fire only once, updates to
indexes can be optimized, and transaction logging is minimized--all of which
can serve to minimize the duration of transactions which will consequently
reduce the probability of deadlocks. If you must use a cursor, then it's
best to cache the results in temp tables or table variables and flush them
using set-based operations. Another technique is to use optimistic
concurrency with rowversioning which is again simpler to accoplish in a
stored procedure. All of the work required to calculate the results to be
committed is done under a READ COMMITTED isolation level with the max
rowversion of each row source cached in local variable and the results
cached in table variables so that all that is left to do after the
transaction is started is to lock the source rows with REPEATABLE READ
verifying at the same time that the max rowversion hasn't changed, to apply
update locks on all rows to be modified or deleted, and finally to issue the
statements that commit the changes.
To summarize: (1) Enclose transaction processing within stored procedures.
(2) Make sure that you obtain locks in the same order in every procedure,
trigger, function, or process. (3) Wait until the last possible instant to
start a transaction. And (4) keep transaction duration as short as possible
by using set-based operations, by caching and flushing, and/or by using
optimistic concurrency.
One other thing: redundancy in a database can increase the probability of
deadlocks. Make sure your database schema conforms at a minimum to
Boyce-Codd normal form. And (This will probably cause Celko to get out his
flame thrower!) use surrogate keys instead of natural keys in DRI
relationships to eliminate the redundancy inherent in natural foreign keys.
A database that is in 5th normal form and that uses surrogate keys correctly
has the additional property that each extrinsic atomic value exist in
exactly one place in the database. Redundancy can thus be completely
eliminated from the database.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eG6qrXCzFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Thanks for the advice. I understand that indexing and update order of the
> tables contribute to deadlocks. My question is is it possible to make a
> large application deadlock free? If yes, please share the tips with me
> how
> to ensure that; what type of guidelines/disciplines to follow to ensure
> tables are always updated in correct order when there are storeprocedures,
> triggers, and direct queries can hit the db at the same time.
> If deadlock free is not guaranteed, then it sounds to me that we would
> need
> to put retrying blocks into the code - no other way around.
> Hope to hear back from you, thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> it?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODkckFHzFHA.2796@.TK2MSFTNGP10.phx.gbl...
> Why do you say that? It basically stops readers from blocking writers and
> visa versa. It does nothing to prevent writers from blocking writers in
> the reverse order.
>
Here are my reasons:
1 My feeling is that a majority of deadlocks involve a shared lock will be
directly eliminated by not issuing shared locks. For the purposes of
deadlock elimination, READ CONCURRENT SNAPSHOT isolation is like putting
NOLOCK on every query, which is a common remedial measure for deadlocks.
2 Across the board improvements in concurrency reduce deadlocks
automatically. This is for the same reason that only busy databases
deadlock. Eliminating S locks will cause fewer lock waits for writing
transactions, which will cause them to hold their X locks for less time,
reducing the time in which they would be vulnerable to a deadlock.
3 Most of the remaining deadlock scenarios are pretty simple and can be
considered coding errors or table design errors.
4 READ CONCURRENT SNAPSHOT is very similar to Oracle's Multi-Version Read
Concurrency, and Deadlocks are extremely rare in Oracle.
David
|||I just hate the thought that people will flock to Snapshot Isolation level
because it is perceived to be easier and better than Read committed. Most
developers that I meet using Oracle do not realize what that (or this new
isolation level) means in regards to the data they are returning. Too many
make decisions based on the data returned without regard to the fact the
data may in fact be changing underneath them. I am not saying there wont be
proper times to use this as everything has it's place. But I already see
where too many people think this will solve all their problems
automatically. Anyway enough of this as this is one of those religious war
kind of topics<g>.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OwWPX0NzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ODkckFHzFHA.2796@.TK2MSFTNGP10.phx.gbl...
> Here are my reasons:
> 1 My feeling is that a majority of deadlocks involve a shared lock will be
> directly eliminated by not issuing shared locks. For the purposes of
> deadlock elimination, READ CONCURRENT SNAPSHOT isolation is like putting
> NOLOCK on every query, which is a common remedial measure for deadlocks.
> 2 Across the board improvements in concurrency reduce deadlocks
> automatically. This is for the same reason that only busy databases
> deadlock. Eliminating S locks will cause fewer lock waits for writing
> transactions, which will cause them to hold their X locks for less time,
> reducing the time in which they would be vulnerable to a deadlock.
> 3 Most of the remaining deadlock scenarios are pretty simple and can be
> considered coding errors or table design errors.
> 4 READ CONCURRENT SNAPSHOT is very similar to Oracle's Multi-Version Read
> Concurrency, and Deadlocks are extremely rare in Oracle.
>
> David
>
|||How to implement set-based operations? They are inherent in every SQL
database. Perhaps you misunderstand what I mean by set-based and row-based
operations. A set-based INSERT, UPDATE or DELETE statement may affect
multple rows, whereas with row-based processing, a separate INSERT, UPDATE
or DELETE statement is issued for each affected row.
There are a plethora of articles, books, and courses available on SQL.
Since cursors can be used and misused in many different ways (not to mention
that there are several different types of cursors), there isn't a simple
all-encompassing pro/con comparison that can be made. There are times
(albeit very rare) when a cursor will outperform a similar set-based
query--sometimes even by several orders of magnitude. Most of the time,
however, the reverse is true.
These facts are always true:
(1) triggers fire once per statement. If you send 100 INSERT...VALUES
statements, all INSERT triggers on the table will fire 100 times, whereas if
you issue the 100 INSERT...VALUES against a temporary table, and then issue
a single INSERT...SELECT, then the INSERT triggers will only fire once.
(2) updates to indexes can be optimized. If you send 100 INSERT...VALUES
statements, then index maintenance is performed 100 times, which may mean 99
extra logical writes per index. A single INSERT...SELECT will cause each
index to be updated only once, and if several changes are made on the same
index page, then instead of several individual updates to the same index
page, you get a single write to that index page.
(3) transaction logging is minimized. Every statement that executes has a
certain amount of transaction log overhead associated with it, so if you
send 100 INSERT...VALUES statements, then there is 100 times the overhead
than with a single INSERT...SELECT. In addition, since index updates are
optimized to minimize writes to each index, it follows that the number of
writes to the transaction log to record the old and new index entries is
similarly reduced.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:OiYpj5fzFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Would you be able to give me a pointer to where I can find more
> information
> about set-based operations such as how to implement them for sqlserver
> 2000
> and pro/con comparisons with the row based operations?
> Thank you very much for the detailed guidance.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:unvwe4HzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> code.
> from
> my
> a
> updates
> within
> procedure
> start
> which
> apply
> the
> to
> possible
> his
> keys.
> correctly
> the
> storeprocedures,
> the
> in
> after
> out
> to
>

Thursday, March 22, 2012

Deadlock question

Deadlock problem (.net code also provided)

Deadlock problem (.net code also provided)

Deadlock problem (.net code also provided)

Wednesday, March 21, 2012

Deadlock on SQL SELECT statement

Sunday, March 11, 2012

deadlock and error code returned

deadlock and error code returned

deadlock and error code returned

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Deadlock alert (message ID 1205) no longer able to be logged in 2005

Deadlock alert (message ID 1205) no longer able to be logged in 2005

deadlock - retrying the transaction

deAttach database

Blog Archive

About Me