SQL Server Performance Forum – Threads Archive
deadlock victim
hello<br /><br />we use mssql 2000 , and we have insert update select queries on given table.<br />during loading test with inserts on the table we get in some cases : <br />[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Transaction (Process ID 6<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)<br /><br />what does it means, how can we fix this?<br /><br />thanks jjSee if this helps:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9102 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Also ensure to install latest JDBC driver on client and server in order to avoid any incompatibility. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi we do not have any 2 or more process that using the same row at this table.
more over when load inserting to the table the deadlock occurs! thanks
I think in this case you need to give more informations.
How does the load happen?
What statement do you use?
What is your table look like?
What indexes are present?
…
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
when new users hits our application they are registerd => new inserting to users table
that consist of:
NavigateUserID – varchar 20
Block – bit
AllowToll – bit
RouteStory – bit
VehicleType – varchar 30
TransactionNumber – int
CreationDate – datetime
LastUsageDate – datetime
FinishLastRoute – bit when couple of new users hit our application there are as much inserts as follow: insert into DATABASE_NAME ..TABLE_NAME+" (NavigateUserID, Block, AllowToll, RouteStory, VehicleType, TransactionNumber, CreationDate , LastUsageDate ,FinishLastRoute ) VALUES (?,?,?,?,?,?,?,?,?)"
Hello All,
I’m facing a deadlock situation in one of our production server running on SQL 2K and initaly I got the error in my application log. and the application log information is as follows.
——————————
04/10/08 05:25:04 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:04 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘76232801′,’76232801′,4,’04-10-2008 05:16:33’)
04/10/08 05:25:09 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:09 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040063′,’29040063′,4,’04-10-2008 05:16:35’)
04/10/08 05:25:16 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:16 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040063′,’29040063′,8,’04-10-2008 05:16:35’)
04/10/08 05:25:21 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:21 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (”,’76232801′,3,’04-10-2008 05:16:41′)
04/10/08 05:25:26 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:26 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040068′,’29040068′,4,’04-10-2008 05:16:46’)
04/10/08 05:25:31 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:31 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040068′,’29040068′,8,’04-10-2008 05:16:46’)
04/10/08 05:25:36 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:36 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040073′,’29040073′,4,’04-10-2008 05:17:03’)
04/10/08 05:25:41 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:41 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040073′,’29040073′,8,’04-10-2008 05:17:03’)
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040081′,’29040081′,4,’04-10-2008 05:17:11’)
04/10/08 05:25:43 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040081′,’29040081′,8,’04-10-2008 05:17:11’)
04/10/08 05:25:55 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:25:55 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040086′,’29040086′,4,’04-10-2008 05:17:19’)
04/10/08 05:26:05 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:05 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040086′,’29040086′,8,’04-10-2008 05:17:19’)
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :211 ERROR – FailedQueriesDB : Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040089′,’29040089′,4,’04-10-2008 05:17:25’)
04/10/08 05:26:07 (Pacific Daylight Time), [1732] CVoipDatabase.cpp :212 ERROR – Insert into Call_State_Info (orig_call_id,call_identifier,call_state_id,start_time) values (‘29040089′,’29040089′,8,’04-10-2008 05:17:26’)
——————————After I got this error I ran the below trace…
DBCC TRACEON (3605,1204,-1)
And I didn’t got the deadlock Information in my error Log……Below are few entries for the same time and date from sql error log.
——————————–
04/10/2008 05:27:28,spid4,Unknown,Starting deadlock search 338817
04/10/2008 05:27:28,spid4,Unknown,Target Resource Owner:
04/10/2008 05:27:28,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:104 ECID:0 Ec0x3E1BF548) Value:0x42c3e2e4
04/10/2008 05:27:28,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:104 ECID:0 Ec0x3E1BF548) Value:0x42c3e2e4
04/10/2008 05:27:28,spid4,Unknown,
04/10/2008 05:27:28,spid4,Unknown,End deadlock search 338817 … a deadlock was not found.
04/10/2008 05:27:28,spid4,Unknown,———————————-
04/10/2008 05:26:53,spid4,Unknown,———————————-
04/10/2008 05:26:53,spid4,Unknown,Starting deadlock search 338815
04/10/2008 05:26:53,spid4,Unknown,Target Resource Owner:
04/10/2008 05:26:53,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:51 ECID:0 Ec0x4FA73538) Value:0x42c3e900
04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:51 ECID:0 Ec0x4FA73538) Value:0x42c3e900
04/10/2008 05:26:53,spid4,Unknown,
04/10/2008 05:26:53,spid4,Unknown,End deadlock search 338815 … a deadlock was not found.
04/10/2008 05:26:53,spid4,Unknown,———————————-
04/10/2008 05:26:53,spid4,Unknown,———————————-
04/10/2008 05:26:53,spid4,Unknown,Starting deadlock search 338816
04/10/2008 05:26:53,spid4,Unknown,Target Resource Owner:
04/10/2008 05:26:53,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:51 ECID:1 Ec0x5CDA80C0) Value:0x42c3e9b8
04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:51 ECID:1 Ec0x5CDA80C0) Value:0x42c3e9b8
04/10/2008 05:26:53,spid4,Unknown,
04/10/2008 05:26:53,spid4,Unknown,End deadlock search 338816 … a deadlock was not found.
04/10/2008 05:26:53,spid4,Unknown,———————————-
04/10/2008 05:25:33,spid4,Unknown,———————————-
04/10/2008 05:25:33,spid4,Unknown,Starting deadlock search 338813
04/10/2008 05:25:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:33,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:68 ECID:0 Ec0x3BC0F538) Value:0x42c3fe90
04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:0 Ec0x3BC0F538) Value:0x42c3fe90
04/10/2008 05:25:33,spid4,Unknown,Node:2 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:1 Ec0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,
04/10/2008 05:25:33,spid4,Unknown,End deadlock search 338813 … a deadlock was not found.
04/10/2008 05:25:33,spid4,Unknown,———————————-
04/10/2008 05:25:33,spid4,Unknown,———————————-
04/10/2008 05:25:33,spid4,Unknown,Starting deadlock search 338814
04/10/2008 05:25:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:33,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:68 ECID:1 Ec0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:1 Ec0x5A4760C0) Value:0x42c3f030
04/10/2008 05:25:33,spid4,Unknown,
04/10/2008 05:25:33,spid4,Unknown,End deadlock search 338814 … a deadlock was not found.
04/10/2008 05:25:33,spid4,Unknown,———————————-
04/10/2008 05:25:03,spid4,Unknown,———————————-
04/10/2008 05:25:03,spid4,Unknown,Starting deadlock search 338811
04/10/2008 05:25:03,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:03,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:68 ECID:25 Ec0x49F3C0C0) Value:0x42c3e118
04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:25 Ec0x49F3C0C0) Value:0x42c3e118
04/10/2008 05:25:03,spid4,Unknown,
04/10/2008 05:25:03,spid4,Unknown,End deadlock search 338811 … a deadlock was not found.
04/10/2008 05:25:03,spid4,Unknown,———————————-
04/10/2008 05:25:03,spid4,Unknown,———————————-
04/10/2008 05:25:03,spid4,Unknown,Starting deadlock search 338812
04/10/2008 05:25:03,spid4,Unknown,Target Resource Owner:
04/10/2008 05:25:03,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:68 ECID:24 Ec0x6B76C0C0) Value:0x42c3fcc4
04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:24 Ec0x6B76C0C0) Value:0x42c3fcc4
04/10/2008 05:25:03,spid4,Unknown,
04/10/2008 05:25:03,spid4,Unknown,End deadlock search 338812 … a deadlock was not found.
04/10/2008 05:25:03,spid4,Unknown,———————————-
04/10/2008 05:24:48,spid4,Unknown,———————————-
04/10/2008 05:24:48,spid4,Unknown,Starting deadlock search 338810
04/10/2008 05:24:48,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:48,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:68 ECID:0 Ec0x3BC0F538) Value:0x42c3f9e4
04/10/2008 05:24:48,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:68 ECID:0 Ec0x3BC0F538) Value:0x42c3f9e4
04/10/2008 05:24:48,spid4,Unknown,
04/10/2008 05:24:48,spid4,Unknown,End deadlock search 338810 … a deadlock was not found.
04/10/2008 05:24:48,spid4,Unknown,———————————-
04/10/2008 05:24:33,spid4,Unknown,———————————-
04/10/2008 05:24:33,spid4,Unknown,Starting deadlock search 338800
04/10/2008 05:24:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:33,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:51 ECID:0 Ec0x66667538) Value:0x42c3f988
04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:51 ECID:0 Ec0x66667538) Value:0x42c3f988
04/10/2008 05:24:33,spid4,Unknown,
04/10/2008 05:24:33,spid4,Unknown,End deadlock search 338800 … a deadlock was not found.
04/10/2008 05:24:33,spid4,Unknown,———————————-
04/10/2008 05:24:33,spid4,Unknown,———————————-
04/10/2008 05:24:33,spid4,Unknown,Starting deadlock search 338801
04/10/2008 05:24:33,spid4,Unknown,Target Resource Owner:
04/10/2008 05:24:33,spid4,Unknown,ResType:ExchangeId Stype:’AND’ SPID:51 ECID:5 Ec0x553DC0C0) Value:0x42c3fd20
04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:’AND’ SPID:51 ECID:5 Ec0x553DC0C0) Value:0x42c3fd20
04/10/2008 05:24:33,spid4,Unknown,
04/10/2008 05:24:33,spid4,Unknown,End deadlock search 338801 … a deadlock was not found.
——————————–
Can anybody help in in this…….what should I do?
Rohit
]]>