应该对 SQL Server 的锁定架构进行哪些更改才能使其对开发人员更加友好？

Question

我最近遇到了一个相当令人沮丧的情况，即当执行这样的语句 select * from table with (rowlock updlock) where key=value 时，SQL Server 拒绝仅针对主键发出锁它。现在不要误会我的意思，它确实锁定了行，但它更进一步并锁定了表。

我已经阅读了有关 SQL 锁升级的内容，并且研究了在锁提示中使用特定索引，但是您会发现，当表中有大量索引且包含数百万条记录并且需要并发更新时，这并不实用。发生在这些记录上。对于小表和特定查询，可以获得所需的行为，但是当表的宽度很大（许多列）并且有许多进程使用数据时，此方法就会崩溃并可能成为真正的争论点。

我希望看到添加的是一个新的锁定提示，就像PKLock（代表主键锁定）一样，它会针对行的主键发出锁定每当使用索引、表扫描或其他方法来获取行时，它都会检查此锁并遵守它，而不是锁定整个表。

由于不需要发出这样的表锁，这将大大增加针对数据库并行执行代码的能力。

请权衡这个想法，并指出它可能存在的任何缺陷、可以改进的方法或应该添加的其他元素来解决我的困境。

编辑

@Remus

如果我执行此查询

begin transaction
select lockname from locks  where lockname='A'
update Locks Set locked=1 where lockname='A'

，然后执行此查询：

begin transaction
select lockname from locks  where lockname='A'

在提交事务之前，两个示例中都会返回 A 行。这是更新后面的读取，而不是阻塞。

成功的解决方案应该在不指定要使用的索引的情况下执行以下操作：

1. 使用查询 1：读取并锁定 A，更新 A
2. 使用查询 2：读取并锁定 B，更新 B，提交查询 2
3. 使用查询 2：读取 B 并被阻止直到锁定A 上已发布
4. 使用查询 1：提交查询 1
5. 使用查询 2：读取并锁定 A，更新 A，提交查询 2

Answer 1

您之前曾问过这个问题并得到了答案：修复您的架构和代码。在那篇文章中，锁冲突是 IX 锁，意向锁上的冲突表明高粒度锁，而高粒度锁又表明表扫描。您不需要锁定提示，您只需要一个索引和适当的查询。以您的其他问题为例， 为什么行级锁定在 SQL Server 中似乎无法正常工作？，答案很简单：锁表需要通过 LockName 上的聚集索引来组织：

CREATE TABLE [dbo].[Locks]( 
    [LockName] [varchar](50) NOT NULL, 
    [Locked] [bit] NOT NULL, 
    CONSTRAINT [PK_Locks] PRIMARY KEY CLUSTERED  ([LockName]));
GO    

insert into Locks (LockName, Locked) values ('A', 0);
insert into Locks (LockName, Locked) values ('B', 0);
GO

在一个会话上执行此操作：

begin transaction
update Locks 
  set Locked=1 
  output inserted.*
  where LockName = 'A';

在另一个会话上执行此操作：

begin transaction
update Locks 
  set Locked=1 
  output inserted.*
  where LockName = 'B';

没有更新冲突，没有阻塞，不需要（错误的）提示，什么都没有。只是很好的正确模式和查询设计。

附带说明一下，您在此处描述的锁已经存在，称为钥匙锁。它们是 SQL Server 运行的默认隐式模式。您到底如何想象 SQL Server 能够发布每秒 16000 tpc 事务的 TPC-C 基准数据？您拥有服务器所需的所有并行能力，您只需阅读一两本书即可了解如何使用它。关于该主题的文献有很多，您可以从 事务处理：概念和技术< /a>.

已更新

begin transaction 
select lockname from locks  where lockname='A' 
update Locks Set locked=1 where lockname='A'

无论您尝试多少/不同的锁定提示，这都永远不会起作用。这就是您使用输出语法进行更新的原因：

begin transaction 
update Locks 
 Set locked=1 
 output inserted.*
 where lockname='A'

这可确保您首先更新，然后返回已更新的内容。这种技术在数据库中相当常见，正是为了满足您所寻求的语义：资源获取。事实上，该技术是资源获取典型子项的基石：队列处理。请参阅OUTPUT 子句中的队列段落。在队列中，您有一个要处理的资源表，每个线程获取一个资源，锁定它并开始处理：

create table Resources (
   id int identity(1,1) not null,
   enqueue_time datetime not null default getutcdate(),
   is_processing bit not null default 0,
   payload xml);

create clustered index cdxResources on Resources 
  (is_processing, enqueue_time);
go   

-- enqueue:
insert into Resources (payload) values ('<do>This</do>');
insert into Resources (payload) values ('<do>That</do>');
insert into Resources (payload) values ('<do>Something</do>');
insert into Resources (payload) values ('<do>Anything</do>');

现在从单独的会话中运行此命令：

--dequeue
begin transaction;
with cte as (
  select top(1) *
  from Resources with(readpast)
  where is_processing = 0
  order by enqueue_time)
update cte
  set is_processing = 1
  output inserted.*;   

您将看到每个会话获取它自己的资源，锁定它并跳过由其他人。碰巧我在生产中有一个完全像这样运行的系统，表中有超过 500 万个资源（它们是 Web 服务支付处理请求），并且每秒从 100 个并发处理器中出列和处理大约 50 个资源（大约需要 2 秒）。每次调用进程）。在一个垃圾硬件上。所以这绝对是可能的。

Answer 2

也许您根本不想指定任何锁定？

http: //blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx