Deadlock Issues

Question

Deadlock Issues

Alvin-331751

Grasshopper

Points: 15
More actions
May 24, 2006 at 12:52 pm

#91286

Hi
I am having issues when the same SQL queries run simultaneously from diiferent ASP App threads is getting deadlocked.
The deadlock is for a query, which calls a Stored Proc. The stored Proc has a transaction, inside of which it does the following
Alter Proc DoSomething
BEING
BEGIN TRANSACTION TRAN1
SELECT * from TABLEA -- captures values in local variables
DELETE from TABLEA
INSERT into TABLEB -- values captured from TABLEA
COMMIT TRANSACTION TRAN1
SELECT @V1, @V2 from TABLEA -- same as in first step
END
It is the DELETE that is causing a Deadlock
Would really appreciate help on this
Thanks
Alvin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How many rows does your SELECT and DELETE statement process? You need to check your execution plans for these 2 statements. My guess is that the execution plan will not show index seek / indexed delete.

Deadlocks can happen when more than one thread is accessing the same tables, but you should be able to greatly minimize this by keeping your transactions as short as possible. Part of keeping your transactions as short as possible is proper index design and use of indexes.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Alvin-331751 Grasshopper Points: 15 More actions · Answer 2

This is affecting 7800 rows. Index is being used where it can, for example delete is based on that.

Thanks

R2ro Default port Points: 1409 More actions · Answer 3

Another possible method to shorten the transaction is to:

INSERT INTO TableB SELECT..FROM TableA

DELETE FROM TableA INNER JOIN TableB ON...

Assuming that indices are available for the SELECT and DELETE..JOIN

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Since you don't use XACT_Abort and you don't have any error checking code to do a conditional rollback, you don't really need the transaction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Alvin-331751 Grasshopper Points: 15 More actions · Answer 5

Even if do a transaction abort, that wouldn't do anything, since the transaction does rollback. Thanks