Re: Locking

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 08:05:23 -0500

A deadlock is when two users attempt to update 2 tables in opposite orders
and neither can complete because they are waiting on the other to release
it's locks. When this happens sql server automatically detects it and will
kill one of the users. So if your app is slow and waiting it is not
deadlocks but rather blocking. Here are some links on this:

http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking

But your simplified scenario does not seem to indicate why there would be an
issue. How many rows are you deleting at one time and how are you doing it?
Do you have proper indexes on the tables? Can you post the code? 

-- 
Andrew J. Kelly
SQL Server MVP
"Justin" <none> wrote in message news:HMydnX4b1q8dcKjdRVn-uA@is.co.za...
> I have a stored proc which reads data out of a table, and deletes it once
it
> has been read.
> At the same time another stored proc is inserting data into the table,
> updated fields, and deleting records.
>
> The problem comes, once the first stored proc is reading from the table,
and
> the 2nd stored proc executes, and tries to delete a record. A deadlock
> occurs, an the SQL box begins to get extremely slow. What is the best way
to
> avoid this?
>
> Thanks,
> Justin
>
>

Relevant Pages

  • Strange error code for deadlock
    ... I am currently writing an deadlock handler for our application (a COM ... Having searched both ADO and SQL Server references, ... "NativeError" field. ... error message to find "deadlock victim" for example); ...
    (microsoft.public.vb.database.ado)
  • Re: Select statement Causing Table Locks
    ... Ok, it is technically a blocking chain, but this query does not continue. ... a deadlock is when two or more processes all block each ... SQL Server will detect deadlock situations, ...
    (microsoft.public.sqlserver.programming)
  • Deadlocks and Parallel Query Processing
    ... SQL Server 2000 SP3A ... Last week one of our processes starting issuing or suffering deadlock ... detected errors every 15 minutes or so. ... best I can tell from trying to query the actual data each update hits ...
    (comp.databases.ms-sqlserver)
  • Re: Deadlock problem when using SQL server
    ... >>I have a deadlock problem when using SQL server. ... >> It seems that the deadlock occurs when several threads udpate and then ... I suppose SQL Server uses a BTree as Default. ...
    (microsoft.public.sqlserver.server)
  • Re: Strange error code for deadlock
    ... Since you are working through OLEDB provider, ... > I am currently writing an deadlock handler for our application (a COM ... > Having searched both ADO and SQL Server references, ... > error message to find "deadlock victim" for example); ...
    (microsoft.public.vb.database.ado)