sql - WITH (UPDLOCK, HOLDLOCK) 和非唯一索引 - 它会锁定整个表吗？

Question

我在 regionId 列上有一个带有非唯一索引的表：

CREATE TABLE [dbo].[Localizations]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [regionId] [int] NOT NULL,

    CONSTRAINT [PK_Localizations] 
        PRIMARY KEY CLUSTERED ([id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [RegionId_index] 
ON [dbo].[Localizations] ([regionId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我正在执行以下查询：

BEGIN TRAN;
    IF NOT EXISTS(SELECT 1 
                  FROM [dbo].[Localizations] WITH (UPDLOCK, HOLDLOCK) 
                  WHERE regionId = 1)
    BEGIN
        WAITFOR DELAY '00:00:10';

        INSERT INTO [dbo].[Localizations] 
        VALUES ('aa', 1);
    END;
COMMIT;

同时以下查询：

UPDATE [dbo].[Localizations]
SET name = 'bb'
WHERE regionid = 2

第二个查询一直等到第一个查询结束。如果我将列的非唯一索引更改为regionId列的唯一索引，regionId则第二个查询将立即执行。:O 为什么？

这是否意味着列的非唯一索引regionId以及WITH (UPDLOCK, HOLDLOCK)锁定整个表？

标签： sqlsql-server