Order of Execution - Deadlocks
-
Monday, December 31, 2012 8:13 PM
Hello,
I have a stored procedure that has multiple updates to the same table and this procedure randomly produces deadlock errors.
Now before someone suggests profiler and traces, I don't have permissions to that.
Is it possible that 1 update statement is holding the lock while the next update statement is trying to write to it?
I was under the impression that the multiple scripts in a stored procedure will execute in order.
Any thoughts welcome.
Try to look ahead so you're not forced to look behind.
All Replies
-
Monday, December 31, 2012 8:29 PMModerator
All statements in this SP will execute in order, in other words, the next UPDATE can not start till the first update finishes. Most likely the problem is because another process was holding the table or the same procedure was executed by several users at the same time.
Are you able to get the deadlock graph?
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Monday, December 31, 2012 8:29 PM
I was under the impression that the multiple scripts in a stored procedure will execute in order.
It is true that statements in a stored procedure will execute consecutively in the specified order. However, a deadlock occurs when different connections hold locks and are waiting on each other. So it seems that you may have a situation where different connections are executing this proc. For example, consider the following sequence:
- connection A updates row 1
- connection B updates row 2
- connection B tries to update row 1 but must wait for connection A to COMMIT
- connection A tries to update row 2 but must wait for connection B to COMMIT
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
-
Monday, December 31, 2012 8:39 PM
Adam,
The contents of a stored procedure do normally run in sequential order. It is possible that "intra-query parallelism" might cause deadlocks. (A few years ago I had serious problems with parallelism deadlock, but not in recent years. I don't know if we weeded out problems in our code or newer versions of SQL Server fixed the problem.)
Most likely, however, is interactions with other queries. Is this stored procedure being run only by you? Or is it being run by many persons? In the latter case, the problem may be multiple people's copy of the stored procedure are deadlocking.
Rather than run profiler or traces, you might ask your administrator to turn on a trace flag that only reports on deadlocks. Trace 1222 (preferred) or 1204 would put entries in the error log.
RLF
- Edited by Russell FieldsMVP Monday, December 31, 2012 8:39 PM
-
Monday, December 31, 2012 8:54 PM
Thanks for all the suggestions.
The stored procedure is only ran by a scheduled job. There should be no other connections using this stored procedure and I've dug through code all day looking for possibly another procedure that might be updating/inserting my table around the the same time the job is scheduled to run. At this point, the deadlock appears to be happening inside of the procedure itself.
Now the idea of parallism deadlocks introduced an interesting idea because there are a few ugly views involved. However, they shouldn't cause any locks correct?
It just sucks that I have no permission to tools work with so I'm stuck with trial and error.
Try to look ahead so you're not forced to look behind.
- Edited by Adam_Turner Monday, December 31, 2012 8:56 PM
-
Monday, December 31, 2012 9:00 PMModeratorCan you disable parallelism with MAXDOP hint for all queries involved in this procedure? Do the tables (views) have triggers?
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Monday, December 31, 2012 9:05 PM
Adam, If you are suffering from 'intra-query parallelism deadlocks' then you should be able to execute the stored procedure with MAXDOP 1 and avoid the problem. (Note that I am not recommending MAXDOP 1 as a general solution.)
Added Note: Intra-parallelism deadlocks are caused by locking, but if two lock requests are in a deadly embrace by holding an unyielding lock (whether or not they intend to update) then one must be killed to resolve the deadlock. Your isolation level and the number of rows involved can both affect this behavior.
See the recent report at: http://www.sqlservercentral.com/Forums/Topic1394904-1550-1.aspx
RLF
- Edited by Russell FieldsMVP Monday, December 31, 2012 9:33 PM Added Note:
-
Monday, December 31, 2012 9:28 PMNo triggers. I will try disabling parallelism on Wednesday.
Try to look ahead so you're not forced to look behind.
-
Monday, December 31, 2012 9:52 PMPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums. So, my first thought is where is the code and why are you so rude? You have a lot of points, so you know better!
>> I have a stored procedure that has multiple updates to the same table and this procedure randomly produces deadlock errors. <<
Are the updates in a TRANSACTION block? Boy, it is hard to debug invisible code!
>> I was under the impression that the multiple scripts in a stored procedure will execute in order. <<
Yes. Mostly. Sort of. There are locks, transaction levels, COMMIT, ROLLBACK, statement failures and other stuff happening. This is why good SQL programmers use procs to hide insanely complicated single statements.
Since you did not mind being vague and general, I will say that 95% of the time, a sequence of updates to that same table can now be combined into one update. The single update will be one transaction, one scan (or less) and run many times faster.
Back in the 1970's the Sybase training class justified using cursors with this problem: Increase the price of cheap books (price < $25) by 10% and reduce the price of expensive books by 5%. You cannot do it as
BEGIN
UPDATE Books
SET price = price * 1.10
WHERE price < 25.00;
UPDATE Books
SET price = price * 0.95
WHERE price >= 25.00;
END;
A book on the $25.00 cusp will go up and down! The argument was that was why we had to use a row-by-row cursor. Today we have this option:
UPDATE Books
SET price
= CASE WHEN price < 25.00
THEN price * 1.10
ELSE price * 0.95 END;
After all these decades I have found that I can use CASE and some other modern SQL features to collapse multiple UPDATEs into one statement.
Want to try again?--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
-
Monday, December 31, 2012 10:08 PMModerator
The following article is on deadlocks and prevention:
http://sqlusa.com/bestpractices/deadlock/
>a stored procedure that has multiple updates to the same table
That sounds like the recipe to produce deadlocks. Can you post the code, DDL & index information? Thanks.
Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012- Edited by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, January 01, 2013 12:40 AM
-
Monday, December 31, 2012 10:10 PM
If this is on SQL 2008 or later, there is no need to enable trace flags, as deadlocks are reported in the System Health session.
Here is a query to find all deadlocks:
SELECT
xed.value('@timestamp', 'datetime2(3)') as CreationDate,
xed.query('.') AS XEvent
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE s.name = N'system_health'
AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESCAdam would still have to ask the DBA to run this query for him.
If there are plenty of deadlocks on the server, it might be necessary to add filters which requires a lot of XQuery. Although since this is in a job, hopefully CreationDate is sufficient.
If I were in Adam's clothes I would crave to get the information, since else I would be operating in a totally dark room.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, December 31, 2012 10:12 PM
-
Tuesday, January 01, 2013 12:55 AMModerator
Hi Erland,
I just simulated a deadlock:
Msg 1205, Level 13, State 51, Line 9 Transaction (Process ID 51) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The xquery above did not pick it up. Thanks.
Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, January 01, 2013 12:56 AM
-
Tuesday, January 01, 2013 10:31 AM
-
Tuesday, January 01, 2013 10:55 AMModerator
Thanks Erland. You are right. I got it now.
<event name="xml_deadlock_report" package="sqlserver" timestamp="2013-01-01T00:52:25.919Z"> <data name="xml_report"> <type name="xml" package="package0" /> <value> <deadlock> <victim-list> <victimProcess id="process2f664ccf8" /> </victim-list> <process-list> <process id="process2f664ccf8" taskpriority="0" logused="0" waitresource="KEY: 16:72057594059030528 (0ca7b7436f59)" waittime="6566" ownerId="8186633" transactionname="user_transaction" lasttranstarted="2012-12-31T19:49:38.473" XDES="0x1b06a6e98" lockMode="U" schedulerid="1" kpid="3316" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-12-31T19:49:38.427" lastbatchcompleted="2012-12-31T19:49:33.463" lastattention="2012-12-31T19:49:33.463" clientapp="Microsoft SQL Server Management Studio - Query" hostname="HPESTAR" hostpid="10652" loginname="HPESTAR\Owner" isolationlevel="read committed (2)" xactid="8186633" currentdb="16" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="9" stmtstart="176" stmtend="302" sqlhandle="0x02000000f569272faac8d0eaf2abd0ee817e4e3f63b1f4c80000000000000000000000000000000000000000"> UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate </frame> </executionStack> <inputbuf> USE AdventureWorks; WHILE ( 1< 2) BEGIN BEGIN TRANSACTION UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate SELECT COUNT(*) FROM Sales.SalesOrderDetail UPDATE Purchasing.PurchaseOrderDetail SET ModifiedDate=ModifiedDate COMMIT TRANSACTION END </inputbuf> </process> <process id="process2f238d0c8" taskpriority="0" logused="144" waitresource="PAGE: 16:1:22688 " waittime="1990" ownerId="8183538" transactionname="user_transaction" lasttranstarted="2012-12-31T19:49:20.533" XDES="0x2f22b7b60" lockMode="U" schedulerid="4" kpid="8072" status="suspended" spid="55" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2012-12-31T19:49:20.407" lastbatchcompleted="2012-12-31T19:47:55.920" lastattention="1900-01-01T00:00:00.920" clientapp="Microsoft SQL Server Management Studio - Query" hostname="HPESTAR" hostpid="10652" isolationlevel="read committed (2)" xactid="8183538" currentdb="16" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200"> <executionStack> <frame procname="AdventureWorks.Sales.iduSalesOrderDetail" line="44" stmtstart="3654" stmtend="4618" sqlhandle="0x0300100076146e6c1e5cce005ba0000000000000000000000000000000000000000000000000000000000000"> UPDATE [Sales].[SalesOrderDetail] SET [Sales].[SalesOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID] AND inserted.[SalesOrderDetailID] = [Sales].[SalesOrderDetail].[SalesOrderDetailID]; -- Update SubTotal in SalesOrderHeader record. Note that this causes the -- SalesOrderHeader trigger to fire which will update the RevisionNumber. </frame> <frame procname="adhoc" line="9" stmtstart="176" stmtend="324" sqlhandle="0x02000000355da0331837745eca99f2663dbfe87d61119ec90000000000000000000000000000000000000000"> UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate </frame> </executionStack> <inputbuf> USE AdventureWorks; WHILE ( 1< 2) BEGIN BEGIN TRANSACTION UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate UPDATE Purchasing.PurchaseOrderDetail SET ModifiedDate=ModifiedDate COMMIT TRANSACTION END </inputbuf> </process> <process id="process2f23d1c38" taskpriority="0" logused="10000" waittime="1989" schedulerid="2" kpid="17264" status="suspended" spid="55" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2012-12-31T19:49:20.407" lastbatchcompleted="2012-12-31T19:47:55.920" lastattention="1900-01-01T00:00:00.920" clientapp="Microsoft SQL Server Management Studio - Query" hostname="HPESTAR" hostpid="10652" isolationlevel="read committed (2)" xactid="8183538" currentdb="16" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200"> <executionStack> <frame procname="AdventureWorks.Sales.iduSalesOrderDetail" line="44" stmtstart="3654" stmtend="4618" sqlhandle="0x0300100076146e6c1e5cce005ba0000000000000000000000000000000000000000000000000000000000000"> UPDATE [Sales].[SalesOrderDetail] SET [Sales].[SalesOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID] AND inserted.[SalesOrderDetailID] = [Sales].[SalesOrderDetail].[SalesOrderDetailID]; -- Update SubTotal in SalesOrderHeader record. Note that this causes the -- SalesOrderHeader trigger to fire which will update the RevisionNumber. </frame> <frame procname="adhoc" line="9" stmtstart="176" stmtend="324" sqlhandle="0x02000000355da0331837745eca99f2663dbfe87d61119ec90000000000000000000000000000000000000000"> UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate </frame> </executionStack> <inputbuf> USE AdventureWorks; WHILE ( 1< 2) BEGIN BEGIN TRANSACTION UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate UPDATE Purchasing.PurchaseOrderDetail SET ModifiedDate=ModifiedDate COMMIT TRANSACTION END </inputbuf> </process> <process id="process2f661ecf8" taskpriority="0" logused="10000" waittime="1990" schedulerid="4" kpid="436" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-12-31T19:49:20.407" lastbatchcompleted="2012-12-31T19:47:55.920" lastattention="1900-01-01T00:00:00.920" clientapp="Microsoft SQL Server Management Studio - Query" hostname="HPESTAR" hostpid="10652" loginname="HPESTAR\Owner" isolationlevel="read committed (2)" xactid="8183538" currentdb="16" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200"> <executionStack> <frame procname="AdventureWorks.Sales.iduSalesOrderDetail" line="44" stmtstart="3654" stmtend="4618" sqlhandle="0x0300100076146e6c1e5cce005ba0000000000000000000000000000000000000000000000000000000000000"> UPDATE [Sales].[SalesOrderDetail] SET [Sales].[SalesOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID] AND inserted.[SalesOrderDetailID] = [Sales].[SalesOrderDetail].[SalesOrderDetailID]; -- Update SubTotal in SalesOrderHeader record. Note that this causes the -- SalesOrderHeader trigger to fire which will update the RevisionNumber. </frame> <frame procname="adhoc" line="9" stmtstart="176" stmtend="324" sqlhandle="0x02000000355da0331837745eca99f2663dbfe87d61119ec90000000000000000000000000000000000000000"> UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate </frame> </executionStack> <inputbuf> USE AdventureWorks; WHILE ( 1< 2) BEGIN BEGIN TRANSACTION UPDATE Sales.SalesOrderDetail SET ModifiedDate=ModifiedDate UPDATE Purchasing.PurchaseOrderDetail SET ModifiedDate=ModifiedDate COMMIT TRANSACTION END </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594059030528" dbid="16" objectname="AdventureWorks.Sales.SalesOrderDetail" indexname="1" id="lock2ef035880" mode="X" associatedObjectId="72057594059030528"> <owner-list> <owner id="process2f661ecf8" mode="X" /> </owner-list> <waiter-list> <waiter id="process2f664ccf8" mode="U" requestType="wait" /> </waiter-list> </keylock> <pagelock fileid="1" pageid="22688" dbid="16" subresource="FULL" objectname="AdventureWorks.Sales.SalesOrderDetail" id="lock2e2ffaf00" mode="IX" associatedObjectId="72057594059030528"> <owner-list> <owner id="process2f664ccf8" mode="IU" /> </owner-list> <waiter-list> <waiter id="process2f238d0c8" mode="U" requestType="wait" /> </waiter-list> </pagelock> <exchangeEvent id="Pipe21f216920" WaitType="e_waitPipeGetRow" nodeId="7"> <owner-list> <owner id="process2f238d0c8" /> </owner-list> <waiter-list> <waiter id="process2f23d1c38" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipe213b42fa0" WaitType="e_waitPipeGetRow" nodeId="2"> <owner-list> <owner id="process2f23d1c38" /> </owner-list> <waiter-list> <waiter id="process2f661ecf8" /> </waiter-list> </exchangeEvent> </resource-list> </deadlock> </value> </data> </event>Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
Wednesday, January 02, 2013 12:59 PM
Here is the procedure:
ALTER PROCEDURE [dbo].[uxp_load_BituminousJMF_hdr] As Update imp_BituminousJMF Set [JMF_ESALs] = rtrim(S.JMF_ESALS ) , [ModifiedBy] = 'Sieve Update' , ModifiedOn = getdate() from dbo.uvw_cmms_BituminousJMF_ESAL as S inner join imp_BituminousJMF as jmf on jmf.SupplierCode = S.SupplierCode and jmf.MaterialClassCode = S.MaterialClassCode and jmf.JMFnumber = S.JMFRefNo and jmf.JMFYear = S.JMFYear where len( rtrim( S.JMF_ESALS) ) > 0 and ( rtrim(jmf.[JMF_ESALs]) <> rtrim( S.JMF_ESALS ) Or jmf.[JMF_ESALs] is null ) ----------------------------------------------- -- I.2 Update AC_Percent ----------------------------------------------- -- The code is in Detail Table ----------------------------------------------- -- II.1 insert new JMF header records ----------------------------------------------- Insert Into imp_BituminousJMF ( [SupplierCode] , [MaterialClassCode] , [JMFNumber] , [JMFYear] , [CreatedBy] , [ModifiedBy] ) select Distinct SupplierCode , MaterialClassCode , JMFRefNo , JMFYear , 'Header Insert' , 'Header Insert' from dbo.uvw_cmms_BituminousJMF as S where not exists (select * from imp_BituminousJMF as jmf where jmf.SupplierCode = S.SupplierCode and jmf.MaterialClassCode = S.MaterialClassCode and jmf.JMFnumber = S.JMFRefNo and jmf.JMFYear = S.JMFYear ) ----------------------------------------------- -- II.2 insert new Supplier records ----------------------------------------------- Insert imp_Supplier (SupplierCode , CreatedBy , ModifiedBy ) Select Distinct SupplierCode , 'JMF', 'JMF' From dbo.uvw_cmms_BituminousJMF As S where not exists (select * from imp_Supplier as lst where lst.SupplierCode = S.SupplierCode ) ----------------------------------------------- -- II.3 insert new matertial code ----------------------------------------------- Insert lkp_tstMaterialClass ( MaterialCodeID, MaterialClassDesc, MaterialClassCode, CreatedBy , ModifiedBy ) Select Distinct MaterialCodeID, MaterialClassCode, MaterialClassCode , 'JMF', 'JMF' From dbo.uvw_cmms_BituminousJMF as S , lkp_tstMaterialCode where not exists (select * from lkp_tstMaterialClass as lst where lst.MaterialClassCode = S.MaterialClassCode ) and MaterialCode = '97' -- Added 8/11/2006 by MMyers for Log 4086 Insert lkp_tstMaterialClass ( MaterialCodeID, MaterialClassDesc, MaterialClassCode, CreatedBy , ModifiedBy ) Select Distinct MaterialCodeID, MaterialClassCode, MaterialClassCode , 'JMF', 'JMF' From dbo.uvw_cmms_BituminousJMF as S , lkp_tstMaterialCode where not exists (select * from lkp_tstMaterialClass as lst where lst.MaterialClassCode = S.MaterialClassCode ) and MaterialCode = '98' --------------------------------------------------------------------------------------------------- ----------------------------------------------- -- II.4 update above 2 IDs in imp_BituminousJMF header table ----------------------------------------------- --Update imp_BituminousJMF --Set SupplierID = supp.SupplierID -- , ModifiedBy = 'SupplierID' -- , ModifiedOn = getdate() --From imp_Supplier as Supp --Where Supp.SupplierCode = imp_BituminousJMF.SupplierCode --And ( imp_BituminousJMF.SupplierID is null -- Or Supp.SupplierID <> imp_BituminousJMF.SupplierID ) Update imp_BituminousJMF Set MaterialCode = '97' -- , MaterialCodeID = 188 , ModifiedBy = 'MaterialCode 97' , ModifiedOn = getdate() Where MaterialCode is null -- Or MaterialCodeID is null ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- Added 8/10/2006 by MMyers for Log 4086 - Update the Material Code field to '98' when oven type is like 'Solvent' Update imp_BituminousJMF Set MaterialCode = '98' , ModifiedBy = 'MaterialCode98' , ModifiedOn = getdate() FROM dbo.uvw_cmms_BituminousJMF INNER JOIN dbo.imp_BituminousJMF ON dbo.uvw_cmms_BituminousJMF.SupplierCode = dbo.imp_BituminousJMF.SupplierCode AND dbo.uvw_cmms_BituminousJMF.MaterialClassCode = dbo.imp_BituminousJMF.MaterialClassCode AND dbo.uvw_cmms_BituminousJMF.JMFYear = dbo.imp_BituminousJMF.JMFYear AND dbo.uvw_cmms_BituminousJMF.JMFRefNo = dbo.imp_BituminousJMF.JMFNumber Where dbo.uvw_cmms_BituminousJMF.OvenMake like 'Solvent%' ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------- -- A.T. 03/23/2012 Update MaterialCode = 97 if it's not solvent ----------------------------------------------- Update imp_BituminousJMF Set MaterialCode = '97' , ModifiedBy = 'MaterialCode97' , ModifiedOn = getdate() FROM dbo.uvw_cmms_BituminousJMF INNER JOIN dbo.imp_BituminousJMF ON dbo.uvw_cmms_BituminousJMF.SupplierCode = dbo.imp_BituminousJMF.SupplierCode AND dbo.uvw_cmms_BituminousJMF.MaterialClassCode = dbo.imp_BituminousJMF.MaterialClassCode AND dbo.uvw_cmms_BituminousJMF.JMFYear = dbo.imp_BituminousJMF.JMFYear AND dbo.uvw_cmms_BituminousJMF.JMFRefNo = dbo.imp_BituminousJMF.JMFNumber Where dbo.uvw_cmms_BituminousJMF.OvenMake NOT LIKE 'Solvent%' ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update imp_BituminousJMF Set MaterialClassID = cls.MaterialClassID , ModifiedBy = 'MaterialClassID97' , ModifiedOn = getdate() From lkp_tstMaterialClass as cls Where cls.MaterialClassCode = imp_BituminousJMF.MaterialClassCode --And (imp_BituminousJMF.MaterialClassID is null --Or cls.MaterialClassID <> imp_BituminousJMF.MaterialClassID ) And MaterialCodeID in ( Select MaterialCodeID From lkp_tstMaterialCode as cd -- Modified 8/11/06 by MMyers for Log 4086 Where cd.MaterialCode = '97') -- Added 8/11/06 by MMyers for Log 4086 Update imp_BituminousJMF Set MaterialClassID = cls.MaterialClassID , ModifiedBy = 'MatlClassIDUpdt' , ModifiedOn = getdate() From uvw_cmms_MaterialClass_MaterialCode cls WHERE cls.MaterialCode= imp_BituminousJMF.MaterialCode AND cls.MaterialClassCode = imp_BituminousJMF.MaterialClassCode ---------------------------------------------------------------------------------- --------------------------------------------------------------- -- II.5 update ESALs -- II.5 update AC_Percent --------------------------------------------------------------- -- for header fix JMF_ESAL - big issue on non-standard input of ESAL range -- for header fix JMF_AC_percent --------------------------------------------------------------- -- Update tst_BituminousProjectTest with new ESAL & AC_Percent if changed --------------------------------------------------------------- Update tst_BituminousProjectTest Set [JMFACPercentage] = jmf.JMF_AC_Percent , [JMFESAL] = jmf.JMF_ESALs , ModifiedOn = getdate() , ModifiedBy = 'JMF Header' From tst_BituminousProjectTest as tst inner join imp_BituminousJMF as jmf on tst.[BituminousJMFID] = jmf.[BituminousJMFID] -- Where tst.JMFACPercentage is null -- or tst.JMFACPercentage <> jmf.JMF_AC_Percent -- or tst.JMFESAL is null -- or tst.JMFESAL <> jmf.JMF_ESALs CREATE UNIQUE NONCLUSTERED INDEX UQ_imp_BituminousJMF ON imp_BituminousJMF ( SupplierCode, MaterialClassCode, JMFNUMBER, JMFYear ) WITH IGNORE_DUP_KEY
Try to look ahead so you're not forced to look behind.
- Edited by Adam_Turner Wednesday, January 02, 2013 3:29 PM
-
Wednesday, January 02, 2013 1:29 PM
Since the conflict is (probably) with another procedure, the code of the procedure alone is not going to resolve anything. (Yeah, I know Kalman asked you to post it.)
However, I make an observation: you don't seem to have any transaction defined, which means that when the procedure dies half-way through, it leaves the database an inconsistent state, and the index you drop will be missing. Not that the lack of a transaction increases the risk for deadlocks, au contraire: with a long transaction, the risk for deadlock is higher. But transactional consistency is more important in my book.
Have you approaced the DBA with the query I posted?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked As Answer by Adam_Turner Thursday, January 03, 2013 1:59 PM
-
Wednesday, January 02, 2013 1:44 PMModeratorAlso, if you're using SQL 2008 or up, some of the statements can be re-written into MERGE and therefore the total number of statements will be reduced.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
.png)
