May 11, 2007 at 9:32 am
Using NOLOCK on an UPDATE statement does not make sense as an XLOCK will be obtained on the table to be updated. To avoid deadlocks UPLOCKs should be applied to all tables within derived tables in a t-sql style UPDATE statement. eg
UPDATE T1
SET ...
FROM Table1 T1
INNER JOIN Table2 WITH (UPDLOCK)
ON ...
INNER JOIN (
SELECT T3.Col1, MAX(T3.Col2) AS Col2
FROM Table3 T3 WITH (UPDLOCK)
GROUP BY T3.Col1
) D
ON ...
If you are still getting problems I would recommend re-writing the UPDATE statements in ANSI style.
May 11, 2007 at 9:39 am
could be fun. so, basically...
UPDATE Table1
SET ...
FROM Table1
INNER JOIN #UPD_TEMP
ON Table1.ID = #UPD_TEMP.ID
I'll discuss that with my colleagues. It may be excessive, like you said because I put a band-aid on the problem yesterday, which has significantly limited (but not eliminated) the underlying problem. I basically put in a table that acts like a token used as a lock. The automated processes have to wait for the token to be released or time-out before they can run. So at least they aren't banging up against each other.
May 11, 2007 at 9:42 am
Saw that, I've been following...Seems that should have worked.
May 11, 2007 at 9:47 am
I appreciate your help, but using UPDLOCK on the joining tables has caused me more pain.
I am using:
DECLARE @val bigint
--assuming T1 and T2 were just inserted into/update with related info
SELECT @val=COUNT_BIG(*) FROM Table1
SELECT @val=COUNT_BIG(*) FROM Table2
UPDATE T1
SET ...
FROM Table1 T1 WITH (NOLOCK)
INNER JOIN Table2 WITH (NOLOCK)
ON ...
INNER JOIN (
SELECT T3.Col1, MAX(T3.Col2) AS Col2
FROM Table3 T3 WITH (NOLOCK) --assuming T3 is a semi-static table
GROUP BY T3.Col1
) D
ON ...
May 11, 2007 at 10:09 am
My example was poor.
The potential problem is when the derived table references the table to be updated. If this is the case then an UPDLOCK needs to be applied in the derived table in order to stop two transactions referenceing the same rows. When two transactions reference the same rows they could potentially try to upgrade them to XLOCKs in a different order causing a deadlock.
UPDATE T1
SET ...
FROM Table1 T1
JOIN (
SELECT T2.col1, MAX(T2.col2) AS col2
FROM Table1 T2 WITH (UPDLOCK)
GROUP BY T2.col1
) D
ON T1.col1 = D.col1
AND T1.col2 = D.col2
May 11, 2007 at 11:23 am
understood, but the NOLOCK is fine for me 😉
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply