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
SELECT PreOrderID, <Other columns....>
FROM PreOrders PO -- 2 mil rows
INNER JOIN Customers C -- 50 K rows
ON PO.CustomerNr = C.CustomerNr
WHERE Processed = 0
AND Department = 4 -- This is also the account number related to the Deadlock process
BEGIN
-- < 100 orders
-- Loop through rows
SELECT ProductNr, Amount, Price, <Other columns....>
FROM PreOrders
WHERE PreOrderID = @PreOrderID
BEGIN
-- AVG 9 rows per order
-- Get data to validate the order row with
SELECT Availibilty, Price, isActive <Other columns....>
FROM Products P -- 100 K rows
LEFT JOIN ProductLocations PL -- 35 K rows
ON P.ProductNr = PL.ProductNr
WHERE P.ProductNr = @ProductNr
-- Validate the order rows against the actual product data
-- Bad rows get emailed and insert 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
UPDATE PreOrders -- !!*** WITH (ROWLOCK)
SET Ordernumbers= @OrderNumber, -- Newly generated number)
Processed = 1
WHERE PreOrderID = @PreOrderID
BEGIN
EDIT: Added screenshots
