Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Locking in Tempdb

3 posts in Windows NT Last posting was on 2000-04-19 22:19:14.0Z
Wallace Henry Posted on 2000-04-19 14:25:31.0Z
Reply-To: "Wallace Henry" <whenry@futurenext.com>
From: "Wallace Henry" <whenry@futurenext.com>
Subject: Locking in Tempdb
Date: Wed, 19 Apr 2000 10:25:31 -0400
Lines: 6
Organization: FutureNext Consulting
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <hWg9#rgq$GA.89@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: host197.futurenext.com 208.203.71.197
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2297
Article PK: 1089728

I have been experiencing some problems with database locking in tempdb. I am
using version 11.02.1. WHen the locking occurs, I get locks on objects that
seem to be system tables within tempdb. What is being writeen or updated in
these tables. Is there anything I can do to prevent this?? Thanks.


Jason Froebe Posted on 2000-04-19 22:19:14.0Z
Message-ID: <38FE30E2.DBEABAEE@sybase.com>
Date: Wed, 19 Apr 2000 17:19:14 -0500
From: Jason Froebe <jfroebe@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.13 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Wallace Henry <whenry@futurenext.com>
Subject: Re: Locking in Tempdb
References: <hWg9#rgq$GA.89@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------7EAB887D0F3F611495829659"
Newsgroups: sybase.public.sqlserver.nt
Lines: 37
NNTP-Posting-Host: jfroebe-desktop.sybase.com 157.133.46.52
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2289
Article PK: 1089719


Wallace Henry wrote:

> I have been experiencing some problems with database locking in tempdb. I am
> using version 11.02.1. WHen the locking occurs, I get locks on objects that
> seem to be system tables within tempdb. What is being writeen or updated in
> these tables. Is there anything I can do to prevent this?? Thanks.

Hi Wallace,

ASE 11.0.2.1 is buggy. At a minimum go to 11.0.3.3 for Y2k reasons as well as
the fact that many locking bugs were fixed.

As for this specific problem, take a look at "tempdb Performance Issues"
chapter in the Performance & Tuning Guide (http://sybooks.sybase.com).

Oh, don't use named caches prior to 11.0.3.2 else you will end up with
corruption from time to time.

Jason


Download VCard jfroebe.vcf


"Mark A. Parsons" <pegasys Posted on 2000-04-19 21:00:49.0Z
Message-ID: <38FE1E81.9D096D28@_internet.co.nz>
Date: Thu, 20 Apr 2000 09:00:49 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Locking in Tempdb
References: <hWg9#rgq$GA.89@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 34
NNTP-Posting-Host: p282.ipa1-n8-16.iconz.net.nz 210.48.25.26
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2293
Article PK: 1089723


Wallace Henry wrote:
>
> I have been experiencing some problems with database locking in tempdb. I am
> using version 11.02.1. WHen the locking occurs, I get locks on objects that
> seem to be system tables within tempdb. What is being writeen or updated in
> these tables. Is there anything I can do to prevent this?? Thanks.

Without more specifics (types of locks, duration of locks, what the
associated user/application is doing, objects affected, etc.) I'll take
a pot-shot here ...

I'm going to assume that you have some users/applications is using #temp
tables. The creation, and subsequent destruction, of #temp tables
requires modifications to various system tables (e.g., sysobjects,
syscolumns, etc.) in tempdb. If you have a lot of users/applications
'constantly' building/destroying #temp tables then you may notice
blocking in tempdb, i.e., contention for locks on system tables.

How to 'prevent' this? Don't use #temp tables. Try to get your hands
on the offending code and see if you can cut down on the use of #temp
tables.

If this pot-shot doesn't address your situation you'll need to provide
more details of exactly what's going on in your system.

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz