June 19, 2012 at 12:25 am
Hey
We have a scenario in our database where a select statement is blocking an update statement until the entire dataset from the select statement is being returned. I have turned on profiler and analysed the trace and found that page locks are being used for the select statement. The select statement is returning an image column (along with some others) and I've found that when I removed the image column page locks were not being used and the issue is no longer there. The transaction level is set to read committed and I have tested this with lock escalation set to both enabled and Auto.
Unfortunately I don’t have an option to change the queries so I want to know if there is there is anything I can do to fix the issue from SQL server and would also like confirmation on what is causing the issue. I have included the 2 queries below
SELECT "PC0".pxObjClass AS "pxObjClass",
--"PC0".pzPVStream AS "PC0pzPVStream",
"PC0".pzInsKey as "pxInsHandle"
FROM pca_work "PC0"
WHERE ( "PC0".BrokerOfficeName LIKE '%%' )
AND ( "PC0".BrokerOfficeLocation LIKE '%%' )
AND ( "PC0".pxObjClass LIKE '%%' )
AND ( "PC0".pxObjClass NOT LIKE '%asfsdfasdf%' )
AND "PC0".IsIntUWPolicy IS NULL
ORDER BY "PC0".pxCreateDateTime
update pca_work
set brokerofficename = 'Oamps - Hobart test'
where pzinskey = 'WESFARMERS-INS-WORK PGCL00034213'
June 19, 2012 at 4:46 am
Update the query to add hint as below
SELECT "PC0".pxObjClass AS "pxObjClass",
--"PC0".pzPVStream AS "PC0pzPVStream",
"PC0".pzInsKey as "pxInsHandle"
FROM pca_work "PC0" WITH (READUNCOMMITTED)
WHERE ( "PC0".BrokerOfficeName LIKE '%%' )
AND ( "PC0".BrokerOfficeLocation LIKE '%%' )
AND ( "PC0".pxObjClass LIKE '%%' )
AND ( "PC0".pxObjClass NOT LIKE '%asfsdfasdf%' )
AND "PC0".IsIntUWPolicy IS NULL
ORDER BY "PC0".pxCreateDateTime
check if this works for you
June 19, 2012 at 5:24 am
NO! Don't use readuncommitted or nolock. Those lead to differences in the data returned by the query[/url] and are generally not a good solution.
Instead, first, tune the query. All those wild card searches with the wild card at the start of the search, those lead to table scans, no matter what you do. You need to eliminate them in some fashion. Second, explore the different snapshot isolation levels that are possible. Personally I prefer using read_committed_snapshot because it's a database setting, not a connection setting. Here's the Books Online entry on that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 19, 2012 at 5:40 am
If you absolutely cannot follow any of Grant's excellent suggestions, then there is a cheat which may work for you:
-- fuzzy non-blocking prefetch, faster without blob column
SELECT [ONLY the PK]
INTO #Temp
FROM pca_work (NOLOCK)
WHERE BrokerOfficeName LIKE '%%'
AND BrokerOfficeLocation LIKE '%%')
AND pxObjClass LIKE '%%'
AND pxObjClass NOT LIKE '%asfsdfasdf%'
AND IsIntUWPolicy IS NULL
-- index #Temp if required
-- actual fetch
SELECT [all the columns I need]
FROM pca_work pca
INNER JOIN #Temp t ON t.[ONLY the PK] = pca.[ONLY the PK]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2012 at 8:38 pm
Hey Guys
Thanks for the solutions, they're all great ideas, I'm looking to change the query structure first and see how that goes.
two questions
Why is the query grabbing page locks as opposed to row locks when the image column is used in the query?
Why is the select query blocking the update statement when an image column is returned in the select query as opposed to not blocking the update statement when the image column is excluded from the query?
June 20, 2012 at 4:15 am
matthew.peters (6/19/2012)
Hey GuysThanks for the solutions, they're all great ideas, I'm looking to change the query structure first and see how that goes.
two questions
Why is the query grabbing page locks as opposed to row locks when the image column is used in the query?
Why is the select query blocking the update statement when an image column is returned in the select query as opposed to not blocking the update statement when the image column is excluded from the query?
It's doing page locks because the locking mechanism determined it could lock a page quicker than locking a number of rows. That's pretty much it.
A select statement will always block an update statement. But, the longer a select takes, say it's pulling a LOB out of separate storage, the more that block is noticeable. Further, if the select is locking more, say a page instead of a row, there's more to block.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2012 at 8:30 pm
OK I did some more investigations and found that the select query is acquiring an 'intent shared lock' on a certain page which is blocking the update statement. The interesting thing is that the lock on the page is not getting released, I can see other page locks being acquired and released but this particular page is not getting released. When I looked into what data was on the page I found that it's the first in-row data page for that table. Anwyay I just thought I'd share this for anyone who's following the post
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply