Re: Transaction Deadlocks your thoughts?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 08:35:21 -0400

The rules for minimizing deadlocks are
1. Keep your transactions short - the longer the tran, the longer you hold
locks, the more likely it is that you will bump into someone in this bad
way.
2. Lock all resources in the same order, Maybe use tables alphabetically and
rows within tables in key order... The order doesn't matter as long as all
of the code does this. It is not possible to follow this all the time, but
to the extent possible - do it.
3. On the front end ( not in the SQL back end ) if you get a deadlock error
(1205) wait a little while and re-try the transaction, sheilding the error
from your end user.
4. You may look at the sp_getapplock procedures in SQL. These would allow
you to "soft lock" any resource you need prior to going into transaction
state, but all code would need to use it.. 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anubis" <anubis@bluestreem.com.au> wrote in message
news:uQLLTDNlEHA.3824@TK2MSFTNGP12.phx.gbl...
> Hello All,
>
>
> I was interested to see peoples opinions and fixes?
>
> I'm currently working on a site which 99% consists of Stored Procedures
and
> on the old site which this one will be replacing is basically recordsets
> with update commands.
>
> The old site is currently producing a large number of transaction
deadlocks,
> I'm hoping that with a database restructure and changing to stored
> procedures this may help to correct the problem?
>
> Any thoughts on this issue would be great!
>
> Thanks
> Anubis
>
>

Relevant Pages

  • Re: How do PerfMon counters work?
    ... Lock waits is explained fairly clearly in the Performance Monitor app - ... Deadlocks are not so well explained in the PerfMon. ... way that deadlocks are detected in SQL Server is that a dedicated System ... connections deadlocked in the lock manager sub-system. ...
    (microsoft.public.sqlserver.server)
  • Connection not timing out
    ... So whichever opens a transaction will have the lock and update the mutex row ... for SQL server holding the lock as well? ...
    (microsoft.public.data.ado)
  • Connection.IsolationLevel = adXactSerializable and timeout
    ... So whichever opens a transaction will have the lock and update the mutex row ... states that the timout settings control the when the error occurs due to ... for SQL server holding the lock as well? ...
    (microsoft.public.data.ado)
  • Re: Understanding how my code is causing deadlocks!
    ... Can you help me elaborate the scenario for me? ... from your post of conversion deadlocks and locking mode compatibility. ... is trying to convert to a U or higher lock and they are waiting for me to ... > Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Concurrent database updates
    ... Update/Delete/Select till you either Rollback or Commit the Transaction. ... then we go by the lock. ... Remember that SQL Server goes by Page level locking and for high volume ...
    (microsoft.public.dotnet.framework.aspnet)