当我更新/插入一行时，它应该锁定整个表吗? [英] When I update/insert a single row should it lock the entire table?
问题描述
我有两个长时间运行的查询，它们都在事务上并且访问同一张表，但是这些表中的行完全分开.这些查询还会执行一些更新，并根据这些查询进行插入.
I have two long running queries that are both on transactions and access the same table but completely separate rows in those tables. These queries also perform some update and inserts based on those queries.
当它们同时运行时，它们会遇到某种类型的锁，这会阻止任务完成，并在更新其中一行时锁定.我在正在读取的行上使用排他行锁，并且在进程中显示的锁是lck_m_ix锁.
It appears that when these run concurrently that they encounter a lock of some kind and it’s preventing the task from finishing and locks up when it goes to update one of the rows. I’m using an exclusive row lock on the rows being read and the lock that shows up on the process is a lck_m_ix lock.
两个问题:
- 当我更新/插入一行时，它会锁定整个表吗?
- 如何解决此类问题?
推荐答案
通常不，但这取决于(对于SQL Server而言，是最常用的答案！)
Typically no, but it depends (most often used answer for SQL Server!)
SQL Server将必须以某种方式锁定事务中涉及的数据.在执行修改时，它必须将数据锁定在表本身中，并将数据锁定在任何受影响的索引中.为了提高并发性，服务器可能决定使用几种粒度"锁定，以便允许多个进程运行:行锁，页锁和表锁是常见的(还有更多).哪种锁定级别在起作用取决于服务器如何决定执行给定的更新.事情变得复杂，还有共享锁，排他锁和意图排他锁之类的锁，它们控制着是否可以读取和/或修改锁定的对象.
SQL Server will have to lock the data involved in a transaction in some way. It has to lock the data in the table itself, and the data any affected indexes, while you perform a modification. In order to improve concurrency, there are several "granularities" of locking that the server might decide to use, in order to allow multiple processes to run: row locks, page locks, and table locks are common (there are more). Which scale of locking is in play depends on how the server decides to execute a given update. Complicating things, there are also classifications of locks like shared, exclusive, and intent exclusive, that control whether the locked object can be read and/or modified.
根据我的经验，SQL Server主要使用页锁来更改表的一小部分，并且如果某个表的较大部分(受统计信息影响)似乎会受到影响，则超过某个阈值后，该阈值将自动升级为表锁.更新或删除.这个想法是，锁定一个表(一个锁)要比获取和管理成千上万的单个行或页锁来进行大的更新要快得多.
It's been my experience that SQL Server mainly uses page locks for changes to small portions of tables, and past some threshold will automatically escalate to a table lock, if a larger portion of a table seems (from stats) to be affected by an update or delete. The idea is that it is faster to lock a table (one lock) than obtaining and managing thousands of individual row or page locks for a big update.
要查看特定情况下发生的情况，您需要查看查询逻辑，并且在您的内容运行时，检查sys.dm_tran_locks，sys.dm_os_waiting_tasks或其他DMV中的锁定/阻塞条件.您可能想发现每个流程中的哪个步骤究竟锁定了什么，以找出一个原因阻止了另一个.
To see what is happening in your specific case, you'd need to look at the query logic and, while your stuff is running, examine the locking/blocking conditions in sys.dm_tran_locks, sys.dm_os_waiting_tasks or other DMV's. You would want to discover what exactly is getting locked by what step in each of your processes, to discover why one is blocking the other.
这篇关于当我更新/插入一行时，它应该锁定整个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
