transactions

I have a small database that I have been testing.
I get an error about a transaction deadlock.
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

Re: transactions

On Jul 30, 1:17 pm, steven wrote:
> I have a small database that I have been testing.
> I get an error about a transaction deadlock.
> 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

You didn't give much info. This might be due to non-availability of
index if you have an update or delete statement in the SP with a
filter. try creating an index on the filter column.

profile deadlock chain event and graph to figure out which object is
causing the event.

Re: transactions

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/DeadlockD etector2_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 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

Re: transactions

On Jul 30, 3:17 am, steven wrote:
> I have a small database that I have been testing.
> I get an error about a transaction deadlock.
> 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

Steve,

the following article will be useful:

http://www.devx.com/CoDe%20Magazine/Article/17447/0/page/7

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Re: transactions

(dba@sql-labs.com) writes:
> 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.

Note that this depends on the kind of access. The default isoaltion
level in SQL Server is READ COMMITTED, so as long as you are only
reading rows, the transaction does not matter in any direction.

> 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.

For updates you should always identify operations that needs to be
performed as a unit, to make sure that your database does not have
inconsistent data. This is a goal which is more than important than
avoiding deadlocks.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx