SQL Server Performance

deadlock victim

Discussion in 'T-SQL Performance Tuning for Developers' started by JJmaster, Aug 4, 2005.

  1. JJmaster New Member

    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 jj
  2. FrankKalis Moderator

  3. satya Moderator

    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.
  4. JJmaster New Member

    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
  5. FrankKalis Moderator

    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)
  6. JJmaster New Member

    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 (?,?,?,?,?,?,?,?,?)"
  7. rohit2900 Member

    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 Ec:(0x3E1BF548) Value:0x42c3e2e4
    04/10/2008 05:27:28,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:104 ECID:0 Ec:(0x3E1BF548) 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 Ec:(0x4FA73538) Value:0x42c3e900
    04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x4FA73538) 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 Ec:(0x5CDA80C0) Value:0x42c3e9b8
    04/10/2008 05:26:53,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:1 Ec:(0x5CDA80C0) 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 Ec:(0x3BC0F538) Value:0x42c3fe90
    04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) Value:0x42c3fe90
    04/10/2008 05:25:33,spid4,Unknown,Node:2 ResType:ExchangeId Stype:'AND' SPID:68 ECID:1 Ec:(0x5A4760C0) 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 Ec:(0x5A4760C0) Value:0x42c3f030
    04/10/2008 05:25:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:1 Ec:(0x5A4760C0) 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 Ec:(0x49F3C0C0) Value:0x42c3e118
    04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:25 Ec:(0x49F3C0C0) 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 Ec:(0x6B76C0C0) Value:0x42c3fcc4
    04/10/2008 05:25:03,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:24 Ec:(0x6B76C0C0) 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 Ec:(0x3BC0F538) Value:0x42c3f9e4
    04/10/2008 05:24:48,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:68 ECID:0 Ec:(0x3BC0F538) 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 Ec:(0x66667538) Value:0x42c3f988
    04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:0 Ec:(0x66667538) 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 Ec:(0x553DC0C0) Value:0x42c3fd20
    04/10/2008 05:24:33,spid4,Unknown,Node:1 ResType:ExchangeId Stype:'AND' SPID:51 ECID:5 Ec:(0x553DC0C0) 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

Share This Page