DEADLOCKS

Hi:
I have a .net web application and I am getting a lot of deadlocks. My
system processes a lot of real-time transactions daily. In addition ,
there are like 3 DTS packages running. The problem is that I am getting a
lot of deadlock and based on the history of the pakages I would say that is
happening because the packages take the control and kill the processes that
are tying to run at the same time.
Is there a way to try to reduce this dead-lock assigning especific features
to the packages.
ThanksI don't think there are any issues specific to DTS packages that cause
deadlocking. A package could be excuting almost anything, so provide more
details about what type of selects / updates / inserts the package is
performing and also the nature of the transactions that are being blocked.
Read up in Books Online about "set transaction isolation level". Consider
using a lower isolation level, like read uncommitted. Basically, this allows
a process to read data currently locked by another transaction. Just
understand what "dirty reads", "nonrepeatable reads", etc. are and whether
or not they would present a significant problem in the specific case of your
queries.
Also, for what it's worth, here are my bookmarks for SQL Server deadlocking:
Deadlocking
http://msdn.microsoft.com/library/d... />
a_8i93.asp
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
Minimizing Deadlocks
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/defaul...kb;en-us;832524
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:%23OfWoX19FHA.4004@.TK2MSFTNGP14.phx.gbl...
> Hi:
> I have a .net web application and I am getting a lot of deadlocks. My
> system processes a lot of real-time transactions daily. In addition ,
> there are like 3 DTS packages running. The problem is that I am getting
> a lot of deadlock and based on the history of the pakages I would say that
> is happening because the packages take the control and kill the processes
> that are tying to run at the same time.
> Is there a way to try to reduce this dead-lock assigning especific
> features to the packages.
> Thanks
>

Deadlocks

Any ideas on resolving deadlocks on a sql-server 2000 windows 2000
platform?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
http://support.microsoft.com/?id=224453
118552 INFO: Handling Deadlock Conditions
http://support.microsoft.com/?id=118552
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
--
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

Deadlock using 'INSERT BULK'

Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris
<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegrou ps.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
for a discussion and some possible workarounds.
David
|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g2000cwe.goo glegroups.com...
>
>
>
>
>
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in the
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seehttp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -

Deadlock using 'INSERT BULK'

Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTi
me,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] Date
Time,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/a.../29/475546.aspx
for a discussion and some possible workarounds.
David|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g20
00cwe.googlegroups.com...
>
>
>
>
>
>
>
>
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in t
he
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seeht
tp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk
bericht weergeven -sql

Deadlock using 'INSERT BULK'

Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
for a discussion and some possible workarounds.
David|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g2000cwe.googlegroups.com...
>
>
> > Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> > 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> > applied). The SqlBulkCopy class is used within a
> > System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> > transactionmanager.
> > The problem is that a a deadlock occurs the command is issued, and I'm
> > having problems understanding why this deadlock happens. The -T1204
> > output is:
> > Deadlock encountered ... Printing deadlock information
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Wait-for graph
> > 2006-11-22 15:23:56.78 spid3
> > 2006-11-22 15:23:56.78 spid3 Node:1
> > 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> > CleanCnt:2 Mode: Schema Flags: 0x0
> > 2006-11-22 15:23:56.78 spid3 Grant List 0::
> > 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> > Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> > 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> > UNKNOWN TOKEN Line #: 1
> > 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> > bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> > [Field4] Decimal(28,13), [Field4] Bit)
> > 2006-11-22 15:23:56.78 spid3 Requested By:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> > 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> > Schema-Mod SPID:77 ECID:0 Ec:(0x3660BA30) Value:0x1ef77880 Cost:(0/0)
> > The "TAB: 8:565577053" refers to the TableName specified in the Input
> > Buf.
> > This problem is reproducable (it happens with multiple tables).
> > Does anyone have a hint on how to tackle this problem?Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in the
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seehttp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -

Deadlock that does not make sence.

