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?