将数据插入SQL Server是否会锁定整个表？ [英] Does inserting data into SQL Server lock the whole table?
问题描述
我正在使用实体框架，并且我正在将包含blob字段的记录插入到我们的数据库中。 Blob字段最多可以包含5 MB的数据。
I am using Entity Framework, and I am inserting records into our database which include a blob field. The blob field can be up to 5 MB of data.
在向该表中插入记录时，它会锁定整个表吗？
When inserting a record into this table, does it lock the whole table?
因此，如果您要查询表中的任何数据，它将一直阻塞到插入完成为止（我知道有解决此问题的方法，但是默认情况下是在讲话）？
So if you are querying any data from the table, will it block until the insert is done (I realise there are ways around this, but I am talking by default)?
需要多长时间才能导致死锁？那时间将取决于服务器上的负载量，例如如果负载不大，会导致死锁吗？
How long will it take before it causes a deadlock? Will that time depend on how much load is on the server, e.g. if there is not much load, will it take longer to cause a deadlock?
有没有一种方法可以监视并查看在任何特定时间被锁定的内容？
Is there a way to monitor and see what is locked at any particular time?
如果每个线程都在单个表上进行查询，那么是否会发生阻塞？那么，是否只有当您的查询具有联接并且作用于多个表时才发生死锁？
If each thread is doing queries on single tables, is there then a case where blocking can occur? So isn't it the case that a deadlock can only occur if you have a query which has a join and is acting on multiple tables?
这是考虑到我的大部分代码只是一堆选择语句，而不是一堆长期运行的事务或类似的东西。
This is taking into account that most of my code is just a bunch of select statements, not heaps of long running transactions or anything like that.
推荐答案
，您在这里有很多问题，呵呵。以下是一些答案：
Holy cow, you've got a lot of questions in here, heh. Here's a few answers:
向该表中插入记录时，它会锁定整个表吗？
不是默认情况下，但是如果使用TABLOCK提示或正在执行某些类型的批量加载操作，则可以。
Not by default, but if you use the TABLOCK hint or if you're doing certain kinds of bulk load operations, then yes.
因此，如果您要查询表中的任何数据
这会变得有点棘手，它会一直阻塞到插入完成为止（我知道可以解决这个问题，但是我默认是在讲话）？如果有人试图从您已锁定的表的页面中选择数据，那么可以，您将阻止他们。您可以通过选择语句上的NOLOCK提示或使用读取已提交的快照隔离来解决此问题。有关隔离级别如何工作的起点，请查看 Kendra Little的隔离级别海报。
This one gets a little trickier. If someone's trying to select data from a page in the table that you've got locked, then yes, you'll block 'em. You can work around that with things like the NOLOCK hint on a select statement or by using Read Committed Snapshot Isolation. For a starting point on how isolation levels work, check out Kendra Little's isolation levels poster.
需要多长时间才能导致死锁？那时间将取决于服务器上的负载量，例如如果负载不大，会导致死锁吗？
死锁不是基于时间的，而是基于依赖关系的。假设我们遇到这种情况：
Deadlocks aren't based on time - they're based on dependencies. Say we've got this situation:
- 查询A持有一堆锁，要完成他的查询，他需要被查询B锁定的东西
- 查询B也持有一堆锁，要完成他的查询，他需要被查询A锁定的东西
任何一个查询都不能向前移动（认为墨西哥僵持），因此SQL Server称其为平局，在后面射击某人的查询，释放他的锁，然后让其他查询继续进行。 SQL Server会根据受害者选择哪个受害者来进行回滚，而后者的成本更低。如果想花哨的话，可以在特定查询上使用SET DEADLOCK_PRIORITY LOW在目标背面绘制目标，SQL Server会首先对其进行射击。
Neither query can move forward (think Mexican standoff) so SQL Server calls it a draw, shoots somebody's query in the back, releases his locks, and lets the other query keep going. SQL Server picks the victim based on which one will be less expensive to roll back. If you want to get fancy, you can use SET DEADLOCK_PRIORITY LOW on particular queries to paint targets on their back, and SQL Server will shoot them first.
有没有办法监视并查看在特定时间锁定了什么？
绝对-您可以像sys.dm_tran_locks一样查询动态管理视图（DMV），但是最简单的方法是使用 Adam Machanic的免费sp_WhoIsActive存储过程。可以这样称呼它，它是sp_who的真正替代品：
Absolutely - there's Dynamic Management Views (DMVs) you can query like sys.dm_tran_locks, but the easiest way is to use Adam Machanic's free sp_WhoIsActive stored proc. It's a really slick replacement for sp_who that you can call like this:
sp_WhoIsActive @get_locks = 1
对于每个正在运行的查询，您将获得一个描述其持有的所有锁的XML。还有一个阻止列，因此您可以查看谁阻止了谁。要解释所持有的锁，您需要检查在线图书中对锁类型的描述。
For each running query, you'll get a little XML that describes all of the locks it holds. There's also a Blocking column, so you can see who's blocking who. To interpret the locks being held, you'll want to check the Books Online descriptions of lock types.
如果每个线程都在单个表上进行查询，那么是否会发生阻塞？难道不是只有当您的查询具有联接并且作用于多个表时才会发生死锁吗？
信不信由你，< a href = http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproduction-deadlocks-involving-only-one-table.aspx rel = nofollow noreferrer>单个查询实际上可以死锁本身，是的，查询只能在一个表上死锁。要了解有关死锁的更多信息，请查看 Jeremiah Peschka撰写的《死锁的困难。
Believe it or not, a single query can actually deadlock itself, and yes, queries can deadlock on just one table. To learn even more about deadlocks, check out The Difficulty with Deadlocks by Jeremiah Peschka.
这篇关于将数据插入SQL Server是否会锁定整个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
