WITH (UPDLOCK, HOLDLOCK) and NON-unique index - does it lock a whole table?

Viewed 138

I have a table below with NON-unique index on the regionId column:

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

I am executing the following query:

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;

And in the meanwhile the following query:

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

The second query waits till the first query ends. If I change the non-unique index of regionId column to the unique index of regionId column then the second query executes immediately. :O Why??

Does it mean the non-unique index of the regionId column together with WITH (UPDLOCK, HOLDLOCK) locks a whole table?

0 Answers
Related