June 21, 2011 at 12:58 am
Dear fellas, Is nolock hint good practice to use in delete operation ?
June 21, 2011 at 1:08 am
No lock is never good practice.
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
June 21, 2011 at 1:10 am
Actually while deleting 50k records one at a time, the table getting locked and affecting other processes as well.
June 21, 2011 at 1:26 am
Query hints prevent the db engine from trying to optimize the query , using them tells SQLthe developer knows exactly what they are looking for. If your not sure do not use them.
Try performing soft deletes , i.e isdeleted bit column which will identify records to be deleted. Then offload the delete activity to no peak times to not affect users.
June 21, 2011 at 3:20 am
try running your deletes in smaller batches to reduce the table lock,.
using a lock hint is not a great idea, unless you really know what you are doing
June 21, 2011 at 4:54 am
Agreed. Nolock should not be followed. However if MS has provided that hint, then where exactly it should be used [to be precise] ?
June 21, 2011 at 4:58 am
But, the key point here is, that you can't use nolock with a delete. A delete operation absolutely must lock as it deletes, no options. If you issue a nolock with the delete statement, SQL Server will ignore it on the table being deleted from, but it might use it on any referential integrity checks that occur prior to the delete.
But I'm with Gail, you really shouldn't be using 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
June 21, 2011 at 10:58 am
sqlnaive (6/21/2011)
Agreed. Nolock should not be followed. However if MS has provided that hint, then where exactly it should be used [to be precise] ?
Like Grant said - it won't work in the context of a delete. It's actually not even tolerated in a delete statement anymore in 2008.
NOLOCK is something that at first might sound like a good idea, but it has horrible consequences and side effects. While it does bypass some locks, it does so by ignoring consistency, and will read data in any transactional state (committed or not). As a result, you get dirty reads, you could possibly duplicate records, skip records, see a version of a record which may roll back, or alternatively see an older version of a record which just hasn't been cleared from cache.
The only real way for it to not have these issues at some point in time is for the data to essentially be static (but then again - why would you get blocks on a read-only database).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 21, 2011 at 12:14 pm
sqlnaive (6/21/2011)
Actually while deleting 50k records one at a time, the table getting locked and affecting other processes as well.
Snapshot isolation (or read committed snapshot)
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply