2

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')
  • Is this a small table? How many rows does this table hold? – Ionic Jun 16 '15 at 13:44
  • How do you obtaion the "locking for the code"? – mustaccio Jun 16 '15 at 13:45
  • The table only has two records (example table). Locking is gained using SQL Profiler. If I change the query to use TOP 1 instead of MIN I get the shared lock as expected. However I just want to understand why IS locks are being taken without resulting in a shared lock, is it because of the range scan? – hpk89 Jun 16 '15 at 14:00
  • TOP 1 .. GROUP BY ... ORDER BY vs. MIN ... GROUP BY give a different semantically query. I recommend you post exact table definition along with test data you use. – Remus Rusanu Jun 16 '15 at 14:37
  • 3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.