I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations.
I have the following code:
SELECT MIN(ID), TextData FROM PKKEYTESTER GROUP BY TextData ORDER BY TextData DESC
The locking for the code is as follows:
IS Lock:Acquired IS Lock:Acquired IS Lock:Released IS Lock:Released
At no point does it get a S lock.
So why does it intend to get a shared lock but actually doesn't?
SQL To create and insert base data into table:
CREATE TABLE [dbo].[PKKEYTESTER]( [ID] [int] NOT NULL, [Textdata] [nvarchar](300) NULL, 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 INSERT INTO PKKEYTESTER VALUES (1, 'A') INSERT INTO PKKEYTESTER VALUES (2, 'B')