May 4, 2009 at 1:42 pm
Hello. I'm hoping someone out there can help.
I have the following table:
CREATE TABLE [dbo].[TableName](
[tableId] [bigint] NOT NULL,
[imageData] [image] NOT NULL,
[date_time] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_tableId] PRIMARY KEY CLUSTERED
(
[tableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
When I issue:
BEGIN TRANSACTION
DELETE TableName
where tableId=9805459
I get the following locks:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51 16 0 0 DB S GRANT
51 16 631653298 1 KEY (db0786df115f) X GRANT
51 16 631653298 1 RID 1:46488:40 X GRANT
51 16 631653298 1 PAG 1:46488 IX GRANT
51 16 631653298 1 PAG 1:2096602 IX GRANT
51 16 631653298 1 RID 1:46488:41 X GRANT
51 16 631653298 0 TAB IX GRANT
I did not expect to see this result. I would have expected to see the Exclusive KEY lock and maybe one PAG lock. I was surprised to see TWO RID locks and TWO page locks.
Does anyone know why there would be two IX PAG locks and two X RID locks? Further why would there be a RID lock at all? I thought those were only used when you had an index based on a heap.
Thanks in advance!
May 4, 2009 at 3:31 pm
I think thats because of the IMAGE datatype column which you are using in ur table.
May 4, 2009 at 3:53 pm
You're right! Once I dropped the image column and ran my one row delete I got:
1 X KEY
1 IX TAB
1 IX PAG
Thanks for the idea.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply