HELP: Deadlocking system tables (aggregate functions, temp tables, select into)

[Lee Doty]

Hiya folks,

The application:

We have implemented a real time material handling system with 50
clients and one server. We do in the neighborhood of 800,000
transactions a day. All apps are written in C++, and we have 2 types
of access methods for the DB: RecordSets for most load/store actions
of our objects, and Direct ODBC for high performance areas of the
system (we got from 4X-60X increase in performance by optimizing these
areas w/ stored procedures and direct ODBC Calls). Each application
maintains 2 connections at all times to the server (one each for
RecordSets and Direct ODBC)

The system:

Most clients are 486/33s with the odd P100 mixed in. The server is an
IBM quad PPro. We are using MS SQLServer 6.5. Clients use win95,
Server is NT3.51.

The problem:

We are encountering hundreds of deadlocks every day (sometimes in the
thousands). Now I'd expect to run into deadlocks in a high volume
system like this, but the problem is that, according to MS tech
support, I'm getting deadlocks from tempdb system tables when I create
temp tables. This deadlock can occur between 2 select statements that
are _not_ enclosed in transactions, as long as they use aggregate
functions.

I'll try to restate that: According to MS, any time I do anything
that causes a temp table to be created (SELECT INTO, Use Aggregate
functions, CREATE TABLE #TableName, ORDER BY, etc.) there is a
possibility that the statement may conflict with another temp table
creator and deadlock. (again for clarity: not block..._Deadlock_)

Excuse my surprise, but this sounds wrong to me. From my
understanding, deadlocks require 2 transactions, at least one
exclusive lock, one or more shared locks, and at least a total of 4
locks.

Our situation has no transactions (other than the implicit atomic tran
that surrounds the selects) no exclusive locks (other than implicit
locks caused when the temp tables are created), and only one resource
in common (the system table accessed to create the temp tables).

3 questions:

1) why isnt resource contention for the system table for creating
temps handled better?
2) is this normal? Do all SQL Database Systems have this problem?
(meaning do I just need to get used to it?)
3) what can I do about it?

Also, if all is as it should be, then can any of you help me to
understand why the server allows this to happen. It seems like this
would have been something simple for the server to handle, and I'm
quite frankly dumbfounded that this is a problem in a modern DBMS.

_Any_ imput (even if its only that I'm just naive) would be very
appreciated.

-Lee
----
"It can't rain all the time"

[Daniel Polack]

Build an error handling that catch the deadlock, then retry your statement.

--
System Developer
Daniel Polack
D...@compunet.de

[Ben McEwan]

> We are encountering hundreds of deadlocks every day (sometimes in the
> thousands). Now I'd expect to run into deadlocks in a high volume
> system like this, but the problem is that,

Even on a high throughput system you should not expect this volume of
deadlocks. I'll bet that some examination of the query construction and
your index selections could do wonders for your situation. Deadlock is a
performance killer, no doubt about it.

> according to MS tech
> support, I'm getting deadlocks from tempdb system tables when I create
> temp tables. This deadlock can occur between 2 select statements that
> are _not_ enclosed in transactions, as long as they use aggregate
> functions.
> I'll try to restate that: According to MS, any time I do anything
> that causes a temp table to be created (SELECT INTO, Use Aggregate
> functions, CREATE TABLE #TableName, ORDER BY, etc.) there is a
> possibility that the statement may conflict with another temp table
> creator and deadlock. (again for clarity: not block..._Deadlock_)

Right. The deadlock is occurring (I'm theorizing based on what you've
said so far) inside tempdb system tables. I've seen this happen before.
The problem can occur when several connections all try to create a temp
table at the same time. To create a temp table, they need to allocate
extents in tempdb, which is tracked in sysindexes. Even if the object
has no indexes defined on it, sysindexes tracks data page allocations.
Now, when one guy is trying to read sysindexes to find out which extent
he can get, and somebody else is trying to update sysindexes to reserve
an extent... deadlock.

> 3 questions:
>
> 1) why isnt resource contention for the system table for creating
> temps handled better?

This deadlock should only be occurring in rare instances when extents
must be newly allocated. I doubt that ALL of your problems with deadlock
- thousands a day, you said- are related to tempdb..sysindexes, although
this may be the instance tech support latched on to.

> 2) is this normal? Do all SQL Database Systems have this problem?
> (meaning do I just need to get used to it?)

No. The deadlock frequency you mention is not normal. It can be fixed.
SOME deadlock is inevitable. This much deadlock is not normal.

> 3) what can I do about it?

If you want to try to fix it yourself, the first step is tracking where
deadlocks occur. To do this, you have two options: turn it on for
everybody (risky, if you're in production, because this DOES affect
performance) or turn it on for a few select clients.

- To turn it on for everybody, start SQL Server with the command line
switches, "-T3605 -T1204". This logs deadlock activity to the server
errorlog.

- To turn it on for some people, have the application login as sa, and
issue the command "dbcc traceon (3605, 1204)" Alternatively, you can
run (3604, 1204) to return the output to your client, instead of the
errorlog.

Now, examine the errorlog for deadlock activity. The output will list
the process ID of the deadlock victim and victor, the index (indid 0
means the data page) and the table. After you're armed with this, you
have a number of options for reducing deadlock.

The other option is (Uh-oh, here's the pitch) to hire a consultant who
has some experience troubleshooting deadlock. If you'd like some help,
drop me an email and we'll talk. If you want to try beating the deadlock
monster on your own, keep the newsgroup posted and show us some deadlock
log output. Good luck.

--
03/28/97 10:56
Ben McEwan
Geist, LLC - http://www.metis.com/~geist
bmc...@metis.com.

[Robert W. Weeks]

"Daniel Polack" <d...@compunet.de> wrote:

>Build an error handling that catch the deadlock, then retry your statement.
>

That's a good idea. Can you tell us how to do this?

------------------------------------------------------------------------
Robert W. Weeks rww...@southwind.net
http://www2.southwind.net/~rwweeks Wichita, Kansas, USA

[Ron Roelfs]

When you are talking about deadlock there are many things to consider.
1) Your DB may automatically handle the first deadlock and retries (DB2
does this).
2) Are your tables designed to reduce deadlocks?
3) Do your queries lock the rows unnecessarily?
4) Should your client be allowed to decided whether to retry?
5) Maybe the scope of your transaction is too large and reducing it will
reduce deadlocks.

Ron Roelfs
rro...@ix.netcom.com

Robert W. Weeks <rww...@southwind.net> wrote in article
<3340b2bd....@news.southwind.net>...

[Binheng SONG]

Lee Doty wrote:
>

> 2) is this normal? Do all SQL Database Systems have this problem?
> (meaning do I just need to get used to it?)

Yes, that normal, it is the weakness of most of the pop RDBMS, the
Codd's
model permmits too much operating by apps on the tables which should be
done by the system.
It will never solve this problem except you design your app carefully
and
carefully.....

-
Binheng SONG, Department Of Applied Mathematics
Tsinghua University, Beijing 100084
Tel: 010-62785886(H), 62782362(O), Fax: 62782362
http://faculty.math.tsinghua.edu.cn/faculty/~bsong/