Posted by dba on 08/17/07 11:43

Hi Steve,

A transaction holds locks on objects until all operations within the
transaction are committed. If you wrap your entire SP within a
transaction, that means all objects accessed by the SP are blocked
until the SP completes. So, contrary to what you expected, doing so
increases the chances of deadlocks.

Try to limit the operations you enclose in a transaction. If you can
avoid it or if it's not necessary, don't use transactions at all. I
also suggest you create proper indexes on the table. That will improve
io access to it and reduce the amount of time required to lock the
table.

I also suggest you try out the tool called SQL Deadlock Detector. It
monitors your database for locks and deadlocks and
provides complete information on captured events. It tells you
everything you need to know (locked objects, blocked statements,
blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about
this tool is it's event diagram which makes it exremely easy to see
what exactly
is going on.

You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.

I've been using it for quite a while now (I purchased it) and find it
very handy and useful.

HTH.



On Jul 30, 4:17 pm, steven <sfuc...@verizon.net> wrote:
> I have a small database that I have been testing.
> I get an error about a transactiondeadlock.
> The code is in stored procedures and I added transactions to the sp's
> but the error happened again.
>
> I wrapped the whole sp in just one transaction and I don't have any
> index on the tables.
>
> When I test just by running a program that sends 3 calls at a time it
> will get a deadlocked transaction as I send 6 or 9 at a time.
>
> I am not sure how it can have a deadlocked transaction after I used
> transactions(begin and commit) in the sp's.
>
> Steve

[Back to original message]