deadlocks and #tables

Question

deadlocks and #tables

bvesan

Old Hand

Points: 360
More actions
March 27, 2002 at 5:11 am

#78068

Hi,
I'm facing a deadlock problem, and I think that it might be a classic one:
Some stored procedure use temporary tables (# ones) that are often called. At the beginning of a call, a table is created. At the end of the call, the table is droped. Those two statements require locks on tempdb..sysobjects, tempdb..syscolumns, and sometimes tempdb..sysindexes. The lock demands on those system tables generate deadlock...
(for information, this is SS6.5, bu I think the deadlock would also occure on SS7 or SS2K)
Since I'm not the first one to use temporary tables in stored procedure, I'm sure I'm not the first one facing this kind of problem !
Does anybody have an idea of how to solve this ?
Thanks.
Benjamin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ckempste SSCoach Points: 17983 More actions · Answer 1

hmm.. id be very supprised if sqlserver escalated the lock to such a level that deadlocking occured, and I wouldnt call it a deadlock as such, just a prolonged wait. I deal with a range of apps under 2k that utilise the same paradigm with no issues against the sys tables, and its reasonably user intensive. Can you send through the lock info? 6.5, from my dealings with it is very poor, id say v7 is v1 of SQLServer for Microsoft, all subsequent versions were poor mans conversions from sybase (now that comment will spark some good talk!).

Did you consider ## tables? under 2k, id consider the table data type but as a DBA its something you dont banter around to the newbi developers as id hate to see what happens to memory usage with rouge programs.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

bvesan Old Hand Points: 360 More actions · Answer 2

There is no need to have a lock escalation in order to get a "good" deadlock.

Since (I guess) system tables have no Cluster Index, newly inserted rows are on the last page, so the deadlock occures as, for example, there is a drop table (delete on syscolumns then delete on sysobjects in one transaction ?), and a create table (insert on sysobjects, then on syscolumns)

Here is the relevant part of the trace:

"2002/03/27 03:34:52.78 spid29 *** DEADLOCK DETECTED with spid 31 ***

spid 29 requesting EX_PAGE (waittype 0x5), blocked by:

SH_PAGE: spid 31, dbid 2, page 0x6, table 0x1, indid 0

spid 29: pstat 0x0000, cputime 193091, hostname: PAR-RMGSRV-4, progname: MSDBLIB, pcurcmd DROP TABLE(0xc7), input buffer: sp_get_pos_loader

Locks held by spid 29:

EX_INT: spid 29, dbid 2, table 0x2

EX_INT: spid 29, dbid 2, table 0x3

EX_INT: spid 29, dbid 2, table 0x1"

...

"

spid 31 waiting for SH_PAGE (waittype 0x8006), blocked by:

UP_PAGE: spid 29, dbid 2, page 0x4, table 0x1, indid 0

EX_PAGE: spid 29, dbid 2, page 0x4, table 0x1, indid 0

SH_PAGE: spid 31, dbid 2, page 0x6, table 0x1, indid 0

"

Of course, dbid 2 is tempdb,

table 1 is sysobjects

table 2 is sysindexes

table 3 is syscolumns ...

I can't use global temporary tables because the same procs may be launched at a time by two different connections from the same SQL user !

Edited by - bvesan on 03/27/2002 07:01:01 AM

Edited by - bvesan on 03/27/2002 07:21:09 AM

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 3

Benjamin,

I'm not a great expert on deadlocks. But global temporary tables, I've had the pleasure of using.

If you add a column SPID, and add this column to any unique/primary keys, then each process has it's own area of your table.

Kind regards

Henrik Staun Poulsen

Stovi Software

Bharatvip Ten Centuries Points: 1198 More actions · Answer 4

We had locking problems in one of our databases due to the use of temp tables in stored procs. The reason for our problem was the use of 'SELECT INTO' when populating the temp tables. When we changed from 'select into' to creating the temp table first and then populating it, the problems disappeared.

e.g.

Our Locking problem:

select a,b,c INTO #XYZ

from Policy where a = 5

Problem solved for us:

Create table #XYZ (a int, b int, c int)

insert #XYZ (a,b,c)

select a,b,c from Policy

where a = 5

Don't know if this is your problem but that's my 2 cents.

e.g. SELECT INTO #xyz that they were not first creating the temp table and then populating it with a select

had some problems with temp tables in stored procs and I found that my problems were because I was

ckempste SSCoach Points: 17983 More actions · Answer 5

Hi there

Very interesting and thanks for the post. Ill do some testing of my own later with the INTO clause and re-check some of our new stored procs with the developers.

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

bvesan Old Hand Points: 360 More actions · Answer 6

Thanks for the SELECT INTO tip.

It looks like CREATE TABLE statement requires only EXclusive locks, whereas SELECT INTO requires EXclusives AND SHared locks, and that is what created the deadlocks.

The stored procs will be modified, and I hope the problem won't occure. You'll be informed.

Benjamin

by the way, Microsoft's workaround for such problem is something like "do not use tmp tables, create classics tables instead, use them, and then drop them" !!!