deadlocked on lock resources. SQL Server 2000

Hi, i am getting this error when i am running a stored procedure.

Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

i think so it is getting this error becasue it blocking it self at one point in the SP

DECLARE cty_Cursor CURSOR FOR
SELECT Country FROM TB_Country

declare @.cty varchar(2)

OPEN cty_Cursor;
FETCH NEXT FROM cty_Cursor into @.cty;
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC SP_DO_SOMETHING @.cty
FETCH NEXT FROM cty_Cursor into @.cty;
END;
CLOSE cty_Cursor;
DEALLOCATE cty_Cursor;

i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.

how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.

Regards,

Haroon

what happens when you run the stored procedure outside of the cursor?

what's in the stored procedure?

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql

Deadlock transaction

I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?On Jun 22, 8:17 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?

Read "Analyzing Deadlocks with SQL Server Profiler" in BOL.

http://sqlserver-tips.blogspot.com/|||Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

"Altman" <baltman@.easy-automation.comwrote in message
news:1182518265.867797.118630@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

>I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
>

|||Oscar Santiesteban (o_santiesteban@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.

--
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.mspx|||On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.|||On Jun 26, 10:30 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>

Quote:

Originally Posted by

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.

If you are on 2005, consider snapshot isolation.

http://sqlserver-tips.blogspot.com

Deadlock problem

Can somebody help me.
I spend 2 days on this problem, and i stuck. i`ve got this error:
Transaction (Process ID ***) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction...
There is a trace:
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 1204, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 3605, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON -1, server process ID
(SPID) 57.
2006-06-01 23:37:43.24 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Wait-for graph
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:1
2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 0::
2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:2
2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 3::
2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x93212e40 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:142 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 142 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Wait-for graph
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:1
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 0::
2006-06-01 23:38:00.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:2
2006-06-01 23:38:00.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 3::
2006-06-01 23:38:00.74 spid4 Owner:0x9339d7a0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 130 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:3
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Wait List:
2006-06-01 23:38:00.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)
2006-06-01 23:38:00.74 spid4 Victim Resource Owner:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)(gaploid@.yandex.ru) writes:
> Can somebody help me.
> I spend 2 days on this problem, and i stuck. i`ve got this error:
> Transaction (Process ID ***) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction...
>...
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Wait-for graph
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:1
> 2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 0::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
> UPDATE Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:2
> 2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
> CleanCnt:2 Mode: S Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 3::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
> SELECT Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
> round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
> (sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
> complex, so.New_name FROM New_ServiceOrderProduct sop INNER
> JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
> 2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:55.74 spid4
Without knowing tables, and not seeing the text of the UDPATE statement
is a bit difficult to say for sure.
But there is an UPDATE statement, and the locks involve the clustered
index of two different tables. That would indicate that the UPDATE
includes a cascading foreign key - or a trigger. Or that it's part
of a longer transaction.
As a starting point, post the full text of the SELECT statement,
the full text of the UPDATE statement and the table definitions,
including constraints and indexes for the tables. Also, include
the output of
SELCECT object_name(894678285), object_name(862678171)
I assume that you know which database is database 8.
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.mspx|||I solve this problem. in my select statement i have a inner join to
another table, so i simply redisign my select query and make it without
inner join.
thx for your answer.

> Without knowing tables, and not seeing the text of the UDPATE statement
> is a bit difficult to say for sure.
> But there is an UPDATE statement, and the locks involve the clustered
> index of two different tables. That would indicate that the UPDATE
> includes a cascading foreign key - or a trigger. Or that it's part
> of a longer transaction.
> As a starting point, post the full text of the SELECT statement,
> the full text of the UPDATE statement and the table definitions,
> including constraints and indexes for the tables. Also, include
> the output of
> SELCECT object_name(894678285), object_name(862678171)
> I assume that you know which database is database 8.
>
> --
> 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.mspx

Deadlock diagnosis

We are experiencing the following deadlock error on a SQL Server 2000
system:
"Transaction (Process ID 53) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."
I've done some research to see how I can track where the deadlock is
occurring and I've come across a couple of recommendations to use DBCC
traces 1204 & 1205. I'm trying to track where exactly in our system this is
occurring as we are interacting with 10 different tables within a
transaction.
Can someone explain the full process to using tracing to diagnose deadlocks
as I've never done this before? Also, is this the best option for SQL
Server 2000?
Thanks in advance.Cipher wrote:
> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun
> the transaction."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system
> this is occurring as we are interacting with 10 different tables
> within a transaction.
> Can someone explain the full process to using tracing to diagnose
> deadlocks as I've never done this before? Also, is this the best
> option for SQL Server 2000?
> Thanks in advance.
Using trace flags and tracing are two different things. To use trace
flags, you need to turn them on using DBCC TRACEON/TRACEOFF. You can
send deadlock info to the error log using:
DBCC TRACEON (1204,3605,-1)
You can also monitor deadlocks using Profiler or running a server-side
trace and including the two deadlock events (Lock:Deadlock and
Lock:DeadlockChain). If you use Profiler to monitor these events (even
Lock:Deadlock by itself will do) along with using the 1204 trace flag,
you'll know when to look in the error log.
If you want to use a server-side trace instead of trace flags, you'll
need to include a few SQL/SP events to make sure you know what each SPID
involved in the deadlock was running at the time. You can do this, but
you should use a server-side trace instead of using Profiler because of
the added event collection activity. Add SQL:StmtStarting, RPC:Starting,
and SP:StmtStarting to the two deadlock events for comprehensive event
collection. If you can filter these results to a set of users or
applications you know are having the problem, that can limit the
collection somewhat.
To run a server-side trace, you can create the trace in Profiler and
script it out using the File - Script Trace option. You need to have the
trace save activity to a file on the server itself (not a network
share). Once it's running, you'll need to stop it manually using
sp_trace_setstatus. You can view the results in Profiler orby using the
fn_trace_gettable function.
I would try the small Profiler trace along with trace flag 1204 first.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
In addition to Davids post check out
http://support.microsoft.com/kb/271509/EN-US/
John
"Cipher" wrote:

> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction
."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system this
is
> occurring as we are interacting with 10 different tables within a
> transaction.
> Can someone explain the full process to using tracing to diagnose deadlock
s
> as I've never done this before? Also, is this the best option for SQL
> Server 2000?
> Thanks in advance.
>
>
>|||When you are able to get the trace dump into the log file, you might want to
check "Troubleshooting Deadlocks" in BOL
inorder to interpret the dump file.
Gopi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:90FB9903-EECE-4BC8-87FF-5FF09AFEFB3B@.microsoft.com...
> Hi
> In addition to Davids post check out
> http://support.microsoft.com/kb/271509/EN-US/
> John
> "Cipher" wrote:
>

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?
Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
--
David Gugick
Imceda Software
www.imceda.com

deadlock

why i m getting this error...

Transaction(Process ID 74) was deadlockeds on lock resources with another process and has been
chosen as the deadlock victim.Return the transaction.898989

i think this is usually on data contention, try to check your sp if it updates another table e.g. manually increments a value of a record number

can you post your sp?
|||

Because another process (user) is accessing the same resource (data) and has a lock on the table.

Refer to Books OnLine, Topics:

Deadlocking Detecting and Ending Deadlocks|||i think this is usually a contention problem. does your sp updates another table during execution? e.g. manually creating/incrementing a record number?

can you post your sp?
|||

Because some two processes are trying to access the same resource and sql server has failed one of them. What you can do is run SQLDiag to catch the processes so you can fix it. Here is an article I wrote on how to do this: http://articles.techrepublic.com.com/5100-9592_11-6116287.html Hope this helps.

Tim

|||

hi below is my procedure where i m getting deadlock issues..

ALTER proc Usp_CAMSUpdateSchemeGroup
(
@.Cams_Upload_Master_Id numeric = null,
@.Maker numeric =null
)
as
declare @.scheme_camscode varchar(50),@.scheme_Group varchar(50)

declare UpdateGroup_Cursor cursor for
select scheme_camscode,scheme_group from Tbl_SchemeMst

open UpdateGroup_Cursor

fetch next from UpdateGroup_Cursor
into @.scheme_camscode,@.scheme_Group

while @.@.fetch_status = 0
begin
update tbl_cams_uploaddetails set
cams_schemegroup =@.scheme_Group Where
scheme_Code=@.scheme_camscode

fetch next from UpdateGroup_Cursor
into @.scheme_camscode,@.scheme_Group

end
close UpdateGroup_Cursor
deallocate UpdateGroup_Cursor

-Setting status pending and maker for fresh entry
update tbl_cams_uploaddetails set
Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending'
Where
CAMS_Upload_Master_ID=@.Cams_Upload_Master_Id

|||You really want to consider not using a cursor for this situation. It looks like your query could be rewritten like this:

UPDATE dd
SET schemegroup = mst.scheme_canscode
FROM
tbl_cams_uploaddetails dd
join tbl_schememst mst on dd.schemegroup = scheme_group and dd.scheme_code = mst.scheme_camscode|||

The only concern that I have about this process is wondering why it is necessary to UPDATE the Scheme_Code in tbl_Cams_UploadDetails for ALL rows in the table. That causes a 'table lock' and is contributing to the deadlock issue.

Isn't it more likely that there are a small number of rows in tbl_Cams_UploadDetails that need to be updated -perhaps the same row(s) that are being updated using the input variable @.Cams_Upload_Master_Id?

Maybe both UPDATE statements can be done together...

As Tim indicated, you can completely replace the CURSOR. Here is a 'revised' suggestion:

Code Snippet

ALTER PROCEDURE Usp_CAMSUpdateSchemeGroup
( @.Cams_Upload_Master_Id numeric = null,
@.Maker numeric = null
)
AS
BEGIN
UPDATE d
SET d.Cams_SchemeGroup = m.Scheme_Group
FROM tbl_Cams_UploadDetails d
JOIN tbl_SchemeMst m
ON d.Scheme_Code = m.Scheme_CamsCode

-Setting status pending and maker for fresh entry
UPDATE tbl_Cams_UploadDetails
SET
Maker = @.Maker,
Make_Date = getdate(),
AuthStatus = 2,
Optype = 0,
Compare_Status = 'Pending'
WHERE CAMS_Upload_Master_ID = @.Cams_Upload_Master_Id
END
GO

Deadlock

I get the message "Transaction (Process ID 67) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
YannisHi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>