October 6, 2015 at 6:06 am
I have a SQL Server 2008R2 SP2 where a NOLOCK query is blocking an update of a different record. I am having a difficult time figuring out why and explaining to the users. The person that runs the NOLOCK query constantly leaves this open all night as I continue to tell her please close down your query tool when you are done. It seems to fall on deaf ears.
What am I missing about this nolock query blocking updates: The select and the update are NOT accessing the same emp record. Completely different rows.
These are the settings on the db:
SET ALLOW_SNAPSHOT_ISOLATION ON;
SET READ_COMMITTED_SNAPSHOT ON;
This is an example of the queries: (slightly changed to hide the obvious)
Select * from emptbl with(nolock) where user like (‘asmith%’)
Update emptbl set EMAIL = abc.abc@yahoo.com where userid = ‘1409630’
October 6, 2015 at 6:09 am
If you're running under read committed snapshot, then why the nolock hint?
Under read committed snapshot, read queries don't take locks anyway, so all the nolock is doing is adding the potential for incorrect data, with NO benefit at all.
Without knowing what the lock wait actually is, what lock the update is waiting for and what lock the select session is holding, there's little that can be said. Query sys.dm_tran_locks for each session, see what locks are granted and what locks are waiting.
Edit: And besides, without an active transaction, locks get released as soon as the statements complete, so leaving the session open all night is not a problem, it won't result in locks being held.
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
October 6, 2015 at 6:30 am
Unless they're starting a transaction and then walking away with it uncommitted. That would lead to this issue, possibly. As Gail says, we don't know what objects are being locked by which process. Identifying that should be step one.
NOLOCK is not magic. Just piling on to what Gail says, why use NOLOCK if you're already using READ COMMITTED SNAPSHOT?
"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
October 6, 2015 at 6:35 am
Grant Fritchey (10/6/2015)
Unless they're starting a transaction and then walking away with it uncommitted.
Seen that happen before. Brought am entire application to a complete standstill on all users' machines.
If it is what's happening, it's not the select with nolock that's causing the blocking. It's something that ran earlier in the transaction.
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
October 6, 2015 at 7:01 am
GilaMonster (10/6/2015)
Grant Fritchey (10/6/2015)
Unless they're starting a transaction and then walking away with it uncommitted.Seen that happen before. Brought am entire application to a complete standstill on all users' machines.
If it is what's happening, it's not the select with nolock that's causing the blocking. It's something that ran earlier in the transaction.
Agreed. And yeah, I've seen it too.
"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
October 6, 2015 at 7:11 am
Ok, thanks for the assistance. Next time it happens I will query sys.dm_tran_locks and see what shows up.
The NOLOCK hint is a left over from when this was in SQL 2000 when she would do this and lock apps out of it. All she does is query specific employee records to find out a status is all after she makes a change to their record to verify the changes she made are changed. I keep telling her close your query tool after she is done. There are automated processess that run at night that update records in this table and her leaving these queries open have hung these jobs in the past. That is why I am asking.
October 6, 2015 at 7:22 am
Unless she's starting a transaction and not committing (and making some form of data modifications in the transaction), closing the query tool is not necessary. In the absence of transactions, locks get released when the statements complete, they're not held until the session is closed.
Sounds like she's starting a transaction before making the changes, but never committing it. Querying it from her session it'll look as though the changes have been made, but they're not committed and hence the locks are being held.
If she is starting a transaction and not committing it, closing the query tool will roll the transaction back, losing her changes.
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
October 6, 2015 at 7:27 am
Does DBCC OPENTRAN show any open transactions belonging to that user?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 6, 2015 at 7:34 am
Markus (10/6/2015)
Ok, thanks for the assistance. Next time it happens I will query sys.dm_tran_locks and see what shows up.The NOLOCK hint is a left over from when this was in SQL 2000 when she would do this and lock apps out of it. All she does is query specific employee records to find out a status is all after she makes a change to their record to verify the changes she made are changed. I keep telling her close your query tool after she is done. There are automated processess that run at night that update records in this table and her leaving these queries open have hung these jobs in the past. That is why I am asking.
There you go. She's doing an update (manual T-SQL? No tool?) and then doing a read to validate. I'll bet however the update is done, it's not committing. That makes the most sense. The SELECT with the NOLOCK is at the end of the transaction, so it looks to be the blocking offender, but it's the UPDATE.
"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
October 6, 2015 at 8:10 am
That just might be it.... when I look at her SPID SQL it shows the select... hum....
That would explain why I see her as a blocking thread to another update... and I kill her thread and the blocked thread then runs. But, it doesn't explain why updating one record would block updating a different record though.
October 6, 2015 at 8:11 am
Eric M Russell (10/6/2015)
Does DBCC OPENTRAN show any open transactions belonging to that user?
Too late to know this now. This happened at 10PM last night. I will keep that in my bag of tricks for next time though. Thanks
October 6, 2015 at 8:18 am
Markus (10/6/2015)
That just might be it.... when I look at her SPID SQL it shows the select... hum....
Yes, because the DMVs show the last statement that a session ran, or the one they're currently running. No DMV shows the previous batch that a session ran.
But, it doesn't explain why updating one record would block updating a different record though.
Because SQL's taken either a page lock or a table lock, probably due to inadequate indexing, maybe due to the way the query was written
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
October 6, 2015 at 8:25 am
GilaMonster (10/6/2015)
Because SQL's taken either a page lock or a table lock, probably due to inadequate indexing, maybe due to the way the query was written
Or both.
"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
October 6, 2015 at 8:57 am
Markus (10/6/2015)
That just might be it.... when I look at her SPID SQL it shows the select... hum....That would explain why I see her as a blocking thread to another update... and I kill her thread and the blocked thread then runs. But, it doesn't explain why updating one record would block updating a different record though.
She's updating email address, probably a variable length character column.
One theory is that this could potentially result in a page split, which require a lock held on any other record contained on that page for the duration of the transaction, which is all night long from what it sounds like.
Another obscure and possibly relevant thing to consider (I vaguely recall this from one of Paul Randal's MCM training podcasts) is that when records are updated under snapshot isolation, SQL Server will actually add a version 14-byte stamp identifier to the record (if it hasn't already been added by snapshot row versioning previously). I'm not sure what side effect that potentially may cause, but perhaps page splits or even blocking, if the transaction is held open for an extended period of time. I mean, padding all versioned records with a 14-byte stamp has got to potentially have some negative impact on concurrency, even if the goal of versioning is better isolation.
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-when-do-versioning-tags-get-added/
Has anyone here actually experienced negative side effects of snapshot version stamping in the field?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 7, 2015 at 10:24 am
I always find it scary that anyone (including myself :-)) is doing direct SQL to "fix" individual records. There are too many ways that a malformed statement can wreak havoc on a database, not to mention just leaving a transaction open and locking a table.
Why not give her one or more stored procedures that do the necessary updates as a clean, safe, and fast transaction?
The procedure(s) could validate that the criteria supplied match one and only one target record, and then apply the change and show the resulting record (without requiring a "nolock").
If you are one of us who has screwed up enough tables in the past that we check, double-check, and triple-check everything (and still manage to make an "oops" now and then), an occasional SQL update may be safe, but this sounds like a "user" regularly being allowed to do direct SQL against production tables. :w00t:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply