什么时候使用NOLOCK？ [英] When is it appropriate to use NOLOCK?
问题描述
我经常遇到一些长时间运行的查询，有时会遇到超时问题和死锁。
我想知道何时使用NOLOCK最合适，在哪里使用？
我是否在更新中使用它？插入？
请注意，您可以在每个表的基础上指定nolock。
我通常在复杂的SELECT查询中使用nolock，但是仅用于几乎从未更改的小查找表以及仅用于显示的数据。您知道这些表列出了当前半年的价格，或者列出了查找ID到字符串等的信息。只有在进行重大更新后，这些内容才会发生变化，之后，服务器通常都会按常规方式重新启动。
显着提高了性能，减少了在最繁忙的时间内出现死锁的可能性，更重要的是，在最坏的情况下，对于涉及到很多表的查询来说，这确实值得注意（这是逻辑上的，他们必须获得更少的锁，并且这些边桌通常在几乎所有地方使用，通常从7-8个减少到需要锁定的4个桌子）
但是要非常小心，不要添加赶紧，不要常规做。正确使用它不会造成伤害，但是如果使用不当，它将造成可怕的伤害。
请勿将其用于高度关键的东西，可计算的东西，因为它
另一个这样的优化是ROWLOCK，它只在行级锁定。当更新（或删除）行彼此不相关的表（例如仅记录日志的表（插入顺序无关紧要）的表）时，这尤其有用。如果您有一个计划，在事务结束的某处将日志记录写入到某个表中，那么这也可以大大提高速度。
如果您的数据库具有相对低百分比的写入可能不值得。我的读写比率低于2：1。
处理此操作时我保存了一些URL：
http://www.developerfusion.com/article/1688 / sql-server-locks / 4 /
I am having timeout issues and deadlocks from time to time with some long running queries.
I'm wondering when is it most appropriate to use NOLOCK and where?
Do I use it on the updates & inserts? or reads?
Note that you can specify nolock on a per table basis.
I typically used nolock in complex SELECT queries, but only for the little lookup tables that almost never changed, and for display-only data. You know the tables that list the prices for the current half year, or lookups of ids to strings etc. Stuff that only changes with major updates after which the servers are usually restarted routinely anyway.
This improved performance significantly, reduced the chance of deadlock in the busiest times, and more importantly it was really noticable during the worst case moments for queries that touched a lot of tables (which is logical, they have to obtain less locks, and those sidetables are often used nearly everywhere, often decreasing from 7-8 to 4 tables that need to be locked)
But be very careful adding it, don't rush it, and don't do it routinely. It won't hurt when used properly, but it will hurt horribly when used improperly.
Don't use it for highly critical stuff, stuff that calculates etc, because it will get inconsistent, anything that leads to a write sooner or later.
Another such optimization is ROWLOCK, which only locks on row level. This is mainly useful when updating (or deleting in) tables where the rows are not related to eachother, like tables where you only put in log records (and the order in which they are inserted doesn't matter). If you have a scheme that somewhere in the end of an transaction a log record is written to some table, this can speed up considerably too.
If your database has a relatively low percentage writes it might not be worth it. I had a read:write ratio of under 2:1.
Some URLs I saved when working on this:
http://www.developerfusion.com/article/1688/sql-server-locks/4/
这篇关于什么时候使用NOLOCK？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
