Deadlock issue SQLServer2000

Hi,
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollback;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollback;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk2msftngp13.phx.gbl>...
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...
> > Hi,
> > I'm facing a deadlock issue in a stored procedure which only deletes
> > records from multiple tables.When i run this stotred proc. multiple
> > times I get deadlock between two SPIDs running the same stored
> > procedure's code.On drilling down in SQL trace using flag 1205 and the
> > SQL server trace I found that these are conversion deadlocks.The
> > records are deleted using PK in follwing sequence of
> > tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> > TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> > TbSFUsage -> TbCID -> TbCustomer.
> >
> > As is evident from names of tables - TbUserService
> > ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> > tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFUsage,TbUser
> > depends(FK) on TbCID
> > tables -
> > TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> > tables -
> > TbCID depends(FK) on TbCustomer.
> >
> > This is SQL trace I get...
> > Deadlock encountered ... Printing deadlock information
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Wait-for graph
> > 2004-10-04 19:06:25.27 spid4
> > 2004-10-04 19:06:25.27 spid4 Node:1
> > 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.27 spid4 Wait List:
> > 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> > Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> > 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:78 ECID:0 Ec:(0x1B58F568) Value:0x193ff980 Cost:(0/614)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:2
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> > CleanCnt:2 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageExpertInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:85 ECID:0 Ec:(0x1D915568) Value:0x1940e180 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4
> > 2004-10-04 19:06:25.32 spid4 Node:3
> > 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2004-10-04 19:06:25.32 spid4 Grant List 0::
> > 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> > Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> > 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> > DELETE Line #: 241
> > 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> > SpCreateCIDWebPageProfiInitializeRollback;1
> > 2004-10-04 19:06:25.32 spid4 Requested By:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> > S SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> > 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:83 ECID:0 Ec:(0x1DA1B568) Value:0x1940f1c0 Cost:(0/518)
> > 2004-10-04 19:06:30.34 spid4
> >
> > the resource 277576027 above is table TbCIDAdmin!
> > Until and unless i change the sequence of deletes I get the deadlock
> > in same table.
> > Can anyone figure out why is this happening?
> > I feel the problem lies in FK constraint checking while deleting rows.
> > Because SQL server must be reading(Shared Lock) the child tables
> > before deleting row from a parent table. Also, when I disabled all
> > foreign key constraint checking I stopped getting the deadlock errors!
> > If this is the reason can anyone pl. tell me how can I fix this?
> > Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> > this stored procedure and at the same time other stored
> > procedures/transaction can work with the constraint checking as
> > ususal.
> > There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> > . Can we somehow replicate this functionlaity in SQL server 2000?
> > Pl. help because this problem has become a real pain ....
> >
> > Thanks in advance
> > Bipul|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces. :(
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> >Awaiting your comments?
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:
> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces. :(
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh@.4ax.com>...
> > On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> > >Awaiting your comments?
> >
> > Are you doing joins in your delete statements?
> >
> > Can you show your code?
> >
> > It is curious, since you own the locks on early deletes, but I'd like
> > to try to figure out what SQLServer *thinks* it's doing!
> >
> > Can you break the transaction into several independent pieces - quick
> > workaround, probably.
> >
> > J.
>