January 10, 2019 at 9:31 am
Hello.
We have the following update statement:
UPDATE [MyTable]
SET [ColumnA] = '...'
,[ColumnB] = '...'
WHERE [ColumnC]='...'
The query is executed from a web application. There can be concurrent actions that trigger the updates in parallel on different threads, but not too many (let's say max 10).
ColumnC holds guid values (random strings). There is a unique non-clustered index on ColumnC (with no included columns). The table has a clustered index on a ColumnD (identity column), not used in the query.
ColumnA and ColumnB are not involved in any index.
What we are experiencing is deadlocks every time when several updates are executed at once (from what I noticed when more than 3 updates run in parallel).
What seems weird to me is that we see a huge amount of page locks, request mode = Update, on the non-clustered index.
- First, I don't understand why pagelocks are made of type U. I would expect an IX lock at the page level
- Secondly, as far as I know, U locks are very hard to 'catch', since they are usually hold for a very short period before transitioning to exclusive locks
- Third, there is nothing at first sight to explain the huge amount of locks (thousands - most of them granted, a few dozens waiting). Only a few queries run on that table, and all have the WHERE clause that limit the data to a single row.
SQL Server version is 10.0.5538.
Any ideas?
January 10, 2019 at 12:49 pm
Are there any triggers on the table?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 11, 2019 at 4:29 am
No, there aren't any triggers.
In the end, we made the locks go away by changing the Cost Threshold for Parallelism in sql server properties from 5 to 40.
But still, I would like to understand what is going on.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply