代码之家 › 专栏 › 技术社区 › NoNaMe

在MSSQL server中使用rowlock更新

rowlocking sql-server

NoNaMe · 技术社区 · 5 年前

我试图理解SQL server中的ROWLOCK,以便在锁定记录后更新它。这是我的观察结果,我想得到一个确认,如果ROWLOCK像一个表或页锁之类的东西,或者我没有尝试它正确。ROWLOCK应该只锁定行,而不是表或页。

以下是我尝试的:

我创建了一个简单的表: row_lock_temp_test 有两列和名称没有主键或索引。现在我打开SQL Server,两个不同的客户端,但凭据相同,并尝试执行一组查询,如下所示:

客户1:

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name = 'CC' where id = 2
3: COMMIT

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name= 'CC' where id = 2
3: COMMIT

我在C-1上执行查询1,2,然后转到C-2,执行相同的查询,两个客户端都执行查询,然后我提交事务,一切正常。

然后我添加了RowLock来更新查询,

C-1级

  1: BEGIN TRANSACTION;
  2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
  3: COMMIT

C-2级

1: BEGIN TRANSACTION;
2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
3: COMMIT

现在,我在C-1上执行查询1和查询2,然后转到C-2并尝试执行相同的2个查询,但是查询像预期的那样卡住了,因为行被C-1锁定,所以在事务提交到C-1之前,它应该在队列中。一旦我在C-1上提交了事务,C-2上的查询就执行了,然后我也在C-2上提交了事务。一切都很好。

C-2级

 1: BEGIN TRANSACTION;
 2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 3
 3: COMMIT

我还尝试使用不同组合的xlock、HOLDLOCK和UPDLOCK,但它总是锁定表。有没有可能只锁一排。

“选择并插入”按预期工作。

提前谢谢。

0 回复 | 直到 5 年前

allmhuran 4 年前

锁定提示只是提示。不能“强制”SQL使用特定类型的锁。

select      tl.request_session_id,
            tl.resource_type,
            tl.request_mode,
            tl.resource_description,
            tl.request_status
from        sys.dm_tran_locks   tl
join        sys.partitions      pt  on  pt.hobt_id = tl.resource_associated_entity_id
join        sys.objects         ob  on  ob.object_id = pt.object_id
where       tl.resource_database_id = db_id()
order by    tl.request_session_id

create table t(i int, j int);
insert t values (1, 1), (2, 2);

begin tran;
update t with(rowlock) set j = 2 where i = 1;

打开第二个SSMS窗口,运行以下命令:

begin tran;
update t with(rowlock) set j = 2 where i = 2;

第二次执行将被阻止。为什么?

在第三个窗口中运行锁定查询,注意有两行带有 resource_type 属于 RID ,一个有一个 status 地位 “等等”。我们会去的 摆脱 resource_description 这些行的列。它的价值是一样的。

好的,那么什么是 ? 这取决于天气 资源类型 . 但对于我们的 摆脱

i = 1 . 当它扫描时,每行上都会有一个更新锁。为什么每行都需要一个更新锁?嗯,可以说,这不是“做”更新。这需要一个专用锁。更新锁通常是用来防止死锁的。

U 锁定每一行。当然,它在插槽0中找到了要立即更新的行,并进行了一次修改 X 十

然后我们开始第二个查询,它还必须扫描所有的行以找到它想要的行。一开始是想抢占先机 U 十 我们第一个查询的锁正在阻止它。

因此,您可以看到,即使使用行锁定,您的第二个查询仍然被阻塞。

i ,获取其独占锁,并等待我们提交。

查询2出现,获取插槽0上的更新锁,查看它想要的值,获取它的独占锁,更新值, ,因为这可能也有它想要的价值。

您还将在下一个“级别”上看到“意向锁”,即页面。该操作让引擎的其余部分知道它可能希望在将来某个时候将锁升级到页面级别。但这不是一个因素。页面锁定不是问题的根源。

这种情况下的解决方案?在列上添加索引 . 在这种情况下,这可能是主键。然后可以按任意顺序进行更新。在这种情况下请求行锁定没有区别,因为SQL不知道有多少行与谓词匹配。但即使你试着力行锁在某些情况下,即使使用主键或适当的索引,SQL仍然可以选择升级锁类型,因为锁定整个页面或整个表比锁定和解锁单个行更有效。