September 7, 2021 at 10:04 am
Had update query running and select query also running at the same time , but getting time out expired for select query after waiting 30 sec to lock. Update query having no lock and resource consumption 17% IO and CPU. query plan having cluster index seek. Please help out to avoid time out expiry for select query.
September 8, 2021 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 8, 2021 at 12:04 pm
The update query still takes an exclusive lock to make the necessary modifications.
Under pessimistic locking your select query waits till the exclusive lock is released.
Without the tabledefinitions ,query, queryplants it will be hard to guess.
Is the update query optimized? Maybe it benefits from an index so only the necessary rows are retrieved and locked?
September 8, 2021 at 12:25 pm
Let's be really clear here, you can't update a row and read it at the same time, when the UPDATE takes an exclusive lock. NOLOCK, while used as a magic wand by a lot of people, doesn't change this, not at the point of needing to hold the lock while the UPDATE occurs. In order to change data within a database, an exclusive lock must be obtained. Period. So, no reading and writing at the same time. NOLOCK works at the edge, the exclusive part of the lock has already cleared, so you can read dirty data. If that exclusive lock is still in place, there is no reading, regardless of NOLOCK.
Also, from the question, you have NOLOCK hints on your UPDATE query? They're just going to be ignored. You can't NOLOCK and UPDATE. It's a contradiction.
However, you do have a potential solution. You could look to snapshot isolation. This keeps a version of a given row available in temporary storage (either tempdb, or, as of 2019, the user database if you have Accelerated Database Recovery enabled) so that reads can occur at the same time as writes. Now, this adds overhead to the system. You'll be putting more of a load on tempdb. So, testing is going to be a huge factor here.
Alternatively, break up the UPDATE into smaller chunks so that it's not holding as many exclusive locks at the same time. You'll see NOLOCK perform better in those circumstances. Although, I'm just not a fan of painting all the code with NOLOCK. However, it does have it's uses. Don't overuse it.
"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
September 10, 2021 at 7:23 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply