Avatar billede mrbonus Novice
26. november 2021 - 11:24 Der er 3 kommentarer

Hvorfor får jeg COLUMNLOCK ved en NONCLUSTERED INDEX

Jeg prøver at finde ud af, om jeg gør noget forkert, eller det er med vilje:
Jeg finder det mærkeligt, at når jeg har en NONUNIQUE INDEX på SessionId, og opdaterer på "SessionId = 'Noget'", så kan jeg ikke lave en SELECT med "SessionId = 'Noget andet'", før min UPDATE er færdig, så det føles som om jeg ikke kun laver en ROWLOCK, men også laver en COLUMNLOCK på SessionId-kolonnen.
Især mærkeligt, fordi jeg ville forvente, at jeg så ville have samme problem uden INDEX på SessionId, men det er ikke tilfældet.

Så er der en anden måde at lave en NONCLUSTERED INDEX på, så det ikke giver mig en oplevelse af en COLUMNLOCK, eller er dette bare by design, og jeg bør undlade at lave en INDEX?

- Yderligere oplysninger nedenfor for at forklare spørgsmålet:

1. DROP INDEX [IX_tal] ON [dbo].[tblLog]

2. CREATE NONCLUSTERED INDEX [IX_tal] ON [dbo].[tblLog] ([SessionID] 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)

når jeg laver DROP INDEX, så bliver jeg ikke låst ude af min SELECT under UPDATE, nedenstående skærmbillede beskriver min oplevelse af problemet.
(Jeg prøvede også med en UNIQEU INDEX, som fungerer perfekt, men når dataene ikke er unikke i den kolonne, så kan jeg ikke bruge den tilgang)

https://imgur.com/a/iZk8KO3
Avatar billede arne_v Ekspert
26. november 2021 - 15:56 #1
Umiddelbart ville jeg mene at du brude kunne lave en:

UPDATE ... WHERE sessionid = ?

uden at låse SELECT ... WHERE sessionid = ? men at:

UPDATE ... SET sessionid = ? WHERE ...

vil låse SELECT ... WHERE sessionid = ? fordi man ikke kan søge i et index der er under opdatering.
Avatar billede mrbonus Novice
06. december 2021 - 10:51 #2
Så du siger at det burde virke som jeg forventer det, hmm,
Jeg har lavet et replikerings script, for at vise at det ikke virker som jeg forventer det

1. Lav tabel og indsæt data.
2. Opdater en række (men kun en anden kolonne), og hold den i "ROWLOCK" da transaktionen ikke er commited i dette vindue
3. Forspørg på en anden række i et andet vindue og se at den er låst igennem samme kolonne, selvom der ikke opdateres i kolonnen

1:-------------------------------------------------
USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblLog](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SessionID] [varchar](50) NULL,
    [LogoutTime] [datetime] NULL,
CONSTRAINT [PK_tblLog] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE NONCLUSTERED INDEX [IX_tal] ON [dbo].[tblLog] ([SessionID] 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)
GO

SET IDENTITY_INSERT [dbo].[tblLog] ON

INSERT INTO [dbo].[tblLog]  ([ID],[SessionID],[LogoutTime]) VALUES (13178619, 'mhw51ilwiucbaqefkwx0mvus', NULL)
INSERT INTO [dbo].[tblLog]  ([ID],[SessionID],[LogoutTime]) VALUES (13178620, 'pe4sgfiepyuokacpu3t2u1a0', NULL)

SET IDENTITY_INSERT [dbo].[tblLog] OFF
GO
----------------------------------------------------

2:-------------------------------------------------
USE [master]
GO

BEGIN TRANSACTION T1

UPDATE [dbo].[tblLog] SET [Logouttime] = '2021-11-24 08:24:31' WHERE [sessionid] = 'pe4sgfiepyuokacpu3t2u1a0'


--ROLLBACK TRANSACTION T1
----------------------------------------------------

3:-------------------------------------------------
USE [master]
GO
SELECT * FROM [dbo].[tblLog] WHERE [sessionid] = 'mhw51ilwiucbaqefkwx0mvus'
----------------------------------------------------
Avatar billede arne_v Ekspert
06. december 2021 - 16:24 #3
Jeg kan som sagt godt forstå at index låses hvis UPDATE opdaterer felt med index.

Men jeg kan heller ikke forstå hvorfor index skal låses hvis UPDATE bare bruger index felt i en WHERE. Det burde ikke vær enoget problem at have to der samtidigt læser fra et index.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester