SQL Server Performance

Deadlock Encountered

Discussion in 'Getting Started' started by lcerni, Aug 17, 2009.

  1. lcerni New Member

    SQL Server 2005 SP3 CU1 is installed on our 64 bit Server
    I thought that by installing CU1 that would resolve our deadlock issues but they still appear. The first statement is an insert into the table while the second is a select from that table into a declared table. Any thoughts on how to resolve?
    2009-08-17 12:58:47.30 spid4s Deadlock encountered .... Printing deadlock information
    2009-08-17 12:58:47.30 spid4s Wait-for graph
    2009-08-17 12:58:47.30 spid4s
    2009-08-17 12:58:47.30 spid4s Node:1
    2009-08-17 12:58:47.30 spid4s OBJECT: 7:1854629650:0 CleanCnt:3 Mode:S Flags: 0x0
    2009-08-17 12:58:47.30 spid4s Grant List 1:
    2009-08-17 12:58:47.30 spid4s Owner:0x0000000178EE1540 Mode: S Flg:0x0 Ref:10 Life:00000001 SPID:89 ECID:0 XactLockInfo: 0x0000000217A568E0
    2009-08-17 12:58:47.30 spid4s SPID: 89 ECID: 0 Statement Type: INSERT Line #: 115
    2009-08-17 12:58:47.30 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1565300686]
    2009-08-17 12:58:47.30 spid4s Requested By:
    2009-08-17 12:58:47.30 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000348256370 Mode: IX SPID:72 BatchID:0 ECID:0 TaskProxy:(0x000000022E2D6598) Value:0xf73a0d40 Cost:(0/6520)
    2009-08-17 12:58:47.30 spid4s
    2009-08-17 12:58:47.30 spid4s Node:2
    2009-08-17 12:58:47.30 spid4s PAGE: 7:1:1890230 CleanCnt:2 Mode:IX Flags: 0x2
    2009-08-17 12:58:47.30 spid4s Grant List 0:
    2009-08-17 12:58:47.30 spid4s Owner:0x00000000F56FA700 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:0 XactLockInfo: 0x00000003482563A8
    2009-08-17 12:58:47.30 spid4s SPID: 72 ECID: 0 Statement Type: INSERT Line #: 360
    2009-08-17 12:58:47.30 spid4s Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1169491295]
    2009-08-17 12:58:47.30 spid4s Requested By:
    2009-08-17 12:58:47.30 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000217A568A8 Mode: S SPID:89 BatchID:0 ECID:0 TaskProxy:(0x00000002E0DAE598) Value:0xf87f6440 Cost:(0/0)
    2009-08-17 12:58:47.30 spid4s
    2009-08-17 12:58:47.30 spid4s Victim Resource Owner:
    2009-08-17 12:58:47.30 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000217A568A8 Mode: S SPID:89 BatchID:0 ECID:0 TaskProxy:(0x00000002E0DAE598) Value:0xf87f6440 Cost:(0/0)
    2009-08-17 12:58:47.31 spid20s deadlock-list
    2009-08-17 12:58:47.31 spid20s deadlock victim=processf71198
    2009-08-17 12:58:47.31 spid20s process-list
    2009-08-17 12:58:47.31 spid20s process id=processcceda8 taskpriority=0 logused=6520 waitresource=OBJECT: 7:1854629650:0 waittime=2140 ownerId=116224434 transactionname=user_transaction lasttranstarted=2009-08-17T12:58:45.153 XDES=0x348256370 lockMode=IX schedulerid=1 kpid=5932 status=suspended spid=72 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-08-17T12:58:45.153 lastbatchcompleted=2009-08-17T12:58:45.153 clientapp=.Net SqlClient Data Provider hostname=CLEDCWS001 hostpid=2132 loginname=ext_stay_webservice isolationlevel=read committed (2) xactid=116224434 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
    2009-08-17 12:58:47.31 spid20s executionStack
    2009-08-17 12:58:47.31 spid20s frame procname=InsidEdge.dbo.usp_ws_ext_stay_create_svc_request line=360 stmtstart=23846 stmtend=26530 sqlhandle=0x030007005f05b545ad12e800329c00000100000000000000
    2009-08-17 12:58:47.31 spid20s INSERT INTO order_labor (
    2009-08-17 12:58:47.31 spid20s order_num, client_id, contract_cd,
    2009-08-17 12:58:47.31 spid20s status_cd, invoice_option, service_type_cd, status_type_ind,
    2009-08-17 12:58:47.31 spid20s order_labor_type_cd, entered_dt, due_dt, etc,
    2009-08-17 12:58:47.31 spid20s /*problem_comment_1, problem_comment_2, problem_comment_3,
    2009-08-17 12:58:47.31 spid20s resolution_comment_1, */
    2009-08-17 12:58:47.31 spid20s escalation_ind, labor_billing_ind, sales_tax, call_out_ind,
    2009-08-17 12:58:47.31 spid20s confirmed_apptmt_ind, emp_id, cust_po_num,
    2009-08-17 12:58:47.31 spid20s last_update_userid, last_update_dt, entered_by)
    2009-08-17 12:58:47.31 spid20s VALUES(
    2009-08-17 12:58:47.31 spid20s @order_num, @client_id, @contract_cd,
    2009-08-17 12:58:47.31 spid20s -- 'U',
    2009-08-17 12:58:47.31 spid20s Case
    2009-08-17 12:58:47.31 spid20s When @tech_id IS NULL Then 'U'
    2009-08-17 12:58:47.31 spid20s Else 'A'
    2009-08-17 12:58:47.31 spid20s End,
    2009-08-17 12:58:47.31 spid20s @invoice_option, @service_type_cd, 'L',
    2009-08-17 12:58:47.31 spid20s 'LAB', @hold_timestamp_local, @due_dt, 2,
    2009-08-17 12:58:47.31 spid20s /*Case
    2009-08-17 12:58:47.31 spid20s When Len(@problem_comment) > 0 Then Left(@problem_comment, 255)
    2009-08-17 12:58:47.31 spid20s Else 'SERVICE REQUEST INSERTED BY EXTENDED STAY HOTELS, VIA WEB SERVICE'
    2009-08-17 12:58:47.31 spid20s End,
    2009-08-17 12:58:47.31 spid20s Case
    2009-08-17 12:58:47.31 spid20s When Len(@problem_comment) > 255 Then Substring(@problem_comment, 256, 255)
    2009-08-17 12:58:47.31 spid20s Else NULL --SPEC SAID '', but column is nullable ################################
    2009-08-17 12:58:47.31 spid20s End,
    2009-08-17 12:58:47.31 spid20s Case
    2009-08-17 12:58:47.31 spid20s When
    2009-08-17 12:58:47.31 spid20s inputbuf
    2009-08-17 12:58:47.31 spid20s Proc [Database Id = 7 Object Id = 1169491295]
    2009-08-17 12:58:47.31 spid20s process id=processf71198 taskpriority=0 logused=0 waitresource=PAGE: 7:1:1890230 waittime=1078 ownerId=116223574 transactionname=INSERT lasttranstarted=2009-08-17T12:58:36.773 XDES=0x217a568a8 lockMode=S schedulerid=4 kpid=896 status=suspended spid=89 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-08-17T12:58:36.773 lastbatchcompleted=2009-08-17T12:58:36.773 clientapp=.Net SqlClient Data Provider hostname=CLE01S2K3RS001 hostpid=5788 loginname=srs_reports isolationlevel=read committed (2) xactid=116223574 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
    2009-08-17 12:58:47.31 spid20s executionStack
    2009-08-17 12:58:47.31 spid20s frame procname=InsidEdge.dbo.usp_SRS_cst_all_closed_work_excel line=115 stmtstart=7806 stmtend=33526 sqlhandle=0x03000700ce974c5dae32b500479c00000100000000000000
    2009-08-17 12:58:47.31 spid20s Insert Into @tmp
    2009-08-17 12:58:47.31 spid20s Select distinct
    2009-08-17 12:58:47.31 spid20s o.client_id,
    2009-08-17 12:58:47.31 spid20s cc.subclient_id,
    2009-08-17 12:58:47.31 spid20s sc.subclient_name,
    2009-08-17 12:58:47.31 spid20s o.contract_cd,
    2009-08-17 12:58:47.31 spid20s ccbo.cust_po_num PO_No,
    2009-08-17 12:58:47.31 spid20s o.order_num,
    2009-08-17 12:58:47.31 spid20s ol.ols_tkt_num,
    2009-08-17 12:58:47.31 spid20s ol.service_type_cd,
    2009-08-17 12:58:47.31 spid20s o.cust_ref_num,
    2009-08-17 12:58:47.31 spid20s o.cust_site_cd,
    2009-08-17 12:58:47.31 spid20s s.store_num,
    2009-08-17 12:58:47.31 spid20s o.site_phone_num,
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(o.site_name),
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(o.site_city),
    2009-08-17 12:58:47.31 spid20s o.site_state,
    2009-08-17 12:58:47.31 spid20s o.site_country,
    2009-08-17 12:58:47.31 spid20s ol.due_dt,
    2009-08-17 12:58:47.31 spid20s ol.confirmed_apptmt_ind,
    2009-08-17 12:58:47.31 spid20s CASE
    2009-08-17 12:58:47.31 spid20s when @canceled=0 then
    2009-08-17 12:58:47.31 spid20s ol.time_out
    2009-08-17 12:58:47.31 spid20s else ol.completed_dt
    2009-08-17 12:58:47.31 spid20s END,
    2009-08-17 12:58:47.31 spid20s case
    2009-08-17 12:58:47.31 spid20s when ol.order_labor_type_cd ='REM' then 'R'
    2009-08-17 12:58:47.31 spid20s else 'D'
    2009-08-17 12:58:47.31 spid20s end,
    2009-08-17 12:58:47.31 spid20s ol.emp_id,
    2009-08-17 12:58:47.31 spid20s resolution=olc1.comments,
    2009-08-17 12:58:47.31 spid20s null,
    2009-08-17 12:58:47.31 spid20s cst.s_type,
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(e.emp_last_name) PM,
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(e1.emp_last_name) CRM,
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(e2.emp_last_name) CSM, --05/30/09 by Craig
    2009-08-17 12:58:47.31 spid20s case
    2009-08-17 12:58:47.31 spid20s when e0.consultant_ind='N' then --05/30/09 by Craig e2. replaced with e0.
    2009-08-17 12:58:47.31 spid20s dbo.fc_capitalize_words(e0.emp_last_name+ ', ' + e0.emp
    2009-08-17 12:58:47.31 spid20s inputbuf
    2009-08-17 12:58:47.31 spid20s Proc [Database Id = 7 Object Id = 1565300686]
    2009-08-17 12:58:47.31 spid20s resource-list
    2009-08-17 12:58:47.31 spid20s objectlock lockPartition=0 objid=1854629650 subresource=FULL dbid=7 objectname=InsidEdge.dbo.order_labor id=lock16d9e4300 mode=S associatedObjectId=1854629650
    2009-08-17 12:58:47.31 spid20s owner-list
    2009-08-17 12:58:47.31 spid20s owner id=processf71198 mode=S
    2009-08-17 12:58:47.31 spid20s waiter-list
    2009-08-17 12:58:47.31 spid20s waiter id=processcceda8 mode=IX requestType=wait
    2009-08-17 12:58:47.31 spid20s pagelock fileid=1 pageid=1890230 dbid=7 objectname=InsidEdge.dbo.orders id=lock31a45ac00 mode=IX associatedObjectId=72057595351072768
    2009-08-17 12:58:47.31 spid20s owner-list
    2009-08-17 12:58:47.31 spid20s owner id=processcceda8 mode=IX
    2009-08-17 12:58:47.31 spid20s waiter-list
  2. MohammedU New Member

  3. satya Moderator

  4. lcerni New Member

    Just to let you know... When I click on these bog posts, I get the following error below. However, I did get my answer through this web page: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx? I was thinking that my issue was too many indexes on the table. And one index was an issue. I removed it and eliminated those deadlocks. I was actually missing two indexes. Using the execution plan, without them, the queries were doing a table scan on the primary key. When I used the DTA and added those two indexes, the execution plan was altered. I believe that I have eliminated the deadlocking. I hope that I haven't spoken too soon. Server Error in '/' Application.

    The resource cannot be found.
    Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

    Requested URL: /blogs/tools/archive/2009/03/17/interpretaion-of-sql-server-deadlock-and-trace-flag-1204.aspx

    Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

Share This Page