September 22, 2009 at 3:21 am
Hi
This is a node from the dead lock graph.
I am not able to get what kind of information is being re-presented here, could someone help?
cheers
Siddarth
September 22, 2009 at 6:34 am
It's blank. If you're posting XML, you need to use the code listing on the left
[ code = "xml" ]
Just remove the extra spaces.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 6:37 am
Grant Fritchey (9/22/2009)
It's blank. If you're posting XML, you need to use the code listing on the left[ code = "xml" ]
Just remove the extra spaces.
I am glad you posted, I was thinking I needed to change my glasses
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 8:07 am
SELECT @deleteRequestID = Request.ID FROM dbo.Request
INNER JOIN dbo.RequestStatus ON Request.CurrentRequestStatusID = RequestStatus.ID
AND RequestStatus.StatusID NOT IN (6,8,9,11,14,16,18,32)
WHERE isnull(Request.IsForRoboticAccount, 0) = 0 AND Request.RequestTypeID IN (3)
AND Request.SystemID = @sysID
AND Request.RequestDetails.value('(/System/SystemLoginName)[1]','varchar(20)') = @accountName
September 22, 2009 at 8:20 am
This node, along with another, had a deadlock, on a page, 6:1:96560 (databaseid:fileid:pageid). This node is the victim, the spid that got killed and rolled back. Based only on the query at hand, something else is occuring within this process. You need to look at both nodes and the entire set of queries within both transactions in order to determine specifically why they locked on that resource. I'd certainly look at all the code for proc CAMS.dbo.GetRevalidationHistory, because I doubt it's just running a SELECT statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 8:26 am
Could you please arrange to answer the followingt things in respect to the dead lock graph posted:-
1:There are 4 process involved but the no of process nodes appearing is 6.
2: Process with SPID=73 is also appearing twice and calling itself. Is that related to inter query parallelism?
3: Process with SPID=60 is appearing twice.
What is happening is different users are trying to execute the same functionality and that's causing the dead locks.I have done some investigation, but before I implement the solution, I need to understand the above mentioned things.
cheers
September 22, 2009 at 8:33 am
siddartha pal (9/22/2009)
Could you please arrange to answer the followingt things in respect to the dead lock graph posted:-1:There are 4 process involved but the no of process nodes appearing is 6.
2: Process with SPID=73 is also appearing twice and calling itself. Is that related to inter query parallelism?
3: Process with SPID=60 is appearing twice.
What is happening is different users are trying to execute the same functionality and that's causing the dead locks.I have done some investigation, but before I implement the solution, I need to understand the above mentioned things.
cheers
Must be something wrong with my browser. I can only see SPID 60 and that only once.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 8:38 am
Grant Fritchey (9/22/2009)
Must be something wrong with my browser. I can only see SPID 60 and that only once.
Nope - your browser is fine.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 8:39 am
Seems some issue with the posting, I am attaching the file in txt format.
September 22, 2009 at 8:56 am
You're seeing two SPID 60's because there are two sub-processed with different ECIDs - i.e. more than one thread is running the query on behalf of that SPID.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 8:58 am
Hi
I have a table with as follows:-
CREATE TABLE [dbo].[RqstStats](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[RequestID] [bigint] NOT NULL,
[StatusID] [int] NOT NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RqstStats_CreatedDate] DEFAULT (getdate()),
[ApproverID] [int] NULL,
[NextStatusID] [int] NULL,
[Remark] [nvarchar](max) NULL,
CONSTRAINT [PK_RqstStats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Now I have defined a view on the same:-
CREATE VIEW [dbo].[AccessRqstStats] WITH SCHEMABINDING
AS
SELECT ID,RequestID,StatusID,ApproverID,NextStatusID FROM dbo.RqstStats
and created index as follows on the same view :-
CREATE UNIQUE CLUSTERED INDEX [IX_AccessRqstStats] ON [dbo].[RqstStats]
(
[ID] ASC
)
If I fire an insert on the table created above, will the view will be getting locked with index?
I think all three objects will be locked but I am getting things different when the same I can view in a dead lock graph. This is just an a quetsion after reading the deadlock graph.
cheers
Siddarth
September 22, 2009 at 9:00 am
Hi
I have a table with as follows:-
CREATE TABLE [dbo].[RqstStats](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[RequestID] [bigint] NOT NULL,
[StatusID] [int] NOT NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_RqstStats_CreatedDate] DEFAULT (getdate()),
[ApproverID] [int] NULL,
[NextStatusID] [int] NULL,
[Remark] [nvarchar](max) NULL,
CONSTRAINT [PK_RqstStats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Now I have defined a view on the same:-
CREATE VIEW [dbo].[AccessRqstStats] WITH SCHEMABINDING
AS
SELECT ID,RequestID,StatusID,ApproverID,NextStatusID FROM dbo.RqstStats
and created index as follows on the same view :-
CREATE UNIQUE CLUSTERED INDEX [IX_AccessRqstStats] ON [dbo].[RqstStats]
(
[ID] ASC
)
If I fire an insert on the table created above, will the view will be getting locked with index?
I think all three objects will be locked but I am getting things different when the same I can view in a dead lock graph.
cheers
Siddarth
September 22, 2009 at 9:06 am
Just an insert is not a cause for deadlocks. You've got to have more than one process attempting to get exclusive access to a row or page. Two shared locks won't cause a deadlock and an exclusive lock and a shared lock will get blocking of one or the other. So something besides a simple select and a simple insert is occurring here. Do you have triggers on the table as well?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 9:35 am
No we dont have triggers. The reason why I asked last question is, in dead lock graph,
SPID=73 has IX lock on AccessRequestStatus which is a view and SPID74 is trying to get an IX lock on the same object.
Actually SPID73 inserts records in a table on which the view is based. So why we can see that IX lock on this view is being aquired by the process?
siddarth
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply