Hi all
Now a day I am facing too-many deadlock problems, is there any way or
possibility that I make automated job, for diagnose the deadlock victim and
kill that process.
Any advice will be greatly appreciated.
Farhan Iqbal
Why would you want to kill the victim? The application should be able to
recover from a deadlock situation and either retry or gracefully handle it
some how. That really isn't a database issue. You would be best served by
spending that time fixing the reason why deadlocks occur in the first place.
Andrew J. Kelly SQL MVP
"Farhan Iqbal" <mr_farhaniqbal@.hotmail.com> wrote in message
news:eR0mzUrTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim
and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
|||Shouldn't SQL automatically be killing the victim, rolling back it's transaction? There really isn't a need to write a job to do this for you.
Have the application constantly checking for the 1205 message.
"Farhan Iqbal" wrote:
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
>
Showing posts with label facing. Show all posts
Showing posts with label facing. Show all posts
Thursday, March 22, 2012
Deadlock Problem in SQL Trigger Urgent
Hello Db experts,
Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.What does the trigger do?|||I'd bet that we can. Check out the FAQ entry for How to get quick and correct answers (http://www.dbforums.com/showthread.php?t=1212452#post4527530) for help.
-PatP|||if you want it fixed you need to give us code.|||Dear Coolberg
Please know thrigger do some calculation for Table1's last inserted record and insert or Update in an another table like Control1 table|||Hello Db experts,
Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!Easy for you to say! ;) Thanks for the Bengali assistance, I'm sure it will help.
-PatP|||Hello Again,
Sorry for not posting the code before. Please have a look at my code below. Now let me explain what problem I faced,there is a table named Table1 where data being inserted from 4 different sources in huge number (Almost 1000 records/sec), The After Insert trigger(Trigger1) written on Table1 inserts or updates the extracted information from Table1 to Table1_Backup.
Most of the times during Insertion to Table1 I am getting a message as:
"Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. "
and I am loosing data in Table1 i.e not all records were inserted.
But when I removed the Trigger , I didn't get this error message and there were no data loss. Looking Forward For any help to come out of this problem.
Code:
create table Table1
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint
)
create table Table1_Backup
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint
)
Create TRIGGER Trigger1
ON Table1
After INSERT
AS
Declare
@.counter int,
@.sId int,
@.sName varchar(50),
@.sPhone varchar(30),
@.sBalance bigint;
Begin
set @.counter = 0;
set @.sId = 0;
set @.sName = null;
set @.sPhone = null;
set @.sBalance = 0;
select @.sId = sId,
@.sName = sName,
@.sPhone = sPhone,
@.sBalance = sBalance
from INSERTED Table1;
Select @.counter = @.sId From Table1_Backup WHERE sId = @.sId;
IF (@.counter < 1 )
insert into Table1_Backup
values(@.sId,@.sName,@.sPhone,@.sBalance);
ELSE
Update Table1_Backup
set sBalance = sBalance + @.sBalance
Where sId = @.sId;
End|||Hi Dada,
Nin bangaly bolchi, kintu ami to problem ta post korechi shudhu bangali der jonno noy, ekhane to non bangali o ache. Kintu apnar shahajjo korar icche dhekhe khub valo laglo. Tai apnake bangaly likchi. Amra bangalay kotha (chat) bolte pari jodi apni chan, amar yahoo id hocche "mahfuz_onsky@.yahoo.com".
Asha kori shomadhan ta apnar kachei pabo ebong ekjon bangali dada er kache pele valoi lagbe.
Kotha hobe.
Mahfuz.
Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Can you show me the Insert statement that you are using to insert data? By the way which version of Sql server are you running?
It may be a problem in your application code,plz provide the code where you are inserting the data.|||In SQL Server, a trigger fires once for each SQL Statement that launches the trigger, no matter how many rows the original statement might have affected. There could be 1000 or more rows in the INSERTED table for any given execution of your trigger.
There appears to be a logic error inside your trigger, but without knowing exactly what you are trying to do, I can't fix that. The code translated to use sets instead of working one row at a time would be:CREATE TRIGGER Trigger1
ON Table1
After INSERT
AS
INSERT INTO Table1_Backup (
sId, sName, sPhone, sBalance)
SELECT sId, sName, sPhone, sBalance
FROM INSERTED
WHERE sID < 1
UPDATE Table1_Backup
SET sBalance = sBalance + (SELECT Sum(INSERTED.sBalance)
FROM INSERTED
WHERE INSERTED.sId = Table1_Backup.sId)
END-PatPsql
Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.What does the trigger do?|||I'd bet that we can. Check out the FAQ entry for How to get quick and correct answers (http://www.dbforums.com/showthread.php?t=1212452#post4527530) for help.
-PatP|||if you want it fixed you need to give us code.|||Dear Coolberg
Please know thrigger do some calculation for Table1's last inserted record and insert or Update in an another table like Control1 table|||Hello Db experts,
Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!Easy for you to say! ;) Thanks for the Bengali assistance, I'm sure it will help.
-PatP|||Hello Again,
Sorry for not posting the code before. Please have a look at my code below. Now let me explain what problem I faced,there is a table named Table1 where data being inserted from 4 different sources in huge number (Almost 1000 records/sec), The After Insert trigger(Trigger1) written on Table1 inserts or updates the extracted information from Table1 to Table1_Backup.
Most of the times during Insertion to Table1 I am getting a message as:
"Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. "
and I am loosing data in Table1 i.e not all records were inserted.
But when I removed the Trigger , I didn't get this error message and there were no data loss. Looking Forward For any help to come out of this problem.
Code:
create table Table1
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint
)
create table Table1_Backup
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint
)
Create TRIGGER Trigger1
ON Table1
After INSERT
AS
Declare
@.counter int,
@.sId int,
@.sName varchar(50),
@.sPhone varchar(30),
@.sBalance bigint;
Begin
set @.counter = 0;
set @.sId = 0;
set @.sName = null;
set @.sPhone = null;
set @.sBalance = 0;
select @.sId = sId,
@.sName = sName,
@.sPhone = sPhone,
@.sBalance = sBalance
from INSERTED Table1;
Select @.counter = @.sId From Table1_Backup WHERE sId = @.sId;
IF (@.counter < 1 )
insert into Table1_Backup
values(@.sId,@.sName,@.sPhone,@.sBalance);
ELSE
Update Table1_Backup
set sBalance = sBalance + @.sBalance
Where sId = @.sId;
End|||Hi Dada,
Nin bangaly bolchi, kintu ami to problem ta post korechi shudhu bangali der jonno noy, ekhane to non bangali o ache. Kintu apnar shahajjo korar icche dhekhe khub valo laglo. Tai apnake bangaly likchi. Amra bangalay kotha (chat) bolte pari jodi apni chan, amar yahoo id hocche "mahfuz_onsky@.yahoo.com".
Asha kori shomadhan ta apnar kachei pabo ebong ekjon bangali dada er kache pele valoi lagbe.
Kotha hobe.
Mahfuz.
Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Can you show me the Insert statement that you are using to insert data? By the way which version of Sql server are you running?
It may be a problem in your application code,plz provide the code where you are inserting the data.|||In SQL Server, a trigger fires once for each SQL Statement that launches the trigger, no matter how many rows the original statement might have affected. There could be 1000 or more rows in the INSERTED table for any given execution of your trigger.
There appears to be a logic error inside your trigger, but without knowing exactly what you are trying to do, I can't fix that. The code translated to use sets instead of working one row at a time would be:CREATE TRIGGER Trigger1
ON Table1
After INSERT
AS
INSERT INTO Table1_Backup (
sId, sName, sPhone, sBalance)
SELECT sId, sName, sPhone, sBalance
FROM INSERTED
WHERE sID < 1
UPDATE Table1_Backup
SET sBalance = sBalance + (SELECT Sum(INSERTED.sBalance)
FROM INSERTED
WHERE INSERTED.sId = Table1_Backup.sId)
END-PatPsql
deadlock problem
Hi all. i am facing a deadlock problem .i have included the -t1204 and
-T3605 trace flags and have got the following o/p pu tin sqls server
logs.
-T3605 trace flags and have got the following o/p pu tin sqls server
logs.
2006-06-01 17:49:21.84 spid4
2006-06-01 17:49:21.84 spid4 Wait-for graph
2006-06-01 17:49:21.84 spid4
2006-06-01 17:49:21.84 spid4 ...
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1380
2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:2
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:59 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:1
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 Wait-for graph
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ...
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:59 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:3
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:26.92 spid4 SPID: 72 ECID: 0 Statement Type: SELECT
Line #: 330
2006-06-01 17:49:26.92 spid4 Owner:0x42be84c0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:72 ECID:0
2006-06-01 17:49:26.92 spid4 Wait List:
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:2
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
2006-06-01 17:49:26.92 spid4 Requested By:
2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1380
2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:26.92 spid4 Grant List::
2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:26.92 spid4 Node:1
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 Wait-for graph
2006-06-01 17:49:26.92 spid4
2006-06-01 17:49:26.92 spid4 ...
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Victim Resource Owner:
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:31.93 spid4 SPID: 69 ECID: 0 Statement Type: SELECT
Line #: 330
2006-06-01 17:49:31.93 spid4 Owner:0x42bdaaa0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:69 ECID:0
2006-06-01 17:49:31.93 spid4 Wait List:
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:3
2006-06-01 17:49:31.93 spid4
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:70 ECID:0 Ec:(0x458154e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event:
RMCMUpdateTrades;1
2006-06-01 17:49:31.93 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
Line #: 1521
2006-06-01 17:49:31.93 spid4 Owner:0x42be8140 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:71 ECID:0
2006-06-01 17:49:31.93 spid4 Grant List::
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
CleanCnt:1 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:2
2006-06-01 17:49:31.93 spid4
2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
2006-06-01 17:49:31.93 spid4 Requested By:
2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
2006-06-01 17:49:31.93 spid4 SPID: 70 ECID: 0 Statement Type: SELECT
Line #: 1167
2006-06-01 17:49:31.93 spid4 Owner:0x42bdc7a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:70 ECID:0
2006-06-01 17:49:31.93 spid4 Grant List::
2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 17:49:31.93 spid4 Node:1
2006-06-01 17:49:31.93 spid4
i have two sps says sp1 and sp2 . the logic is as given below.
SP1
Begin Trans
Update table T1 where it goes for Clustered Index Seek. We'r not
updating clustered index columns in update statement
Select From table T1 where it goes for Clustered Index Scan
Update table T2
Select From table T1 where it goes for Clustered Index Scan
Update table T3
Commit Trans
SP2
Begin Trans
Update table T1 where it goes for Clustered Index Seek. We'r not
updating clustered index columns in update statement
Select From table T1 where it goes for Clustered Index Scan
Update table T2
Select From table T1 where it goes for Clustered Index Scan
Update table T3
Commit Trans
SP1 and SP2 can be executed at the same time. This then creates a
deadlock on table T1.
what i fail to understand from the log is
1. in the log it throws an exculsive lock on the select statement
..(but how can a select statement hv an X clusive lock.)
2. moreover it showws that there is a key lock .what i cannot
understand is even in the update statements of the sps i am not updaing
the fileds of the clustered index.
Thanks.> what i fail to understand from the log is
> 1. in the log it throws an exculsive lock on the select statement
> .(but how can a select statement hv an X clusive lock.)
> 2. moreover it showws that there is a key lock .what i cannot
> understand is even in the update statements of the sps i am not updaing
> the fileds of the clustered index.
The exclusive key lock is probably the row-level lock from the previous
uncommitted UPDATE and is not caused by updating key columns. The
subsequent SELECT statement is reported as holding the lock because it's in
the same transaction.
Scans are notorious for causing deadlocks with row-level locking. Consider
this scenario:
Session 1:
BEGIN TRAN
UPDATE T1 row A
Sesion 2:
BEGIN TRAN
UPDATE T1 row B
Session 1:
SELECT * FROM T1 --blocked when row B is encountered
Session 2:
SELECT * FROM T1 --blocked when row A is encountered, causing
deadlock
As far as addressing deadlocks, you can:
1) review your indexing strategy to prevent scans
2) specify a higher-level lock via a table hint (e.g. TABLOCK, HOLDLOCK)
3) retry following a deadlock
--
Hope this helps.
Dan Guzman
SQL Server MVP
"shark" <xavier.sharon@.gmail.com> wrote in message
news:1149358552.884323.98260@.c74g2000cwc.googlegro ups.com...
> Hi all. i am facing a deadlock problem .i have included the -t1204 and
> -T3605 trace flags and have got the following o/p pu tin sqls server
> logs.
>
> 2006-06-01 17:49:21.84 spid4
> 2006-06-01 17:49:21.84 spid4 Wait-for graph
> 2006-06-01 17:49:21.84 spid4
> 2006-06-01 17:49:21.84 spid4 ...
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Requested By:
> 2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
> RMCMUpdateTrades;1
> 2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
> Line #: 1380
> 2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:71 ECID:0
> 2006-06-01 17:49:26.92 spid4 Grant List::
> 2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-06-01 17:49:26.92 spid4 Node:2
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Requested By:
> 2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
> 2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
> Line #: 1167
> 2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:59 ECID:0
> 2006-06-01 17:49:26.92 spid4 Grant List::
> 2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-06-01 17:49:26.92 spid4 Node:1
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 Wait-for graph
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 ...
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Victim Resource Owner:
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:72 ECID:0 Ec:(0x45d214e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Requested By:
> 2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
> 2006-06-01 17:49:26.92 spid4 SPID: 59 ECID: 0 Statement Type: SELECT
> Line #: 1167
> 2006-06-01 17:49:26.92 spid4 Owner:0x42be8e20 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:59 ECID:0
> 2006-06-01 17:49:26.92 spid4 Grant List::
> 2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 17:49:26.92 spid4 Node:3
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Requested By:
> 2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event: RMCMAddOrder;1
> 2006-06-01 17:49:26.92 spid4 SPID: 72 ECID: 0 Statement Type: SELECT
> Line #: 330
> 2006-06-01 17:49:26.92 spid4 Owner:0x42be84c0 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:72 ECID:0
> 2006-06-01 17:49:26.92 spid4 Wait List:
> 2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (b801c993060c)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 17:49:26.92 spid4 Node:2
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:59 ECID:0 Ec:(0x45f4d4e0) Value:0x42b
> 2006-06-01 17:49:26.92 spid4 Requested By:
> 2006-06-01 17:49:26.92 spid4 Input Buf: RPC Event:
> RMCMUpdateTrades;1
> 2006-06-01 17:49:26.92 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
> Line #: 1380
> 2006-06-01 17:49:26.92 spid4 Owner:0x42be8140 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:71 ECID:0
> 2006-06-01 17:49:26.92 spid4 Grant List::
> 2006-06-01 17:49:26.92 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-06-01 17:49:26.92 spid4 Node:1
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 Wait-for graph
> 2006-06-01 17:49:26.92 spid4
> 2006-06-01 17:49:26.92 spid4 ...
> 2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
> 2006-06-01 17:49:31.93 spid4 Victim Resource Owner:
> 2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:71 ECID:0 Ec:(0x46a034e0) Value:0x42b
> 2006-06-01 17:49:31.93 spid4 Requested By:
> 2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
> 2006-06-01 17:49:31.93 spid4 SPID: 69 ECID: 0 Statement Type: SELECT
> Line #: 330
> 2006-06-01 17:49:31.93 spid4 Owner:0x42bdaaa0 Mode: S Flg:0x0
> Ref:1 Life:00000000 SPID:69 ECID:0
> 2006-06-01 17:49:31.93 spid4 Wait List:
> 2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 17:49:31.93 spid4 Node:3
> 2006-06-01 17:49:31.93 spid4
> 2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:70 ECID:0 Ec:(0x458154e0) Value:0x42b
> 2006-06-01 17:49:31.93 spid4 Requested By:
> 2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event:
> RMCMUpdateTrades;1
> 2006-06-01 17:49:31.93 spid4 SPID: 71 ECID: 0 Statement Type: SELECT
> Line #: 1521
> 2006-06-01 17:49:31.93 spid4 Owner:0x42be8140 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:71 ECID:0
> 2006-06-01 17:49:31.93 spid4 Grant List::
> 2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (bd01b71dcec3)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-06-01 17:49:31.93 spid4 Node:2
> 2006-06-01 17:49:31.93 spid4
> 2006-06-01 17:49:31.93 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:69 ECID:0 Ec:(0x4583f4e0) Value:0x42b
> 2006-06-01 17:49:31.93 spid4 Requested By:
> 2006-06-01 17:49:31.93 spid4 Input Buf: RPC Event: RMCMAddOrder;1
> 2006-06-01 17:49:31.93 spid4 SPID: 70 ECID: 0 Statement Type: SELECT
> Line #: 1167
> 2006-06-01 17:49:31.93 spid4 Owner:0x42bdc7a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:70 ECID:0
> 2006-06-01 17:49:31.93 spid4 Grant List::
> 2006-06-01 17:49:31.93 spid4 KEY: 8:776441890:1 (b801c993060c)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 17:49:31.93 spid4 Node:1
> 2006-06-01 17:49:31.93 spid4
> i have two sps says sp1 and sp2 . the logic is as given below.
>
> SP1
> Begin Trans
> Update table T1 where it goes for Clustered Index Seek. We'r not
> updating clustered index columns in update statement
> Select From table T1 where it goes for Clustered Index Scan
> Update table T2
> Select From table T1 where it goes for Clustered Index Scan
> Update table T3
> Commit Trans
>
> SP2
> Begin Trans
> Update table T1 where it goes for Clustered Index Seek. We'r not
> updating clustered index columns in update statement
> Select From table T1 where it goes for Clustered Index Scan
> Update table T2
> Select From table T1 where it goes for Clustered Index Scan
> Update table T3
> Commit Trans
>
> SP1 and SP2 can be executed at the same time. This then creates a
> deadlock on table T1.
> what i fail to understand from the log is
> 1. in the log it throws an exculsive lock on the select statement
> .(but how can a select statement hv an X clusive lock.)
> 2. moreover it showws that there is a key lock .what i cannot
> understand is even in the update statements of the sps i am not updaing
> the fileds of the clustered index.
> Thanks.|||hi dan,
thanks for your help .
a few queries .........
you have mentioned
about
>1) review your indexing strategy to prevent scans
how do i do this?do u mean that i should reconsider the columns that i
use in clustered index?
will using an index hint help in this case?
thanks once again .
Dan Guzman wrote:
> > what i fail to understand from the log is
> > 1. in the log it throws an exculsive lock on the select statement
> > .(but how can a select statement hv an X clusive lock.)
> > 2. moreover it showws that there is a key lock .what i cannot
> > understand is even in the update statements of the sps i am not updaing
> > the fileds of the clustered index.
> The exclusive key lock is probably the row-level lock from the previous
> uncommitted UPDATE and is not caused by updating key columns. The
> subsequent SELECT statement is reported as holding the lock because it's in
> the same transaction.
> Scans are notorious for causing deadlocks with row-level locking. Consider
> this scenario:
> Session 1:
> BEGIN TRAN
> UPDATE T1 row A
> Sesion 2:
> BEGIN TRAN
> UPDATE T1 row B
> Session 1:
> SELECT * FROM T1 --blocked when row B is encountered
> Session 2:
> SELECT * FROM T1 --blocked when row A is encountered, causing
> deadlock
> As far as addressing deadlocks, you can:
> 1) review your indexing strategy to prevent scans
> 2) specify a higher-level lock via a table hint (e.g. TABLOCK, HOLDLOCK)
> 3) retry following a deadlock
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||shark (xavier.sharon@.gmail.com) writes:
> thanks for your help .
> a few queries .........
> you have mentioned
> about
>>1) review your indexing strategy to prevent scans
> how do i do this?do u mean that i should reconsider the columns that i
> use in clustered index?
That and non-clustered indexes. Since you did not post tables or the
actual statements, it is of course impossible for us here to suggest
anything.
Also, when you review indexing, you cannot only to this with this
particular deadlock in mind, but you do of course need to consider
other queries.
> will using an index hint help in this case?
Impossible to tell from this distance, but generally you should avoid
index hints, and only use them as a last resort.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Wednesday, March 21, 2012
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,TbSFU
sage,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:
SpCreateCIDWebPageExpertInitializeRollba
ck;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 Cost0/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:
SpCreateCIDWebPageExpertInitializeRollba
ck;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 Ec0x1D915568) Value:0x1940e180 Cost0/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:
SpCreateCIDWebPageProfiInitializeRollbac
k;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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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,TbSFU
sage,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:
> SpCreateCIDWebPageExpertInitializeRollba
ck;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 Ec0x1B58F568) Value:0x193ff980 Cost0/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:
> SpCreateCIDWebPageExpertInitializeRollba
ck;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 Ec0x1D915568) Value:0x1940e180 Cost0/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:
> SpCreateCIDWebPageProfiInitializeRollbac
k;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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> 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...|||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:
> 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:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>
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,TbSFU
sage,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:
SpCreateCIDWebPageExpertInitializeRollba
ck;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 Cost0/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:
SpCreateCIDWebPageExpertInitializeRollba
ck;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 Cost0/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:
SpCreateCIDWebPageProfiInitializeRollbac
k;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 Cost0/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 Cost0/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,TbSFU
sage,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:
> SpCreateCIDWebPageExpertInitializeRollba
ck;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 Cost0/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:
> SpCreateCIDWebPageExpertInitializeRollba
ck;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 Cost0/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:
> SpCreateCIDWebPageProfiInitializeRollbac
k;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 Cost0/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 Cost0/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@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> 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...|||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:
> 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:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>
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,TbUse r
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 Ec0x1B58F568) Value:0x193ff980 Cost0/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 Ec0x1D915568) Value:0x1940e180 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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
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.c om...
> 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,TbUse r
> 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 Ec0x1B58F568) Value:0x193ff980 Cost0/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 Ec0x1D915568) Value:0x1940e180 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/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>...[vbcol=seagreen]
> 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.c om...
|||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:
> 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>. ..
>
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,TbUse r
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 Cost0/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 Cost0/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 Cost0/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 Cost0/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
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.c om...
> 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,TbUse r
> 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 Cost0/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 Cost0/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 Cost0/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 Cost0/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>...[vbcol=seagreen]
> 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.c om...
|||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:
> 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>. ..
>
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.
>
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.
>
deadlock issue in sql server 2000 enterprise edition version 8.00.
we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
--
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>|||thanks for your feedback.
I have seen the article on deadlock but any simpler way to handle it.
like a sp_configure statement
"Ryan" wrote:
> To see what version you're on issue the following :-
> SELECT SERVERPROPERTY('Edition')
> This article may provide help with your deadlocking :-
> http://support.microsoft.com/kb/271509/
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> > we have installed sql server 2000 enterprise edition on our erp server.
> > We are facing frequent deadlock problem ie one process blocks the other
> > process frequently.
> > The compatibility of the databases has been set to 80.
> > First of all whether the version is that of enterprise edition ?
> > secondly any particular setting to resolve the deadlock issues ?
> >
> >
>
>|||I'm afriad there is no quick fix for deadlocking, there are some traceflags
you can turn on to give you detailed information about the nature of your
deadlock :-
DBCC TRACEON (1204,3605,-1)
This will write deadlock information to the SQL Server Errorlog, which can
be read using sp_ReadErrorLog.
Here's a good article about Anti-Blocking strategies :-
http://vyaskn.tripod.com/anti_blocking_strategies.htm
HTH. Ryan
"Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> thanks for your feedback.
> I have seen the article on deadlock but any simpler way to handle it.
> like a sp_configure statement
> "Ryan" wrote:
>> To see what version you're on issue the following :-
>> SELECT SERVERPROPERTY('Edition')
>> This article may provide help with your deadlocking :-
>> http://support.microsoft.com/kb/271509/
>> --
>> HTH. Ryan
>>
>> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
>> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
>> > we have installed sql server 2000 enterprise edition on our erp server.
>> > We are facing frequent deadlock problem ie one process blocks the other
>> > process frequently.
>> > The compatibility of the databases has been set to 80.
>> > First of all whether the version is that of enterprise edition ?
>> > secondly any particular setting to resolve the deadlock issues ?
>> >
>> >
>>|||thanks
"Ryan" wrote:
> I'm afriad there is no quick fix for deadlocking, there are some traceflags
> you can turn on to give you detailed information about the nature of your
> deadlock :-
> DBCC TRACEON (1204,3605,-1)
> This will write deadlock information to the SQL Server Errorlog, which can
> be read using sp_ReadErrorLog.
> Here's a good article about Anti-Blocking strategies :-
> http://vyaskn.tripod.com/anti_blocking_strategies.htm
>
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
> message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> > thanks for your feedback.
> >
> > I have seen the article on deadlock but any simpler way to handle it.
> > like a sp_configure statement
> >
> > "Ryan" wrote:
> >
> >> To see what version you're on issue the following :-
> >>
> >> SELECT SERVERPROPERTY('Edition')
> >>
> >> This article may provide help with your deadlocking :-
> >>
> >> http://support.microsoft.com/kb/271509/
> >>
> >> --
> >> HTH. Ryan
> >>
> >>
> >> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> >> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> >> > we have installed sql server 2000 enterprise edition on our erp server.
> >> > We are facing frequent deadlock problem ie one process blocks the other
> >> > process frequently.
> >> > The compatibility of the databases has been set to 80.
> >> > First of all whether the version is that of enterprise edition ?
> >> > secondly any particular setting to resolve the deadlock issues ?
> >> >
> >> >
> >>
> >>
> >>
>
>sql
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
--
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>|||thanks for your feedback.
I have seen the article on deadlock but any simpler way to handle it.
like a sp_configure statement
"Ryan" wrote:
> To see what version you're on issue the following :-
> SELECT SERVERPROPERTY('Edition')
> This article may provide help with your deadlocking :-
> http://support.microsoft.com/kb/271509/
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> > we have installed sql server 2000 enterprise edition on our erp server.
> > We are facing frequent deadlock problem ie one process blocks the other
> > process frequently.
> > The compatibility of the databases has been set to 80.
> > First of all whether the version is that of enterprise edition ?
> > secondly any particular setting to resolve the deadlock issues ?
> >
> >
>
>|||I'm afriad there is no quick fix for deadlocking, there are some traceflags
you can turn on to give you detailed information about the nature of your
deadlock :-
DBCC TRACEON (1204,3605,-1)
This will write deadlock information to the SQL Server Errorlog, which can
be read using sp_ReadErrorLog.
Here's a good article about Anti-Blocking strategies :-
http://vyaskn.tripod.com/anti_blocking_strategies.htm
HTH. Ryan
"Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> thanks for your feedback.
> I have seen the article on deadlock but any simpler way to handle it.
> like a sp_configure statement
> "Ryan" wrote:
>> To see what version you're on issue the following :-
>> SELECT SERVERPROPERTY('Edition')
>> This article may provide help with your deadlocking :-
>> http://support.microsoft.com/kb/271509/
>> --
>> HTH. Ryan
>>
>> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
>> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
>> > we have installed sql server 2000 enterprise edition on our erp server.
>> > We are facing frequent deadlock problem ie one process blocks the other
>> > process frequently.
>> > The compatibility of the databases has been set to 80.
>> > First of all whether the version is that of enterprise edition ?
>> > secondly any particular setting to resolve the deadlock issues ?
>> >
>> >
>>|||thanks
"Ryan" wrote:
> I'm afriad there is no quick fix for deadlocking, there are some traceflags
> you can turn on to give you detailed information about the nature of your
> deadlock :-
> DBCC TRACEON (1204,3605,-1)
> This will write deadlock information to the SQL Server Errorlog, which can
> be read using sp_ReadErrorLog.
> Here's a good article about Anti-Blocking strategies :-
> http://vyaskn.tripod.com/anti_blocking_strategies.htm
>
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
> message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> > thanks for your feedback.
> >
> > I have seen the article on deadlock but any simpler way to handle it.
> > like a sp_configure statement
> >
> > "Ryan" wrote:
> >
> >> To see what version you're on issue the following :-
> >>
> >> SELECT SERVERPROPERTY('Edition')
> >>
> >> This article may provide help with your deadlocking :-
> >>
> >> http://support.microsoft.com/kb/271509/
> >>
> >> --
> >> HTH. Ryan
> >>
> >>
> >> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> >> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> >> > we have installed sql server 2000 enterprise edition on our erp server.
> >> > We are facing frequent deadlock problem ie one process blocks the other
> >> > process frequently.
> >> > The compatibility of the databases has been set to 80.
> >> > First of all whether the version is that of enterprise edition ?
> >> > secondly any particular setting to resolve the deadlock issues ?
> >> >
> >> >
> >>
> >>
> >>
>
>sql
deadlock issue in sql server 2000 enterprise edition version 8.00.
we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>
Thursday, March 8, 2012
DeadLock
Hello
we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
dead lock issues.
any idea?
Regards
GopiHave you enabled
DBCC TRACEON (1204) ?
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||adding, error message.
Message
Save Profile Failed Transaction (Process ID 75) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
Source
MicroClear.EnterpriseSolutions.DataProfileServices
Stack Trace
at
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileService.SaveProfileFromXmlDocument(XmlDocument
profileDocument, ArrayList updateOtherProfiles, Boolean insertNotExist,
ProfileParam[] updateProfiles, String MainProfileFieldsList, Boolean
byPassSecurity) at
MicroClear.EnterpriseSolutions.Web.Services.ActionServices.Actor.ProfileHelpers.SaveProfile(ArrayList
updateOtherProfiles, Boolean insertNotExist) at
MicroClear.Actors.eFormsAdmin.AdmSubmitRequest.NewSetFlag() in
c:\eForms\Actors\eFormsAdmin\AdmSubmitRequest.cs:line 2528
Base Exception
Int32 SaveProfileFromXmlDocument(System.Xml.XmlDocument,
System.Collections.ArrayList, Boolean,
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileParam[],
System.String, Boolean)
Actor
SetFlag_2
Module
SubmissionService
Framework Msg
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||No, we didnt trace but using SQL profiler, trying to identify.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Have you enabled
> DBCC TRACEON (1204) ?
> "Gopi" <gopi2it@.gmail.com> wrote in message
> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
>> dead lock issues.
>> any idea?
>> Regards
>> Gopi
>|||Gopi
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
"Gopi" <gopi2it@.gmail.com> wrote in message
news:eH$LooSeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> No, we didnt trace but using SQL profiler, trying to identify.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Have you enabled
>> DBCC TRACEON (1204) ?
>> "Gopi" <gopi2it@.gmail.com> wrote in message
>> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are
>> facing dead lock issues.
>> any idea?
>> Regards
>> Gopi
>>
>
we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
dead lock issues.
any idea?
Regards
GopiHave you enabled
DBCC TRACEON (1204) ?
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||adding, error message.
Message
Save Profile Failed Transaction (Process ID 75) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
Source
MicroClear.EnterpriseSolutions.DataProfileServices
Stack Trace
at
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileService.SaveProfileFromXmlDocument(XmlDocument
profileDocument, ArrayList updateOtherProfiles, Boolean insertNotExist,
ProfileParam[] updateProfiles, String MainProfileFieldsList, Boolean
byPassSecurity) at
MicroClear.EnterpriseSolutions.Web.Services.ActionServices.Actor.ProfileHelpers.SaveProfile(ArrayList
updateOtherProfiles, Boolean insertNotExist) at
MicroClear.Actors.eFormsAdmin.AdmSubmitRequest.NewSetFlag() in
c:\eForms\Actors\eFormsAdmin\AdmSubmitRequest.cs:line 2528
Base Exception
Int32 SaveProfileFromXmlDocument(System.Xml.XmlDocument,
System.Collections.ArrayList, Boolean,
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileParam[],
System.String, Boolean)
Actor
SetFlag_2
Module
SubmissionService
Framework Msg
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||No, we didnt trace but using SQL profiler, trying to identify.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Have you enabled
> DBCC TRACEON (1204) ?
> "Gopi" <gopi2it@.gmail.com> wrote in message
> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
>> dead lock issues.
>> any idea?
>> Regards
>> Gopi
>|||Gopi
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
"Gopi" <gopi2it@.gmail.com> wrote in message
news:eH$LooSeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> No, we didnt trace but using SQL profiler, trying to identify.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Have you enabled
>> DBCC TRACEON (1204) ?
>> "Gopi" <gopi2it@.gmail.com> wrote in message
>> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are
>> facing dead lock issues.
>> any idea?
>> Regards
>> Gopi
>>
>
Subscribe to:
Posts (Atom)