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.