avoid deadlock at all cost

Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
>> Based on the conversation that I saw, there is no guarantee to avoid
>> deadlock via sp_getapplock. See:
>> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
>> Any other idea? Thanks!
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David