April 2, 2009 at 3:26 am
Hi,
I am using one stored procedure to update/insert values to a table.
As I am tracking information frequently, there will be cases of multiple inserts\updates to that table.. At this time deadlock is happening.
I tried using transactions. But it din't work.. plz help me ...
Thanks ,
Manohar
April 2, 2009 at 4:03 am
Without seeing the queries in question, the only advice I can give you is to ensure there are no unnecessary commands within the transactions, ensure that your queries are as optimal as possible, ensure that your indexes support the workload and to access objects in the same order in different tranactions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 4:11 am
Here condition will checked for existance of tht record... If the record is already inside that table, then it wil be updated else a new record gets inserted...
UPDATE [DBO].[UserProgressData] SET
[ElementValue]=@ElementValue
where [UserID]=@UserID and [CourseAssetID]=@CourseAssetID and [CSAssetID]=@CSAssetID AND [ElementName=@ElementName
ELSE
insert into [DBO].[UserProgressData]
(UserID,CourseAssetID,CSAssetID,ElementName,Elementvalue) values (@UserID,@CourseAssetID,@CSAssetID,@ElementName,@ElementValu)
Only these statements are inside that procedure with that condition which checks for the existance...
April 2, 2009 at 7:09 am
The full procedures please (both of the ones involved in the deadlock), plus table structure and index definitions. If you can post the deadlock graph (produced by traceflag 1222) as well it would be very useful.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 10:25 pm
He is the procedure...
ALTER proc [dbo].[UpdateUserProgressData]
(
@user-id bigint=0,
@CourseAssetID bigint=0,
@CSAssetID bigint=0,
@ElementName nvarchar(max)='',
@ElementValue nvarchar(max)='',
@status nvarchar(max) OUTPUT
)
AS
BEGIN
IF EXISTS(select 1 from [UserProgressData] WITH(NOLOCK) where [UserID]=@UserID and [CourseAssetID]=@CourseAssetID and [CSAssetID]=@CSAssetID AND [ElementName]=@ElementName)
BEGIN
UPDATE [DBO].[UserProgressData] SET
[ElementValue]=@ElementValue
where [UserID]=@UserID and [CourseAssetID]=@CourseAssetID
and [CSAssetID]=@CSAssetID AND [ElementName]=@ElementName
SET @status='u001'
END
ELSE
BEGIN
insert into [DBO].[UserProgressData]
(UserID,
CourseAssetID,
CSAssetID,
ElementName,
Elementvalue)
values (@UserID,@CourseAssetID,@CSAssetID,@ElementName,@ElementValue)
SET @status='i001'
END
END
And here is the table structure
UserID bigint
CourseAssetIDbigint
CSAssetIDbigint
ElementNamenvarchar(MAX)
ElementValuenvarchar(MAX)
NoOfAttemptsbigint
The table has no primary key defined and no index defined.
And check the deadlock graph in attachment...
thank u...
April 2, 2009 at 11:05 pm
manohar (4/2/2009)
...The table has no primary key defined and no index defined...
NO table should be created without a primary key. Fix that and see if the problem goes away.
Also, these look like a problem:
ElementName nvarchar(MAX)
ElementValue nvarchar(MAX)
Why would a name have a datatype of nvarchar(MAX)? Especially since it is being used as part of the natural key. Come up with something realistic and change it to that.
ElementName and ElementValue make this look like some variation of an entity/attribute/value design. If so, that's too bad, and there is a good chance you will continue to have problems with deadlocks and many other things.
And finally, get rid of the WITH(NOLOCK) hint. That's a terrible idea in a transactional system. If you are worried about blocking, look into using SNAPSHOT isolation or READ_COMMITED_SNAPSHOT.
April 2, 2009 at 11:21 pm
Thanks Michael,
That deadlock problem got solved after adding primary key to the table.
Cheers,
Manohar
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply