need help with deadlock in simple update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need help with deadlock in simple update

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2004-06-07 : 12:26:47

I read the BOL info and the articles on this site re deadlocking and how to resolve. However, I am at a loss as to figuring out this one deadlocking problem I have been encountering recently.

I have what I think is a pretty simple sproc that updates (or creates) an entry in a table. The table is only updated in this sproc (ie no triggers other other updating sprocs that might confuse things), and the sproc was working very nicely until recently.

Can anyone tell me why this simple update/create sproc might cause a deadlock?

Any help appreciated.

-------------------------------------------------------------
-- to get a deadlock condition, we change the last param
-- between one value and another
exec putForecastGroup 'joe','13','123','ABCD','EFG','2004','03','09',30
-------------------------------------------------------------

CREATE PROCEDURE putForecastGroup @userid varchar(10), @companycd varchar(2), @forecastinggroup varchar(30), @brand_code varchar(5), @subclass_code varchar(5), @year varchar(4), @type varchar(2), @period varchar(2), @cases int
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @oldcases int, @changesmade bit
DECLARE @brand varchar(30), @itemsubclass varchar(20)

SET @changesmade = 0

SELECT @brand = [description] FROM systbl_brand WHERE brand_code = @brand_code
SELECT @itemsubclass = [description] FROM systbl_itemsubclass WHERE subclass_code = @subclass_code

-- get previous value if it exists
SELECT @oldcases = isnull(cases, 0)
FROM forgroup
WHERE companycd = @companycd AND forecastinggroup = @forecastinggroup AND brand_code = @brand_code AND subclass_code = @subclass_code AND [year] = @year AND period = @period AND type = @type

-- insert/update only if the cases does not equal existing cases; log any change
IF @@rowcount = 0
BEGIN
INSERT forgroup
(companycd, forecastinggroup, brand_code, subclass_code, [year], period, type, cases, userid, lastmoddatetime)
VALUES
(@companycd, @forecastinggroup, @brand_code, @subclass_code, @year, @period, @type, @cases, @userid, getdate())

SET @changesmade = 1
END
ELSE
-- only update if changes made
IF @oldcases <> @cases
BEGIN
-- **** this is where the deadlock occurs ****
UPDATE forgroup
SET
cases = @cases,
userid = @userid,
lastmoddatetime = getdate()
WHERE companycd = @companycd AND forecastinggroup = @forecastinggroup AND brand_code = @brand_code AND subclass_code = @subclass_code AND [year] = @year AND period = @period AND type = @type

SET @changesmade = 1
END

IF @changesmade > 0
-- track changes
INSERT foraudit
(companycd, forecastinggroup, brand_code, subclass_code, [year], period, type, oldcases, adjcases, newcases, userid, lastmoddatetime)
VALUES
(@companycd, @forecastinggroup, @brand_code, @subclass_code, @year, @period, @type, isNull(@oldcases, 0), isNull(@cases - @oldcases, 0), @cases, @userid, getdate())

GO

gdeconto
Posting Yak Master

107 Posts

Posted - 2004-06-07 : 12:50:07

p.s. here is the deadlocking error I get:

Server: Msg 1205, Level 13, State 50, Procedure putForecastGroup, Line 39
Transaction (Process ID 76) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

gdeconto
Posting Yak Master

107 Posts

Posted - 2004-06-07 : 13:09:52

p.p.s

Why, when I use DBCC TRACEON (1204) on my sql2k, do I not get the large amount of info that BOL mentions and that I have seen in posts on using DBCC TRACEON?? I have tried sending the lock tracing info to the log and to the client but dont see anything detailed, just the message you see in the reply I posted earlier

gdeconto
Posting Yak Master

107 Posts

Posted - 2004-06-07 : 15:31:46

hmmmmm. I have solved the problem but I am not sure why.

I found out that someone had recreated the table but had forgotten to create the indicis for it.

when I recreated the indicis, the updates worked fine!?

doesnt answer:

1. why we got a deadlock error message when there was only one table being updated (I know that deadlocks can be any resources, not just tables)

2. why creating the indicis solved the problem

3. why my DBCC TRACEON (1204) doesnt produce any detailed trace info

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources