Re: Deadlock problem when using SQL server

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

From: giespte (giespte_at_discussions.microsoft.com)
Date: 10/18/04 

Date: Mon, 18 Oct 2004 09:01:08 -0700

"David Browne" wrote:

>
> "giespte" <giespte@discussions.microsoft.com> wrote in message
> news:8F3D976F-A89A-46F3-8D1A-873948405241@microsoft.com...
> >I have a deadlock problem when using SQL server.
> >
> > It seems that the deadlock occurs when several threads udpate and then
> > query
> > different rows of the same table. I was able to reduce it to a simple
> > example:
> >
> > A table containing named counters:
> > The table has 2 columns:
> > name varchar( 100 )
> > value int( 4 )
> >
> > The table has a clustered primary key on the column 'name'.
> >
> > To get a unique counter value the following SQL statement (in a
> > transaction)
> > are executed:
> >
> > UPDATE counters SET value = value + 1 WHERE name = ?;
> > SELECT value FROM counters WHERE name = ?;
> >
>
> Are you wrapping these statements in an explicit transaction?
Yes.
>
> What is the index structure for the table?
The table has a clustered primary key on 'name', created with no additional
parameter. I suppose SQL Server uses a BTree as Default.

I remember that years ago, i had a performance problem with OpenIngres. A
hash Index improved the situation then. Is it possible to use hash indexes
with SQL Server ? Would they help in this situation ?
 
The Profiler logs a udpate key lock acquired and released for every row when
executing the update and a shared key lock acquired and released for every
row for the select. This looks like an index page scan to me.
Other locks ( Intended Update and Intended Share ) were set at table and
page level. The profiler skipped some log messages, so i have not been able
to analyze it in full detail, but it was lots of data to go through.
>
> Oracle and Sql Server are just different. In Oracle the statement
>
> SELECT value FROM counters WHERE name = ?;
>
> Generates no locks whatsoever, so I'm not sure you're going to get this to
> work without some customization for the different databases.
>
I know. That´s why i tried the nolock hint.
The thing that really surprised me was, that the deadlock occurred even
though the threads work on a separate data rows. There has to be some locking
at page or table level.

Thanks for the prompt reply.
Peter

> David
>
>
>

Relevant Pages

  • Re: SQL Server Performance Issue
    ... Deadlock were already there in the system. ... but comes the high log flush time issue and system is slow down. ... other log files, does it solve problem of high log flush time. ... You will get deadlock tracaes in the SQL Server error log by adding ...
    (microsoft.public.sqlserver.server)
  • Re: DBCC TRACEON did not capture info on deadlocking
    ... enabled tracing through DBCC TRACEON. ... And is there any other alternatives I have in capturing the deadlock ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • 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)