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 isn't logging SQL statements

Hi,
I'm running SQL 2005 SP1 and we're getting some deadlocks. There is nothing
written to the event log. I've turned on 1204 and the log is showing the
deadlock however again, no SQL statements. I see this in the SQL Server log:
Log Viewer could not read information for this log entry. Cause: Data is
Null. This method or property cannot be called on Null values.. Content:.
I've also tried profiling deadlock and deadlock chain events and I can't get
the SQL still. Anyone tell me what I'm doing wrong?
ThanksHi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:03FDEDA3-935F-458F-81AA-9489BAB1C2B5@.microsoft.com...
> Hi,
> I'm running SQL 2005 SP1 and we're getting some deadlocks. There is
> nothing
> written to the event log. I've turned on 1204 and the log is showing the
> deadlock however again, no SQL statements. I see this in the SQL Server
> log:
> Log Viewer could not read information for this log entry. Cause: Data is
> Null. This method or property cannot be called on Null values.. Content:.
> I've also tried profiling deadlock and deadlock chain events and I can't
> get
> the SQL still. Anyone tell me what I'm doing wrong?
> Thanks