Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

unexplained lock escalation - please help! Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
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)
WHERE TableAStatusTypeID IN (1, 5, 9) AND DATEDIFF(second, LastUpdateTime, @curUtcDate) >= 300
ORDER BY LastUpdateTime ASC
)
UPDATE TOP (100) TA WITH (ROWLOCK)
SET ServerId = @serverId,
TransactionId = @transactionId,
LastUpdateTime = @curUtcDate,
TableAStatusTypeID = CASE WHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePending THEN 4
WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePending THEN 8
WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPending THEN 12
ELSE TableAStatusTypeID + 1
END,
RetriesProcess = CASE WHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePending THEN 0
WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePending THEN 0
WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPending THEN 0
ELSE RetriesProcess + 1
END
FROM dbo.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 ?
Post #1424131
Posted Tuesday, February 26, 2013 9:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 40,404, Visits: 36,847
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1424144
Posted Tuesday, February 26, 2013 11:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 20,801, Visits: 32,724
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1424190
Posted Tuesday, February 26, 2013 4:36 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1424281
Posted Wednesday, February 27, 2013 12:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
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.
Post #1424364
Posted Wednesday, February 27, 2013 12:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 40,404, Visits: 36,847
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1424365
Posted Wednesday, February 27, 2013 1:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1424386
Posted Wednesday, February 27, 2013 1:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
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?
Post #1424393
Posted Wednesday, February 27, 2013 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 40,404, Visits: 36,847
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1424399
Posted Wednesday, February 27, 2013 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
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?
Post #1424417
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse