odd deadlocking behaviour

I am running SQL Server 2000 SP3a on a single processor computer and:
I have a table with the following columns
CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
[JOB_NO] [int] NOT NULL ,
[OVERRIDDEN_PERIOD_START] [datetime] NULL ,
[OVERRIDDEN_PERIOD_END] [datetime] NULL
) ON [PRIMARY]
GO
That has the following data:
1,06/01/2003 17:00:00,NULL
2,NULL,13/01/2003 08:00:00
2,13/01/2003 17:00:00,NULL
3,NULL,20/01/2003 08:00:00
3,20/01/2003 17:00:00,NULL
4,NULL, 27/01/2003 08:00:00
4,27/01/2003 17:00:00,NULL
If I now try deleting these rows from two database sessions in the order as
seen below I end up with a deadlock:
Firstly on SPID 56:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 2
Secondly on SPID 57:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 1
... This then blocks ? Which I am not sure about.
Thirdly back on SPID 56:
delete from SP148_JOB_AHO_PERIODS where job_no = 6
Creates a deadlock victimising SPID 57.
I accept that this table does not have a primary key but when I add one it
still deadlocks.
Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
Why does a deadlock occur when SPID 56 subsequently deletes the row where
job_no is 6 i.e. no rows?
When I looked at sp_lock output it appeared that both sessions were waiting
on the same RID, does this mean that a RID is not necessarily 1 database
table row?
Thanks- What kind of lock triggered the deadlock? row, index, page, extent, table,
database
- Do you have an index by "job_no"?
AMB
"Tony Jones" wrote:
> I am running SQL Server 2000 SP3a on a single processor computer and:
> I have a table with the following columns
> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> [JOB_NO] [int] NOT NULL ,
> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> [OVERRIDDEN_PERIOD_END] [datetime] NULL
> ) ON [PRIMARY]
> GO
> That has the following data:
> 1,06/01/2003 17:00:00,NULL
> 2,NULL,13/01/2003 08:00:00
> 2,13/01/2003 17:00:00,NULL
> 3,NULL,20/01/2003 08:00:00
> 3,20/01/2003 17:00:00,NULL
> 4,NULL, 27/01/2003 08:00:00
> 4,27/01/2003 17:00:00,NULL
> If I now try deleting these rows from two database sessions in the order as
> seen below I end up with a deadlock:
> Firstly on SPID 56:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 2
> Secondly on SPID 57:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 1
> ... This then blocks ? Which I am not sure about.
> Thirdly back on SPID 56:
> delete from SP148_JOB_AHO_PERIODS where job_no = 6
> Creates a deadlock victimising SPID 57.
> I accept that this table does not have a primary key but when I add one it
> still deadlocks.
> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> Why does a deadlock occur when SPID 56 subsequently deletes the row where
> job_no is 6 i.e. no rows?
> When I looked at sp_lock output it appeared that both sessions were waiting
> on the same RID, does this mean that a RID is not necessarily 1 database
> table row?
> Thanks|||Turn on trace flag 1204 and -1. Then your answers will be in the errorlog.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5E921D65-22E3-40D0-AE4C-BEBF76DF8FC7@.microsoft.com...
>- What kind of lock triggered the deadlock? row, index, page, extent,
>table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
>> I am running SQL Server 2000 SP3a on a single processor computer and:
>> I have a table with the following columns
>> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
>> [JOB_NO] [int] NOT NULL ,
>> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
>> [OVERRIDDEN_PERIOD_END] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> That has the following data:
>> 1,06/01/2003 17:00:00,NULL
>> 2,NULL,13/01/2003 08:00:00
>> 2,13/01/2003 17:00:00,NULL
>> 3,NULL,20/01/2003 08:00:00
>> 3,20/01/2003 17:00:00,NULL
>> 4,NULL, 27/01/2003 08:00:00
>> 4,27/01/2003 17:00:00,NULL
>> If I now try deleting these rows from two database sessions in the order
>> as
>> seen below I end up with a deadlock:
>> Firstly on SPID 56:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 2
>> Secondly on SPID 57:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 1
>> ... This then blocks ? Which I am not sure about.
>> Thirdly back on SPID 56:
>> delete from SP148_JOB_AHO_PERIODS where job_no = 6
>> Creates a deadlock victimising SPID 57.
>> I accept that this table does not have a primary key but when I add one
>> it
>> still deadlocks.
>> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
>> Why does a deadlock occur when SPID 56 subsequently deletes the row where
>> job_no is 6 i.e. no rows?
>> When I looked at sp_lock output it appeared that both sessions were
>> waiting
>> on the same RID, does this mean that a RID is not necessarily 1 database
>> table row?
>> Thanks|||The blocked SPID 57 is waiting on a RID (so a row lock) both SPIDs are
waiting on the same rid i.e. the same fileid:pageid:slot, hence the question
is the RID locking more than one row?
No I have not had a index on job_no I have tried a primary kry on a new
column defined to be the Identity column. This produced the same deadlock
behaviour.
"Alejandro Mesa" wrote:
> - What kind of lock triggered the deadlock? row, index, page, extent, table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
> > I am running SQL Server 2000 SP3a on a single processor computer and:
> >
> > I have a table with the following columns
> > CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> > [JOB_NO] [int] NOT NULL ,
> > [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> > [OVERRIDDEN_PERIOD_END] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > That has the following data:
> >
> > 1,06/01/2003 17:00:00,NULL
> > 2,NULL,13/01/2003 08:00:00
> > 2,13/01/2003 17:00:00,NULL
> > 3,NULL,20/01/2003 08:00:00
> > 3,20/01/2003 17:00:00,NULL
> > 4,NULL, 27/01/2003 08:00:00
> > 4,27/01/2003 17:00:00,NULL
> >
> > If I now try deleting these rows from two database sessions in the order as
> > seen below I end up with a deadlock:
> >
> > Firstly on SPID 56:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 2
> >
> > Secondly on SPID 57:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 1
> > ... This then blocks ? Which I am not sure about.
> >
> > Thirdly back on SPID 56:
> > delete from SP148_JOB_AHO_PERIODS where job_no = 6
> >
> > Creates a deadlock victimising SPID 57.
> >
> > I accept that this table does not have a primary key but when I add one it
> > still deadlocks.
> >
> > Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> > Why does a deadlock occur when SPID 56 subsequently deletes the row where
> > job_no is 6 i.e. no rows?
> >
> > When I looked at sp_lock output it appeared that both sessions were waiting
> > on the same RID, does this mean that a RID is not necessarily 1 database
> > table row?
> >
> > Thanks