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 TaskProxy0x000000022E2D6598) Value:0xf73a0d40 Cost0/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 TaskProxy0x00000002E0DAE598) Value:0xf87f6440 Cost0/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 TaskProxy0x00000002E0DAE598) Value:0xf87f6440 Cost0/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
Installing SP3 will not resolve the dead lock issues... Simple way to resolve is, if you don't need committed data in SELECT statement then use NOLOCK hint or READ UNCOMMITTED isolation level for SELECT statement. Check the following following blog post for more detais how to resolve.... http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
SQL Server Deadlock and Trace Flag 1204 and 1222- a big question every time Deadlock not logged even though using relevant trace flag? Troubleshooting blocking in SQL Server 2005 and 2008 - new tool These 3blog posts are out of my experinece to solve the issues. HTH
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
