February 26, 2013 at 9:04 am
Hi,
I have a stored procedure in my SQL 2008 database that contains only the following TSQL :
WITH TableA_CTE AS
(
SELECT TOP (100) ID
FROM dbo.TableA WITH (NOLOCK)
WHERETableAStatusTypeID IN (1, 5, 9) AND DATEDIFF(second, LastUpdateTime, @curUtcDate) >= 300
ORDER BY LastUpdateTime ASC
)
UPDATE TOP (100) TA WITH (ROWLOCK)
SETServerId= @serverId,
TransactionId= @transactionId,
LastUpdateTime= @curUtcDate,
TableAStatusTypeID=CASEWHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePendingTHEN 4
WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePendingTHEN 8
WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPendingTHEN 12
ELSE TableAStatusTypeID + 1
END,
RetriesProcess=CASEWHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePendingTHEN 0
WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePendingTHEN 0
WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPendingTHEN 0
ELSE RetriesProcess + 1
END
FROMdbo.TableA TA
INNER JOIN TableA_CTE CTE
ON TA.ID = CTE.ID;
now, when I run load on the DB and trace it for lock escalation, i can see lock escalation "LOCK_THRESHOLD" on the table (that comes from the above stored procedure).
I running with the defaults of SQL installation and table escalation on "TableA" is allowed.
Why do I have lock escalation although I :
1) used CTE to fetch only 100 rows
2) specified NOLOCK in the CTE select part
3) specified ROWLOCK on the update statement
4) used inner join in order to update only 100 rows.
PLEASE HELP!!! I want to avoid disallowing lock escalation on the table.
Do you know what can be the reason that I run into lock escalation using that SP ?
February 26, 2013 at 9:26 am
You're probably getting escalation because of the rowlock hint.
Without the hint, SQL takes locks at whatever granularity it thinks best and escalates to table locks at a specific threshold
With the hint, SQL takes row locks and escalates to table locks at the same threshold.
So if SQL would have taken page locks, but you force it to take row locks, that means more locks taken and an earlier escalation.
Try dropping both the nolock hint (which is meaningless on the target of an update and harmful elsewhere) and the rowlock hint, see if that helps. Also try tuning the query and indexes. The query's probably not using indexes efficiently, so reading and locking far more rows than ideal.
There are ways to completely prevent escalation, which results in SQL running out of memory and potentially crashing instead of escalating locks.
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
February 26, 2013 at 11:13 am
One change to your code would be this in the CTE:
SELECT TOP (100) ID
FROM dbo.TableA
WHERE TableAStatusTypeID IN (1, 5, 9) AND LastUpdateTime <= dateadd(ss, -300, @CurUtcDate)
ORDER BY LastUpdateTime ASC
February 26, 2013 at 4:36 pm
You join TableA with cte which also is TableA, on ID field. Get rid of the hints, get rid of the cte, get rid of the join. It's a plain simple update. You can write select and turn it into update. Why bringing complexity into simple things?
February 27, 2013 at 12:35 am
it seems to me you are all missing the whole point.
i want to lock only rows (i don't want SQL to lock page / table).
i'm dealing with a huge runtime application that have a lot of updates , inserts and deletes.
if I will remove the hints, SQL will make up on his own.. and will lock pages/tables as well (this i want to avoid)
now, anyone have an idea why there is a lock escalation in my statement?? although there is TOP clause.
getting rid of the ROWLOCK is impossible as i don't want it to be locked in any other level than ROW.
February 27, 2013 at 12:40 am
aviadavi (2/27/2013)
if I will remove the hints, SQL will make up on his own.. and will lock pages/tables as well (this i want to avoid)
Correct, it will. This is the recommended approach. Let SQL manage the locking, don't try to outsmart it as you will often fail (unless you know exactly what you're doing and why)
now, anyone have an idea why there is a lock escalation in my statement?? although there is TOP clause.
I explained that already.
You're probably getting escalation because of the rowlock hint.
Without the hint, SQL takes locks at whatever granularity it thinks best and escalates to table locks at a specific threshold
With the hint, SQL takes row locks and escalates to table locks at the same threshold.
So if SQL would have taken page locks, but you force it to take row locks, that means more locks taken and an earlier escalation.
Plus the query is inefficient and probably lacking useful indexes, so SQL has to read and lock a lot more of the table than it would were the query efficient with suitable indexes.
getting rid of the ROWLOCK is impossible as i don't want it to be locked in any other level than ROW.
Short of traceflags that disable lock escalation completely and leave your server at risk of running out of lock memory, throwing ugly errors and potentially crashing, you cannot force only row locks. The ROWLOCK hint says 'start with row locks, escalate to table if necessary'
I recommend you tune that query, the changes the others have mentioned will make it more efficient and assuming you have appropriate indexes will mean that SQL will have to read and lock less of the table, likely resulting in row locks by default, without needing the hint.
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
February 27, 2013 at 1:25 am
You will not get lock escalation once you simplify your query. 100 rows is not near the escalation threshold, especially if you have right indexes. Check execution plan of your UPDATE statement - is there a full table scan or full index scan ? If yes, you are missing an index.
Additionally, enabling "read committed snapshot" isolation at database level will greatly reduce locking.
February 27, 2013 at 1:42 am
I see. well, I'm running it with read uncommitted isolation level. as i don't care few updates will not be performed exactly as i plan.
as for the execution plan, i see many index seeks and one index scan on a non-clustered index.
how can i know what index is missing? (i ran the missing indexes feature by selecting from the dmv and there is no recommended index to add on that table)
should I look on the predicate? defined values? object? output list?
February 27, 2013 at 1:52 am
aviadavi (2/27/2013)
I see. well, I'm running it with read uncommitted isolation level. as i don't care few updates will not be performed exactly as i plan.
Read uncommitted (and nolock) only apply to selects, not to updates or any other data change. Also, bad idea.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
as for the execution plan, i see many index seeks and one index scan on a non-clustered index.
how can i know what index is missing?
should I look on the predicate? defined values? object? output list?
Index scan = read of the entire index, that's where your lock escalation is coming from. First tune the query as has been recommended in this thread, then see if you still are getting the scans.
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
February 27, 2013 at 2:45 am
Hi,
I updated as the people here suggested.
when i only run :
SELECT TOP (100) ID
FROM dbo.TableA
WHERE TableAStatusTypeID IN (1, 5, 9) AND LastUpdateTime <= dateadd(ss, Retries * -30, @CurUtcDate)
ORDER BY LastUpdateTime ASC
I still get an index scan although Reties column is included in the index.
but when i change Retires to a value - say "100" , it uses index seek.
i was trying to change the order in the index and run it again - but it still uses index scan.
what shall I try now?
February 27, 2013 at 6:08 am
Retries index cannot be used because it has operators applied to it before comparison, it is not "naked" attribute.
Index on LastUpdateTime should be used because you need TOP 100 ordered on that field.
CREATE INDEX TableA_IX_LastUpdateTime
ON dbo.TableA(LastUpdateTime) INCLUDE( TableAStatusTypeID, Retries, ID )
You could help optimizer a bit by splitting LastUpdateTime condition into two,
and one of them has "fixed" boundary, that does not depend on the values in the row:
SELECT TOP (100) ID
FROM dbo.TableA
WHERE TableAStatusTypeID IN (1, 5, 9)
AND LastUpdateTime <= @CurUtcDate
AND LastUpdateTime <= dateadd(ss, Retries * -30, @CurUtcDate)
ORDER BY LastUpdateTime ASC
February 27, 2013 at 6:46 am
aviadavi (2/27/2013)
what shall I try now?
What Lynn suggested right at the beginning.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply