January 9, 2014 at 8:32 am
Hi,
I'm getting 20-30 deadlocks per day on the same table. (2 mil rows)
Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg)
Total datatype size per row = 114 bytes
We have multiple departments that run a process that takes orders from our PreOrders table,
checks the rows and inserts them into the actual orders table.
This very import process is VB6. So to illistrate I simplified it in TSQL (Below).
The first SELECT statement is the deadlock victim and the end UPDATE statement is the winner. (Different SPIDs)
There are 4 different departments, each running this process every 30 secs.
After reading several articles, I was thinking my easiest option would be to add WITH (ROWLOCK) to the update statement.
I am able to recreate this entire process in TSQL, except for the automatic printing of invalid rows.
What other options do I have?
This'll be the first time I've had to use a query hint and am a bit reluctant as my knownledge on hints is (still) limited.
--Loop through headers
-- THIS SELECT STATEMENT IS ALWAYS THE DEADLOCK VICTIM
SELECTPreOrderID, <Other columns....>
FROMPreOrders PO -- 2 mil rows
INNER JOIN Customers C -- 50 K rows
ON PO.CustomerNr = C.CustomerNr
WHEREProcessed = 0
AND Department = 4 -- This is also the account number related to the Deadlock process
BEGIN
-- < 100 orders
-- Loop through rows
SELECTProductNr, Amount, Price, <Other columns....>
FROMPreOrders
WHEREPreOrderID = @PreOrderID
BEGIN
-- AVG 9 rows per order
-- Get data to validate the order row with
SELECTAvailibilty, Price, isActive <Other columns....>
FROMProducts P -- 100 K rows
LEFT JOIN ProductLocations PL -- 35 K rows
ON P.ProductNr = PL.ProductNr
WHEREP.ProductNr = @ProductNr
-- Validate the order rows against the actual product data
-- Bad rows get printed and inserted with a counter row (Cancels the row order)
-- Good rows get inserted into actual Orders table
END
-- Generate the new order number
-- Save the head and rows into the acutal order tables
-- Update the preOrder as having been processed
-- THIS UPDATE STATEMENT IS ALWAYS THE DEADLOCK WINNER
UPDATEPreOrders -- !!*** WITH (ROWLOCK)
SETOrdernumbers= @OrderNumber, -- Newly generated number)
Processed= 1
WHEREPreOrderID= @PreOrderID
BEGIN
January 9, 2014 at 8:49 am
The problem I see here is that you getting rows that have not been processed and then checking for bad data or other stuff and then processing them. Because of the time it takes to review the rows another process may come in and start grabbing those same rows while you are in the middle of your update. So, the update wants the row that the select statement has, but the select statement can't finish until it gets the row that the update has locked.
Looks like you have an isolation level issue (concurrency) in that once you have read the rows to be processed they are now free to be accessed by other transactions. What you want to have happen is once the records are read that they are locked to that transaction and no longer available to other transactions. The three isolation levels that are available to you are Repeatable Reads, Serializable and Snapshot Isolation (and Read Committed if using RCSI). I would suggest that you look into snapshot isolation as a possible fix to this issue and make sure that you wrap your entire process in a transaction.
January 10, 2014 at 2:51 am
Could you also post the schema and indices?
January 10, 2014 at 5:14 am
Because of the time it takes to review the rows another process may come in and start grabbing those same rows while you are in the middle of your update
Each of the departments only grab their own rows (DepartmentID = @DepartmentID)
There are no other processes pulling data from the PreOrders table or updating.
If these processes were to run sequentially then the deadlocks wouldn't occur.
That said, would I be able to get away with using a NOLOCK hint on the first SELECT?
That would be close to running the each process in snapshot isolation, right?
Could you also post the schema and indices?
Clive, I'm hoping we can figurre this out without bloating the thread. Btw, the schema is terrible. From the 21 columns, 8 could use smaller data types. But this is not the cause of the deadlocks.
January 10, 2014 at 6:34 am
Dennis Post (1/9/2014)
...This very import process is VB6. ...I am able to recreate this entire process in TSQL, except for the automatic printing of invalid rows....
Can you convert the process to be a single stored procedure? The set for printing wouldn't be a problem, there are a variety of ways to handle it. Your options are far more limited if statements are run sequentially by the client.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 10, 2014 at 8:24 am
Can you convert the process to be a single stored procedure? The set for printing wouldn't be a problem, there are a variety of ways to handle it. Your options are far more limited if statements are run sequentially by the client.
I probably could yes.
The processes are started by our automation domain accounts from random terminal servers.
These account start a process that repeats every 30 seconds, though they can initally start at different times. I believe that if they ran sequentially, then the deadlocks wouldn't occur.
Could you please explain the limitations you are thinking of?
And could you point out a couple of idea's to achieve the printing goal? CLR?
The printers are geographically seperated and dependent on the process account.
Btw, thanks all for helping me out 🙂
January 10, 2014 at 8:32 am
Dennis Post (1/10/2014)
Because of the time it takes to review the rows another process may come in and start grabbing those same rows while you are in the middle of your update
Each of the departments only grab their own rows (DepartmentID = @DepartmentID)
There are no other processes pulling data from the PreOrders table or updating.
If these processes were to run sequentially then the deadlocks wouldn't occur.
That said, would I be able to get away with using a NOLOCK hint on the first SELECT?
That would be close to running the each process in snapshot isolation, right?
It would not be the same as NOLOCK. NOLOCK can return you dirty data and I don't think you want that. Is there a reason you can't use SI? There are different flavors of snapshot one is read committed and the other is snapshot isolation. They both come with the same hit to tempdb, but one gives you more control over which processes use it (snapshot isolation) and the other converts your default isolation level to snapshot (RCSI = big hammer).
January 13, 2014 at 3:31 am
It would not be the same as NOLOCK. NOLOCK can return you dirty data and I don't think you want that.
Dirty reads shouldn't be an issue. Each process only works with their own rows (Dept = @Dept), and only once. There are no other processes that manipulate the data.
Is there a reason you can't use SI?
The process is not nicely wrapped up in a transaction. :(.
It's all done via a VB6 application. Multiple subs, functions and record sets. I'm not sure how to implement SI in this case.
Info links:
January 23, 2014 at 5:09 am
January 23, 2014 at 6:25 am
Dennis Post (1/23/2014)
It's been a week now.NOLOCK hint did the trick.
No more deadlocks with this process.
No dirty read issues.
If you have nolock, then you have the potential for dirty reads, duplicate rows and missed rows and it doesn't matter (for the latter 2) that each process works on one department.
I suggest you ditch the nolock (which can cause some nasty intermittent incorrect results problems) and consider one of the snapshot isolation levels as Keith suggested.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2014 at 4:08 am
If you have nolock, then you have the potential for dirty reads, duplicate rows and missed rows and it doesn't matter (for the latter 2) that each process works on one department.
I suggest you ditch the nolock (which can cause some nasty intermittent incorrect results problems) and consider one of the snapshot isolation levels as Keith suggested.
Without knowing how to accomplish the printing objects, I cannot turn this into a nice transaction.
ChrisM@Work hinted at possibilities...
Are there ways to use SI with VB6 code?
How would I be able to know if dirty reads occur?
IF (SQLPRO Says "No") THEN "Listen" 🙂
Listening....
January 24, 2014 at 4:28 am
Dennis Post (1/24/2014)
If you have nolock, then you have the potential for dirty reads, duplicate rows and missed rows and it doesn't matter (for the latter 2) that each process works on one department.
I suggest you ditch the nolock (which can cause some nasty intermittent incorrect results problems) and consider one of the snapshot isolation levels as Keith suggested.
Without knowing how to accomplish the printing objects, I cannot turn this into a nice transaction.
ChrisM@Work hinted at possibilities...
Are there ways to use SI with VB6 code?
How would I be able to know if dirty reads occur?
IF (SQLPRO Says "No") THEN "Listen" 🙂
Listening....
I was thinking along the lines of populating a table with data generated by your stored procedure. A process disconnected from the stored procedure (it could possibly be tagged onto the end) would poll the table for the existence of data, work with it then truncate the table. The point of this is to allow the transaction to run to completion as quickly as possible and certainly without interference from the printing process.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply