|
|
Posted by Erland Sommarskog on 08/17/07 22:07
(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|