我将如何从不断插入（并且需要）的表中获取数据而不引起任何锁定，以便插入将继续不被注意。
我环顾四周，发现 select with nolock 选项，但是，如果我理解正确，这不会停止锁创建，而是绕过当前锁并获取所有内容？
谢谢。
编辑：该表永远不会更新，只会插入和选择
How would I go about grabbing data from a table that is CONSTANTLY being inserted into (and needs to be) without causing any locking so that the inserts will continue unheeded.
I've looked around and found select with nolock option but, if I'm understanding correctly, this does not stop the lock creation, rather goes around current locks and grabs everything?
Thanks.
EDIT: This table will never be UPDATED, only INSERTS and SELECTS
发布评论
评论（5）
只要您不介意从表中读取脏数据，这对您来说就不成问题。确保正确设置转换隔离级别，并且您的调用代码（如果适用）没有使用隐式事务，这样应该没问题。
微软的事务隔离文档：
http://msdn.microsoft.com/en-us/library/ms173763.aspx
NOLOCK 是一个常见的选项，在我看来，当遇到这样的情况时，NOLOCK 是一个被滥用的选项。尽管它可以帮助您克服高争用情况下的问题，但也可能导致难以追踪错误。尽管这是一个持续争论的问题，但请查看 http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx 获取想法使用此类提示的一些风险。
As long as you don't mind getting dirty reads from your table this shouldn't be a problem for you. Make sure that the translation isolation level is set appropriately and that your calling code (if applicable) isn't using implicit transactions and you should be fine.
Microsoft's Transaction Isolation Docs:
http://msdn.microsoft.com/en-us/library/ms173763.aspx
NOLOCK is a common, and in my opinion, abused option when running into situations like this. Although it can help you overcome problems in high contention situations it can also cause difficult to track down bugs. Although this is something of an ongoing argument check out http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx for an idea of some of the risks with using hints like this.
从表中选择时可以使用 NOLOCK 提示。有一些像这样的副作用（您基本上可以得到脏读。）
NOLOCK 在您添加到的查询中不会发出行锁，并且对其他正在运行的查询发出的锁没有影响。 NOLOCK 确实会发出 Sch-S 锁，即架构稳定性锁，这不会给您带来问题。
You can use the NOLOCK hint when selecting from the table. There are some side effects like this (you can basically get a dirty read.)
NOLOCK issues no row locks in the query you add it to, and has no impact on the locks issued by other running queries. NOLOCK does issue a a Sch-S lock, Schema Stability lock, which isn't going to cause you a problem.
我相信你误会了。 select ... with (nolock) 将不会获取任何锁。也就是说，它不会阻止任何其他写入。
缺点似乎是它将包含未提交的读取，因此结果可能无法保留写入事务回滚。
I believe you have misunderstood. select ... with (nolock) will not acquire any locks. That is to say, it will not block any other writes.
The downside seems to be that it will include uncommitted reads, so the result may not hold it the writing transaction rolls back.
您可以使用 NOLOCK，但我仅建议在您知道“脏数据”可以接受的情况下（例如，您知道数据一旦插入就永远不会被更改或删除的 syslog 数据库）。最好的方法是从未被锁定的数据中进行 SELECT；您能识别出不受插入影响的行吗？例如，如果使用默认为 GETDATE() 的 CreateDate 列插入数据，请确保您的查询从该点之前提取数据。
当然，这完全取决于写入的数据量以及插入语句是否生成行锁、页锁或表锁......
You can use NOLOCK, but I would only recommend that in cases where you know that "dirty data" is acceptable (for example, a syslog database where you know data will never be altered or deleted once it's been inserted). The best way to do it is to SELECT from data that is NOT being locked; can you identify rows that aren't being affected by your insert? For example, if your data is being inserted with a CreateDate column defaulting to GETDATE(), make sure your queries pull data from BEFORE that point.
Of course, it all depends on how much data is being written and whether or not the insert statement is generating row or page or table locks...
这里没有讨论的一种选择是使用复制。如果您复制相关表并在复制的数据库上运行查询，则不会阻止插入/更新。 （在您的情况下，我将使用事务复制 - https://msdn.microsoft。 com/en-us/library/ms151176.aspx）。
One option not discussed here is to use replication. If you replicate the table in question and run your queries on the replicated database, you will not block inserts/updates. (In your case, I would use transactional replication - https://msdn.microsoft.com/en-us/library/ms151176.aspx).