I am getting a deadlock error message that is perplexing me.
Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
database and stored procedures are what we have in common. We are doing some testing now to see how well these different
applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
have received a deadlock error in the ADO.NET application.
We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
side is for the entire select.
I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
be a timeout instead of a deadlock?
The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
deadlock.
Any insight into this perplexing problem would be very welcomed.
MikeHard to say without having access to your db, but the scenario you describe
doesn't sound like deadlock proof.
Sometimes deadlock might occur because of lack of appropriate indexes, and
sometimes because of the way your applications/transactions are written.
Best way to figure this out is to use Profiler to trace statement starting,
deadlock and deadlock chain events. Once you identify the conflicting
processes, reopen the trace file and filter by process id's. Move your way
upwards from the deadlock event and write down a time-based chain of events
under columns representing the different processes. Examine the tables'
indexes and try to figure out the cause of the deadlock.
--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||First of all, do you have triggers on your tables? If you do, that's the
place you need to check.
Second, make sure both your applications use Optimistic concurrency control.
Especially the one that only reads should be optimistic.
Finally,
open your books online and check for the "ROWLOCK" documentation. It gives
you a list of query hints like "Read Past" which skips locked rows, or
HoldLock which will wait for the records.
"Mike Malter" <mikemalter@.nospam.com> wrote in message
news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET
with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing
some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO
edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO
side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked
by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the
case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a
deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike
>|||Just some general information here. Maybe it is of some help to you.
In SQL-Server, there are basically two situations that may lead to
deadlocks:
1. Locks are acquired in different order in different transactions. This
is the 'classic' deadlock. The chance is increased when the transactions
can not use an index to lock at row level. The chance is also increased
when there are many lock requests, or when memory is low.
2. Lock escalation (from row/page locks to table locks) can lead to
deadlocks. If lock escalation is caused by low memory, then lowering
your locking granularity from row locks to page locks may help.
Gert-Jan
Mike Malter wrote:
> I am getting a deadlock error message that is perplexing me.
> Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The
> database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records, we
> have received a deadlock error in the ADO.NET application.
> We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the ADO.NET
> side is for the entire select.
> I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I would
> think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't there
> be a timeout instead of a deadlock?
> The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> deadlock.
> Any insight into this perplexing problem would be very welcomed.
> Mike|||May be it will be more helpful if you can enable the Trace Flags (1204,
3605) and run the scenario you are describing post the Portions of the
deadlock images that (if any) get logged in the SQL Error logs.
DBCC TRACEON(1204, 3605)
--
HTH
Satish Balusa
Corillian Corp.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uKiIeAjdDHA.1632@.TK2MSFTNGP12.phx.gbl...
> Hard to say without having access to your db, but the scenario you
describe
> doesn't sound like deadlock proof.
> Sometimes deadlock might occur because of lack of appropriate indexes, and
> sometimes because of the way your applications/transactions are written.
> Best way to figure this out is to use Profiler to trace statement
starting,
> deadlock and deadlock chain events. Once you identify the conflicting
> processes, reopen the trace file and filter by process id's. Move your way
> upwards from the deadlock event and write down a time-based chain of
events
> under columns representing the different processes. Examine the tables'
> indexes and try to figure out the cause of the deadlock.
> --
> BG, SQL Server MVP
> Solid Quality Learning
> www.solidqualitylearning.com
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the
ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is
locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||Gert,
Thanks for your reply.
The deal in this case is that there is only one table called parent.
What I am doing is a select on this table for two columns while the other system is doing random updates.
I am wondering if you could elaborate a little more about too many lock requests.
Thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> Just some general information here. Maybe it is of some help to you.
> In SQL-Server, there are basically two situations that may lead to
> deadlocks:
> 1. Locks are acquired in different order in different transactions. This
> is the 'classic' deadlock. The chance is increased when the transactions
> can not use an index to lock at row level. The chance is also increased
> when there are many lock requests, or when memory is low.
> 2. Lock escalation (from row/page locks to table locks) can lead to
> deadlocks. If lock escalation is caused by low memory, then lowering
> your locking granularity from row locks to page locks may help.
> Gert-Jan
>
> Mike Malter wrote:
> >
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
The
> > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
would
> > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike|||Vassilis,
Thanks for your reply.
We do not have any triggers on the table in this test.
The one that reads is using ADO.NET. Do you know how to set concurrency control to Optimistic in ADO.NET?
I will look into query hints, thanks.
Mike
"Vassilis Devletoglou" <vdev@.acn.gr> wrote in message news:OjlLowjdDHA.1876@.TK2MSFTNGP12.phx.gbl...
> First of all, do you have triggers on your tables? If you do, that's the
> place you need to check.
> Second, make sure both your applications use Optimistic concurrency control.
> Especially the one that only reads should be optimistic.
> Finally,
> open your books online and check for the "ROWLOCK" documentation. It gives
> you a list of query hints like "Read Past" which skips locked rows, or
> HoldLock which will wait for the records.
>
>
> "Mike Malter" <mikemalter@.nospam.com> wrote in message
> news:Ot0HQ5idDHA.736@.TK2MSFTNGP09.phx.gbl...
> > I am getting a deadlock error message that is perplexing me.
> >
> > Our scenario is like this; separate applications using ADO and ADO.NET
> with EnterpriseServices are coexisting in this project. The
> > database and stored procedures are what we have in common. We are doing
> some testing now to see how well these different
> > applications can get along with each other, and in one of our tests, ADO
> edits random records and ADO.NET selects all records, we
> > have received a deadlock error in the ADO.NET application.
> >
> > We are both in a transaction when this occurs. The transaction on the ADO
> side is per record, and the transaction on the ADO.NET
> > side is for the entire select.
> >
> > I can understand that the select will want to read a record that is locked
> by the edit, however the edit is so quick that I would
> > think that the select would not be blocked very long. If that were the
> case, where the select was blocked too long, wouldn't there
> > be a timeout instead of a deadlock?
> >
> > The other factor here is that if we only edit 5 records we never get a
> deadlock. If we edit more than that, we always get a
> > deadlock.
> >
> > Any insight into this perplexing problem would be very welcomed.
> >
> > Mike
> >
> >
>|||The maximum number of outstanding locks is determined by some internal
formula. A major factor in this formula is the total amount of memory
that is available to SQL-Server. If the number of actual locks comes
close to this maximum number, lock escalation will occur/increase. In
that situation, SQL-Server is more likely to 'trade in' several row
locks or page locks for one table lock. If this happens for two
processes that have locks on the same table, this results in a deadlock.
BOL has a special section for "number of locks the system can allocate"
Gert-Jan
Mike Malter wrote:
> Gert,
> Thanks for your reply.
> The deal in this case is that there is only one table called parent.
> What I am doing is a select on this table for two columns while the other system is doing random updates.
> I am wondering if you could elaborate a little more about too many lock requests.
> Thanks.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > Just some general information here. Maybe it is of some help to you.
> >
> > In SQL-Server, there are basically two situations that may lead to
> > deadlocks:
> > 1. Locks are acquired in different order in different transactions. This
> > is the 'classic' deadlock. The chance is increased when the transactions
> > can not use an index to lock at row level. The chance is also increased
> > when there are many lock requests, or when memory is low.
> >
> > 2. Lock escalation (from row/page locks to table locks) can lead to
> > deadlocks. If lock escalation is caused by low memory, then lowering
> > your locking granularity from row locks to page locks may help.
> >
> > Gert-Jan
> >
> >
> > Mike Malter wrote:
> > >
> > > I am getting a deadlock error message that is perplexing me.
> > >
> > > Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project.
> The
> > > database and stored procedures are what we have in common. We are doing some testing now to see how well these different
> > > applications can get along with each other, and in one of our tests, ADO edits random records and ADO.NET selects all records,
> we
> > > have received a deadlock error in the ADO.NET application.
> > >
> > > We are both in a transaction when this occurs. The transaction on the ADO side is per record, and the transaction on the
> ADO.NET
> > > side is for the entire select.
> > >
> > > I can understand that the select will want to read a record that is locked by the edit, however the edit is so quick that I
> would
> > > think that the select would not be blocked very long. If that were the case, where the select was blocked too long, wouldn't
> there
> > > be a timeout instead of a deadlock?
> > >
> > > The other factor here is that if we only edit 5 records we never get a deadlock. If we edit more than that, we always get a
> > > deadlock.
> > >
> > > Any insight into this perplexing problem would be very welcomed.
> > >
> > > Mike|||Quick clarification: Simultaneous attempts to escalate locks on the same
table by two processes will never directly lead to deadlock. If a table
level lock (S or X) can not be acquired when escalation is attempted, the
escalation attempt is cancelled and locks will continue to be acquired at
the row/page levels.
Mike: You should verify that the update transactions always commit after a
single update. If not, the randomness of the updates could easily lead to
deadlocks. What is the isolation mode for the select -- read committed, or
stronger?
It would also be helpful if you posted the queries involved and the table
schema -- deadlocks are possible if the update modifies non-clustered index
keys even if each update transasction only modifies one row.
--
Santeri Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5E2280.E7455FE3@.toomuchspamalready.nl...
> The maximum number of outstanding locks is determined by some internal
> formula. A major factor in this formula is the total amount of memory
> that is available to SQL-Server. If the number of actual locks comes
> close to this maximum number, lock escalation will occur/increase. In
> that situation, SQL-Server is more likely to 'trade in' several row
> locks or page locks for one table lock. If this happens for two
> processes that have locks on the same table, this results in a deadlock.
> BOL has a special section for "number of locks the system can allocate"
> Gert-Jan
>
> Mike Malter wrote:
> >
> > Gert,
> >
> > Thanks for your reply.
> >
> > The deal in this case is that there is only one table called parent.
> >
> > What I am doing is a select on this table for two columns while the
other system is doing random updates.
> >
> > I am wondering if you could elaborate a little more about too many lock
requests.
> >
> > Thanks.
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F5CD36E.C05CE94F@.toomuchspamalready.nl...
> > > Just some general information here. Maybe it is of some help to you.
> > >
> > > In SQL-Server, there are basically two situations that may lead to
> > > deadlocks:
> > > 1. Locks are acquired in different order in different transactions.
This
> > > is the 'classic' deadlock. The chance is increased when the
transactions
> > > can not use an index to lock at row level. The chance is also
increased
> > > when there are many lock requests, or when memory is low.
> > >
> > > 2. Lock escalation (from row/page locks to table locks) can lead to
> > > deadlocks. If lock escalation is caused by low memory, then lowering
> > > your locking granularity from row locks to page locks may help.
> > >
> > > Gert-Jan
> > >
> > >
> > > Mike Malter wrote:
> > > >
> > > > I am getting a deadlock error message that is perplexing me.
> > > >
> > > > Our scenario is like this; separate applications using ADO and
ADO.NET with EnterpriseServices are coexisting in this project.
> > The
> > > > database and stored procedures are what we have in common. We are
doing some testing now to see how well these different
> > > > applications can get along with each other, and in one of our tests,
ADO edits random records and ADO.NET selects all records,
> > we
> > > > have received a deadlock error in the ADO.NET application.
> > > >
> > > > We are both in a transaction when this occurs. The transaction on
the ADO side is per record, and the transaction on the
> > ADO.NET
> > > > side is for the entire select.
> > > >
> > > > I can understand that the select will want to read a record that is
locked by the edit, however the edit is so quick that I
> > would
> > > > think that the select would not be blocked very long. If that were
the case, where the select was blocked too long, wouldn't
> > there
> > > > be a timeout instead of a deadlock?
> > > >
> > > > The other factor here is that if we only edit 5 records we never get
a deadlock. If we edit more than that, we always get a
> > > > deadlock.
> > > >
> > > > Any insight into this perplexing problem would be very welcomed.
> > > >
> > > > Mikesql

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.