May 20, 2024 at 9:36 am
I have few snapshot transactions , now my admin has informed me that put no lock in all tables in snapshot transactions, as it was blocking locking deadlocking with others. so I suggested to put read uncommited instead of snapshot when u are putting no lock in all tables of the transaction.
q1) snapshote takes lot of resources , so if u are putting nolock in all tables of the snapshot transaction then it is better to put read uncommitted is my stmt correct?
q2) or as the snapshot says that it does not clash with other transactions then should we look in other directions like resource consumption?
q3) putting nolock means, read uncommited data from the table, does it means same when it is put in side the snapshot transaction or some other creates are there?
your sincerely
May 21, 2024 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.
May 21, 2024 at 8:23 pm
My opinion - NOLOCK is not a solution to blocking or deadlocks. NOLOCK is a "band-aid" solution in most cases and in the rare cases it is "needed", there are better options.
IF you are doing a SELECT, then NOLOCK will just read uncommitted data and read data that may be changing. What I mean is if someone is running a query to insert data while your NOLOCK query is running, there is a chance that the insert causes page splits which results in duplicate rows being read as the row is read in both pages (or so is my understanding). It can also result in missed rows. So I would avoid NOLOCK at all costs.
NOLOCK is also VERY VERY unlikely to stop deadlocks in my experience. I VERY VERY rarely see a deadlock caused by a SELECT. To be honest, I'm not sure if I've ever seen that. If you are putting NOLOCK on an INSERT, UPDATE, or DELETE query, that's not going to do anything. SQL doesn't allow NOLOCK on data change operations as it doesn't make sense.
Brent Ozar has a nice writeup on NOLOCK and why it is bad and why you shouldn't be using it AND alternatives:
https://www.brentozar.com/archive/2021/11/nolock-is-bad-and-you-probably-shouldnt-use-it/
My opinion, if you are seeing a lot of blocking and deadlocks, it is better to investigate the query and see how you can reduce the run times so the blocking becomes smaller. If you see no good way to do that in your query, can you move SOME of the data to temp tables or table variables instead of pulling from the "busy" tables? Pull some of the data (by date for example) to a temp table and use that instead? That can reduce the blocking time on the busy tables.
What I suspect is happening is that your blocking and deadlocks are on data manipulation queries (INSERT, UPDATE, DELETE) and not on SELECT. NOLOCK and Snapshot isolation are not going to help with blocking caused by data manipulation queries. For those, the best option is to find ways to make your data manipulation queries faster OR do things to minimize the locks which will minimize the blocking and reduce the chance of a deadlock.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 21, 2024 at 9:58 pm
If you are putting NOLOCK on an INSERT, UPDATE, or DELETE query, that's not going to do anything. SQL doesn't allow NOLOCK on data change operations as it doesn't make sense.
To be absolutely clear, the table being modified cannot specify NOLOCK, but another table(s) simply referenced in the modification query can.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 22, 2024 at 3:08 pm
Mr. Brian Gale wrote:If you are putting NOLOCK on an INSERT, UPDATE, or DELETE query, that's not going to do anything. SQL doesn't allow NOLOCK on data change operations as it doesn't make sense.
To be absolutely clear, the table being modified cannot specify NOLOCK, but another table(s) simply referenced in the modification query can.
Yes, sorry. I should have been more clear on that. I still think that adding NOLOCK isn't going to solve any problems though. My opinion, someone will need to review the deadlock graphs to determine the cause of the deadlocks and work from there (may need more indexes, may need fewer indexes, may need to tune the queries, may need to rewrite the query). Same thing with the blocking - need to review the queries that are being blocked and take appropriate action. If you have a cursor (for example), that's going to be a performance suck AND can be a cause of blocking.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 27, 2024 at 5:54 am
I am worried that your admin is suggesting using nolock. It suggests they have a limited understanding of how a relational database works, especially SQL Server.
The posts above give the message that nolock is to be avoided. It is good advice.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 27, 2024 at 3:06 pm
Quick comment: NO LOCK == NO LUCK
😎
Has your doctor ever asked you how many painkillers you are taking in order to diagnose your problem?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply