August 5, 2011 at 3:24 pm
Hi,
Here is a blocked-process-report from our 2005 prod db. It is looks like the PK of one table in the blocked process is locked by four delete statement. But there is NOT any FK between those four tables in blocking process with the table in SELECT statement in blocked process.
Anybody can provide any idea to explain the report? Thanks.
<blocked-process-report monitorLoop="28225">
<blocked-process>
<process id="process392c2e718" taskpriority="0" logused="0"
waitresource="KEY: 5:72057594781040640 (4600b05b4964)"
waittime="8171" ownerId="69708926" transactionname="SELECT"
lasttranstarted="2011-07-18T11:59:55.313" XDES="0x65c0b9708" lockMode="S" schedulerid="4" kpid="7544"
status="suspended"
spid="321" sbid="0" ecid="0" priority="0" transcount="0"
lastbatchstarted="2011-07-18T11:59:55.313"
lastbatchcompleted="2011-07-18T11:59:55.313" clientapp=".Net SqlClient Data Provider" hostname="SACMCSAPP002" hostpid="8372"
loginname="DELTADS\DDC_SVC_FRMWKS"
isolationlevel="read committed (2)" xactid="69708926" currentdb="5" lockTimeout="4294967295" clientoption1="671088672"
clientoption2="128056"> <executionStack> <frame line="1"
sqlhandle="0x020000009f33c607aae2318a463ae2a385b51fa1b83ad41e"/> </executionStack> <inputbuf>
SELECT FormWorks_Host_joined_on_HostID.HostName Host,
FormWorks_WorkerType_joined_on_WorkerTypeID.WorkerType WorkerType,
FormWorks_WorkerInstance.InstanceNumber InstanceNumber,
FormWorks_WorkerInstance.UserName UserName,
FormWorks_WorkerInstance.Connected Connected,
FormWorks_WorkerInstance.LoggedIn LoggedIn,
FormWorks_WorkerInstance.Paused Paused,
FormWorks_WorkerInstance.Draining Draining,
FormWorks_WorkerInstance.AdminDraining AdminDraining,
FormWorks_WorkerInstance.RunState RunState,
FormWorks_WorkerInstance.LWMState LWMState,
FormWorks_WorkerInstance.RecallOverride RecallOverride,
FormWorks_WorkerInstance.WorkRecordCapacity WorkRecordCapacity,
FormWorks_WorkerInstance.WorkRecordsPendingAssign WorkRecordsPendingAssign,
FormWorks_WorkerInstance.WorkRecordsUpdating WorkRecordsUpdating,
FormWorks_WorkerInstance.WorkRecordsOutstanding WorkRecordsOutstanding,
FormWorks_WorkerInstance.WorkRecordsPendingAssignAck WorkRecordsPe </inputbuf> </process>
</blocked-process>
<blocking-process>
<process status="running" spid="109" sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2011-07-18T12:00:13.850"
lastbatchcompleted="2011-07-18T12:00:13.850"
clientapp=".Net SqlClient Data Provider"
hostname="SACMCSAPP001" hostpid="6556" loginname="DELTADS\DDC_SVC_FRMWKS"
isolationlevel="read committed (2)" xactid="69719130" currentdb="5"
lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack> <frame line="6" stmtstart="428" stmtend="604"
sqlhandle="0x02000000613d7139a9a3ffeffb94709298e2fa0d0735ae65"/>
</executionStack> <inputbuf> (@ItemID uniqueidentifier)
DELETE FROM [FormWorks_UncollatedPage] WITH (ROWLOCK) WHERE [ItemId]=@ItemID;
DELETE FROM [FormWorks_Page] WITH (ROWLOCK) WHERE [ItemId]=@ItemID;
DELETE FROM [FormWorks_Document] WITH (ROWLOCK) WHERE [ItemId] = @ItemID;
DELETE FROM [FormWorks_WorkRecordItem] WITH (ROWLOCK) WHERE [ItemId] = @ItemID;
</inputbuf> </process>
</blocking-process>
</blocked-process-report>
August 5, 2011 at 5:37 pm
What generated this report?
It looks like whatever process it is, truncated the inputbuffer node of the blockedprocess node. You can see that the Select statement got cut off.
I'd recommend finding the proc that has that Select statement and check the entire statement to see if it is related to those queries that are deleting.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 8, 2011 at 9:29 am
Thanks, Jason.
I got the complete sql process from the trace file. There is NO any direct FK related between the table in the blocked prcoess and blocking process. Anybody has any idea? Thanks.
SELECT FormWorks_Host_joined_on_HostID.HostName Host,
FormWorks_WorkerType_joined_on_WorkerTypeID.WorkerType WorkerType,
FormWorks_WorkerInstance.InstanceNumber InstanceNumber,
FormWorks_WorkerInstance.UserName UserName,
FormWorks_WorkerInstance.Connected Connected,
FormWorks_WorkerInstance.LoggedIn LoggedIn,
FormWorks_WorkerInstance.Paused Paused,
FormWorks_WorkerInstance.Draining Draining,
FormWorks_WorkerInstance.AdminDraining AdminDraining,
FormWorks_WorkerInstance.RunState RunState,
FormWorks_WorkerInstance.LWMState LWMState,
FormWorks_WorkerInstance.RecallOverride RecallOverride,
FormWorks_WorkerInstance.WorkRecordCapacity WorkRecordCapacity,
FormWorks_WorkerInstance.WorkRecordsPendingAssign WorkRecordsPendingAssign,
FormWorks_WorkerInstance.WorkRecordsUpdating WorkRecordsUpdating,
FormWorks_WorkerInstance.WorkRecordsOutstanding WorkRecordsOutstanding,
FormWorks_WorkerInstance.WorkRecordsPendingAssignAck WorkRecordsPendingAssignAck,
FormWorks_WorkerInstance.WorkRecordsPendingCAA WorkRecordsPendingCAA
FROM FormWorks_WorkerInstance
LEFT OUTER JOIN FormWorks_Host FormWorks_Host_joined_on_HostID
ON (FormWorks_Host_joined_on_HostID.HostID = FormWorks_WorkerInstance.HostID)
LEFT OUTER JOIN FormWorks_WorkerType FormWorks_WorkerType_joined_on_WorkerTypeID
ON (FormWorks_WorkerType_joined_on_WorkerTypeID.WorkerTypeID = FormWorks_WorkerInstance.WorkerTypeID)
August 8, 2011 at 10:52 am
How do you have FKs defined on these tables?
DELETE FROM [FormWorks_UncollatedPage] WITH (ROWLOCK) WHERE [ItemId]=@ItemID;
DELETE FROM [FormWorks_Page] WITH (ROWLOCK) WHERE [ItemId]=@ItemID;
DELETE FROM [FormWorks_Document] WITH (ROWLOCK) WHERE [ItemId] = @ItemID;
DELETE FROM [FormWorks_WorkRecordItem] WITH (ROWLOCK) WHERE [ItemId] = @ItemID
Are they set for cascade delete? Do they reference another table that references the tables in the blocked SQL?
Could you post table definitions for each of the tables involved in this issue (blocked process as well as blockers)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 8, 2011 at 11:19 am
Jason,
Thanks for your time. Here is more info.
Are they set for cascade delete? NO
Do they reference another table that references the tables in the blocked SQL? YES. I list this table's definition at the end. Thanks.
--- 4 tables in blocking process
CREATE TABLE [dbo].[FormWorks_UncollatedPage](
[ItemID] [uniqueidentifier] NOT NULL,
[PageIndex] [int] NULL,
CONSTRAINT [PK_FormWorks_UncollatedPage_ItemID] PRIMARY KEY NONCLUSTERED
([ItemID] 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
ALTER TABLE [dbo].[FormWorks_UncollatedPage] WITH CHECK ADD CONSTRAINT [FK_FormWorks_UncollatedPage_ItemID] FOREIGN KEY([ItemID])
REFERENCES [dbo].[FormWorks_WorkRecordItem] ([ItemID])
GO
ALTER TABLE [dbo].[FormWorks_UncollatedPage] CHECK CONSTRAINT [FK_FormWorks_UncollatedPage_ItemID]
GO
CREATE TABLE [dbo].[FormWorks_Page](
[ItemID] [uniqueidentifier] NOT NULL,
[ParentDocumentID] [uniqueidentifier] NULL,
[PageIndex] [int] NULL,
CONSTRAINT [PK_FormWorks_Page_ItemID] PRIMARY KEY NONCLUSTERED
([ItemID] 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
ALTER TABLE [dbo].[FormWorks_Page] WITH CHECK ADD CONSTRAINT [FK_FormWorks_Page_ItemID] FOREIGN KEY([ItemID])
REFERENCES [dbo].[FormWorks_WorkRecordItem] ([ItemID])
GO
ALTER TABLE [dbo].[FormWorks_Page] CHECK CONSTRAINT [FK_FormWorks_Page_ItemID]
GO
ALTER TABLE [dbo].[FormWorks_Page] WITH CHECK ADD CONSTRAINT [FK_FormWorks_Page_ParentDocumentID] FOREIGN KEY([ParentDocumentID])
REFERENCES [dbo].[FormWorks_Document] ([ItemID])
GO
ALTER TABLE [dbo].[FormWorks_Page] CHECK CONSTRAINT [FK_FormWorks_Page_ParentDocumentID]
GO
CREATE TABLE [dbo].[FormWorks_Document](
[ItemID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_FormWorks_Document_ItemID] PRIMARY KEY NONCLUSTERED
([ItemID] 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
ALTER TABLE [dbo].[FormWorks_Document] WITH CHECK ADD CONSTRAINT [FK_FormWorks_Document_ItemID] FOREIGN KEY([ItemID])
REFERENCES [dbo].[FormWorks_WorkRecordItem] ([ItemID])
GO
ALTER TABLE [dbo].[FormWorks_Document] CHECK CONSTRAINT [FK_FormWorks_Document_ItemID]
GO
CREATE TABLE [dbo].[FormWorks_WorkRecordItem](
[ItemID] [uniqueidentifier] NOT NULL,
[WRID] [int] NULL,
[ItemIndex] [int] NULL,
[ItemType] [int] NULL,
[BatchID] [varchar](200) NULL,
[WFWRErrorString] [varchar](200) NULL,
[DocID] [varchar](200) NULL,
[PageID] [varchar](200) NULL,
[DCN] [varchar](200) NULL,
[BatchNum] [varchar](200) NULL,
[JDay] [varchar](200) NULL,
[Scan_HostName] [varchar](200) NULL,
[RWT_UniqueID] [varchar](200) NULL,
[ISA06_WRAttr] [varchar](200) NULL,
CONSTRAINT [PK_FormWorks_WorkRecordItem_ItemID] PRIMARY KEY NONCLUSTERED
([ItemID] 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
ALTER TABLE [dbo].[FormWorks_WorkRecordItem] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkRecordItem_WRID] FOREIGN KEY([WRID])
REFERENCES [dbo].[FormWorks_WorkRecord] ([WRID])
GO
ALTER TABLE [dbo].[FormWorks_WorkRecordItem] CHECK CONSTRAINT [FK_FormWorks_WorkRecordItem_WRID]
GO
-- tables in blocked process
CREATE TABLE [dbo].[FormWorks_Host](
[HostID] [int] IDENTITY(1,1) NOT NULL,
[HostName] [varchar](200) NULL,
[State] [int] NULL,
[ProtocolVersion] [bigint] NULL,
[DiskSize_Repository_K] [bigint] NULL,
[DiskFree_Repository_K] [bigint] NULL,
[DiskSize_FMD_K] [bigint] NULL,
[DiskFree_FMD_K] [bigint] NULL,
[FMDConnected] [int] NULL,
[Quenched] [int] NULL,
[Draining] [int] NULL,
[Paused] [int] NULL,
[AdminDraining] [int] NULL,
[Clearing] [int] NULL,
[ClearToPercentage] [int] NULL,
[ClearingToHostID] [int] NULL,
CONSTRAINT [PK_FormWorks_Host_HostID] PRIMARY KEY CLUSTERED
([HostID] 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
ALTER TABLE [dbo].[FormWorks_Host] WITH CHECK ADD CONSTRAINT [FK_FormWorks_Host_ClearingToHostID] FOREIGN KEY([ClearingToHostID])
REFERENCES [dbo].[FormWorks_Host] ([HostID])
GO
ALTER TABLE [dbo].[FormWorks_Host] CHECK CONSTRAINT [FK_FormWorks_Host_ClearingToHostID]
GO
CREATE TABLE [dbo].[FormWorks_WorkerType](
[WorkerTypeID] [int] IDENTITY(1,1) NOT NULL,
[WorkerType] [varchar](200) NULL,
CONSTRAINT [PK_FormWorks_WorkerType_WorkerTypeID] PRIMARY KEY CLUSTERED
([WorkerTypeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[FormWorks_WorkerInstance](
[WorkerInstanceID] [int] IDENTITY(1,1) NOT NULL,
[HostID] [int] NOT NULL,
[WorkerTypeID] [int] NOT NULL,
[InstanceNumber] [int] NULL,
[UserName] [varchar](200) NULL,
[Connected] [int] NULL,
[LoggedIn] [int] NULL,
[Paused] [int] NULL,
[Draining] [int] NULL,
[AdminDraining] [int] NULL,
[RunState] [int] NULL,
[LWMState] [int] NULL,
[RecallOverride] [int] NULL,
[WorkRecordCapacity] [int] NULL,
[WorkRecordsPendingAssign] [int] NULL,
[WorkRecordsUpdating] [int] NULL,
[WorkRecordsOutstanding] [int] NULL,
[WorkRecordsPendingAssignAck] [int] NULL,
[WorkRecordsPendingCAA] [int] NULL,
CONSTRAINT [PK_FormWorks_WorkerInstance_WorkerInstanceID] PRIMARY KEY CLUSTERED
([WorkerInstanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[FormWorks_WorkerInstance] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkerInstance_HostID] FOREIGN KEY([HostID])
REFERENCES [dbo].[FormWorks_Host] ([HostID])
GO
ALTER TABLE [dbo].[FormWorks_WorkerInstance] CHECK CONSTRAINT [FK_FormWorks_WorkerInstance_HostID]
GO
ALTER TABLE [dbo].[FormWorks_WorkerInstance] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkerInstance_WorkerTypeID] FOREIGN KEY([WorkerTypeID])
REFERENCES [dbo].[FormWorks_WorkerType] ([WorkerTypeID])
GO
ALTER TABLE [dbo].[FormWorks_WorkerInstance] CHECK CONSTRAINT [FK_FormWorks_WorkerInstance_WorkerTypeID]
GO
-- one more table between these two groups
CREATE TABLE [dbo].[FormWorks_WorkRecord](
[WRID] [int] NOT NULL,
[WorkerTypeID] [int] NULL,
[WorkerInstanceID] [int] NULL,
[State] [int] NOT NULL,
[Priority] [bigint] NULL,
[HoldPending] [int] NULL,
[DeletePending] [int] NULL,
[RecallPending] [int] NULL,
[CopyPending] [int] NULL,
[AutoRecallPending] [int] NULL,
[ReassignPending] [int] NULL,
[DeleteOperator] [varchar](200) NULL,
CONSTRAINT [PK_FormWorks_WorkRecord_WRID] PRIMARY KEY CLUSTERED
([WRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[FormWorks_WorkRecord] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkRecord_State] FOREIGN KEY([State])
REFERENCES [dbo].[FormWorks_WorkRecordStateName] ([ID])
GO
ALTER TABLE [dbo].[FormWorks_WorkRecord] CHECK CONSTRAINT [FK_FormWorks_WorkRecord_State]
GO
ALTER TABLE [dbo].[FormWorks_WorkRecord] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkRecord_WorkerInstanceID] FOREIGN KEY([WorkerInstanceID])
REFERENCES [dbo].[FormWorks_WorkerInstance] ([WorkerInstanceID])
GO
ALTER TABLE [dbo].[FormWorks_WorkRecord] CHECK CONSTRAINT [FK_FormWorks_WorkRecord_WorkerInstanceID]
GO
ALTER TABLE [dbo].[FormWorks_WorkRecord] WITH CHECK ADD CONSTRAINT [FK_FormWorks_WorkRecord_WorkerTypeID] FOREIGN KEY([WorkerTypeID])
REFERENCES [dbo].[FormWorks_WorkerType] ([WorkerTypeID])
GO
ALTER TABLE [dbo].[FormWorks_WorkRecord] CHECK CONSTRAINT [FK_FormWorks_WorkRecord_WorkerTypeID]
GO
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply