Deadlock Resolution - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Deadlock Resolution

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-11-26 : 07:16:19

I've got an application which is beginning to deadlock, in part because it was originally designed for use by about a dozen people, and it's now being used by 100's simultaneously. I fixed it by changing the "UPSERT" queries to include a TRY/CATCH block. Here's what the queries generally look like:

BEGIN TRANSACTION
BEGIN TRY
	IF EXISTS (SELECT ID FROM MyTable WHERE ID = 12345)
	BEGIN
	   UPDATE MyTable
	   SET MyNumber = 12345678
	   WHERE ID = 12345;
	   SELECT 12345;
	END
	ELSE
	BEGIN
	   INSERT INTO MyTable (MyNumber) Values(12345678);
	   SELECT SCOPE_IDENTITY();
	END
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK
END CATCH;

There will generally be about 5 of these in rapid succession by any one user, each to a different table. To fix the Deadlock I added the TRY/CATCH block that you see (it wasn't in the original queries). At a glance my worry is that this query might get stuck never finishing. Is that a valid concern? And if so, how do I handle that? Is there a better (read: more secure) way to handle this? For instance, should I try and combine those 5 UPSERT's into a single command somehow to mitigate the problem? Thanks for any suggestions.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-26 : 07:41:10

your deadlocking issues are probably down to the individual insert's / updates taking a while to do. Do you have nice indices on the relevant tables? If your connections all have to perform table scans then you'll be running the risk of blocking, etc.

100's of users shouldn't be any kind of problem if they are all doing stuff like your snippet implies.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

VentureFree
Starting Member

19 Posts

Posted - 2009-11-26 : 07:56:42

I'm self taught, and my predecessor preferred to dump everything into single tables so that's all I've really worked with so far. This is my first real-world attempt at using an actual relational database. That being the case I've never had to do any kind of indexing before now. You better believe I'm looking into it now.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-26 : 11:32:18

Can you post some examples of your bigger tables?

Include the number of rows if possible?

Is the database normalised at all?

Do you even have keys and constraints or is everything just excel spreadsheets in sql form?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources