为什么我的完整表被锁定而不是行？

Question

我运行了这个 SQL：

create table temp
(
     id int,
    name varchar(10)
)
insert into temp values(1,'a');

然后我运行，

select 1 from temp  where id = 1 

一切正常。

然后我运行了一个未提交的插入操作，

SET NOCOUNT ON;
    DECLARE @TranCount INT;
    SET @TranCount = @@TRANCOUNT;



        IF @TranCount = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION Insertorupdatedevicecatalog;

insert into temp values(2,'b')

然后我运行,

select 1 from temp  where id = 1 

但这次没有返回任何内容。为什么我的整个表格被锁定了，而不仅仅是第二行？

- Imran Qadir Baksh - Baloch

---

表是堆（没有像通常一样的唯一CI）导致了意外的锁定。通过在id上创建唯一CI，这个问题将会消失。 - usr

我在我的本地系统上运行了代码，但无法复现。 第二个“SELECT”返回的是1，正如预期的那样。 - SchmitzIT

@SchmitzIT，我正在使用一个新的数据库、新的安装和一个全新的表进行测试。 - Imran Qadir Baksh - Baloch

@usr，是的，你说得对。如果我在ID上添加CI，它就有效。但是从SQL-Server锁定整个表格是荒谬的。 - Imran Qadir Baksh - Baloch

@user960567 - 我也试过了。表格创建脚本在你的原始帖子中。我真的无法使用你提供的代码重现这个问题。 - SchmitzIT

Answer 1

SQL Server并未锁定整个表。我可以看到写入事务锁定了单个行标识。

读者必须扫描整张表，因为没有索引。

这意味着读者被插入行上的X锁阻塞。基本上，读者等待另一个事务决定是否要实际提交此行或回滚它。

会话51已插入ID 2。会话54是被阻塞的选择操作。这里没有任何页面或表锁（除了意向锁，在这里无关紧要）。

该表是堆（没有像通常一样的唯一聚集索引），导致出现意外锁定。通过在id上创建唯一聚集索引，可以解决此问题。

- usr

---

你能写出返回上述行的查询吗？ - Imran Qadir Baksh - Baloch

如果您编写属于Session_id的SQL语句，那就太好了。 - Imran Qadir Baksh - Baloch

@user960567 这是一个更大的脚本集合的一部分。它不是一个孤立的查询，它依赖于许多其他实用程序的页面和页面。尝试从sys.dm_tran_locks (http://technet.microsoft.com/en-us/library/ms190345.aspx)中进行选择。 - usr

好的，谢谢。但是你是怎么得到上面那些行的？顺便说一句，回答很棒。 - Imran Qadir Baksh - Baloch

你是在说锁定报告吗？那是我的自定义查询，太大了无法发布。 - usr

是的。但这会帮助我和其他人进行调试。 - Imran Qadir Baksh - Baloch

Answer 2

我的猜测是，你的表可能并没有被锁定（或者由于你只有很少的行，它可能会被锁定）。我认为正在发生的事情是，为了知道在哪里插入新行，SQL Server必须锁定（写锁定，防止读取）值周围的一系列行。由于你的表中只有很少的行，所以看起来像是整个表都被锁定了。当你添加更多行时，插入单个行时不应再出现这种行为。

顺便说一下，你的表应该有一个主键和/或聚集索引。这将有助于未来添加更多行。否则，你将需要进行扫描，这肯定会延长更新（或者插入）所需的时间。

- Randy Minder

---

我正在使用新数据库、新安装和全新的表进行测试。你也可以自己测试一下。 - Imran Qadir Baksh - Baloch

我已经测试过这个更新。它与UPDATE具有相同的结果。 - Imran Qadir Baksh - Baloch

是的，更新（Update）在表格中只有这么少的行时会以大致相同的方式工作。当然，插入或更新应该只需要几毫秒，因此锁定时间不会很长，用户甚至都不会注意到。 - Randy Minder