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.

Finding Query causing locks to be used up

4 posts in General Discussion Last posting was on 2010-09-21 17:48:42.0Z
Tim Posted on 2010-09-21 06:02:09.0Z
Sender: 6a51.4c98499b.1804289383@sybase.com
From: Tim
Newsgroups: sybase.public.ase.general
Subject: Finding Query causing locks to be used up
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c984a61.6a60.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Sep 2010 23:02:09 -0700
X-Trace: forums-1-dub 1285048929 10.22.241.41 (20 Sep 2010 23:02:09 -0700)
X-Original-Trace: 20 Sep 2010 23:02:09 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29574
Article PK: 78804

Hi ,
We upgraded to ASE 15.0.3 and have been having ASE has run
out of locks error almost everyday despite locks set at
750000.We have engines.number of worker processes 25
max parallel degree 1
max query parallel degree 1
max scan parallel degree 1
So not relying on high parallism either
The lock promotion thresholds are at default values
row lock promotion HWM 200
row lock promotion LWM 200
row lock promotion PCT 100
So after 200 row locks i believe, table lock should be
secured if its not conflicting with other existing locks
.How do we figure out the exact query causing this as a
bunch of queries are captured everytime in error log.However
checking the query plan shows index usage. We have all DRL
tables. Also we run reorg rebuild, update stats and
recompiles daily


Rob V [ Sybase ] Posted on 2010-09-21 11:37:19.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Finding Query causing locks to be used up
References: <4c984a61.6a60.1681692777@sybase.com>
In-Reply-To: <4c984a61.6a60.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c9898ef@forums-1-dub>
Date: 21 Sep 2010 04:37:19 -0700
X-Trace: forums-1-dub 1285069039 10.22.241.152 (21 Sep 2010 04:37:19 -0700)
X-Original-Trace: 21 Sep 2010 04:37:19 -0700, vip152.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29575
Article PK: 78805


On 21-Sep-2010 08:02, Tim wrote:
> Hi ,
> We upgraded to ASE 15.0.3 and have been having ASE has run
> out of locks error almost everyday despite locks set at
> 750000.We have engines.number of worker processes 25
> max parallel degree 1
> max query parallel degree 1
> max scan parallel degree 1
> So not relying on high parallism either
> The lock promotion thresholds are at default values
> row lock promotion HWM 200
> row lock promotion LWM 200
> row lock promotion PCT 100
> So after 200 row locks i believe, table lock should be
> secured if its not conflicting with other existing locks
> .How do we figure out the exact query causing this as a
> bunch of queries are captured everytime in error log.However
> checking the query plan shows index usage. We have all DRL
> tables. Also we run reorg rebuild, update stats and
> recompiles daily

Check your ASE errorlog. Whenever you run out of locks, a 1204 error is
raised, which is also written to the ASE errorlog.
This includes the SQL text and the user who executed it.

Two things to note however:
- the session running into the out-of-locks condition is not necessarily
the session responsible for the problem: there may be another session
that eats up almost all locks, and then someone else needs some locks
but runs into a 1204 error. The latter one gets written to the ASE
errorlog, the former won't. If this is your situation, then all you can
do is run a script or something that keeps an eye on the syslocks table
to see who is consuming all those locks (your script would have to run
continuously so it could cause a performance hit in your server)

- when the statement causing the problem is inside a stored procedure,
the SQL text printed in the ASE errorlog will only show the the call of
the stored proc, not the statement inside the proc that is consuming all
those locks.


Lastly, your problem could also originate from a coding error whereby a
transaction is not properly closed with commit or rollback, thus leaving
it open and accumulate locks until all locks have been used up. This
could be difficult to diagnose. Keep an eye on systransactions to see if
that may be happening.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


Jason L. Froebe [TeamSybase] Posted on 2010-09-21 12:54:35.0Z
From: "Jason L. Froebe [TeamSybase]" <jason.froebe@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Finding Query causing locks to be used up
References: <4c984a61.6a60.1681692777@sybase.com> <4c9898ef@forums-1-dub>
In-Reply-To: <4c9898ef@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c98ab0b$1@forums-1-dub>
Date: 21 Sep 2010 05:54:35 -0700
X-Trace: forums-1-dub 1285073675 10.22.241.152 (21 Sep 2010 05:54:35 -0700)
X-Original-Trace: 21 Sep 2010 05:54:35 -0700, vip152.sybase.com
Lines: 9
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29576
Article PK: 78806

It may also be possible that 750k locks may simply not be sufficient for
your application(s) / environment. I have several servers at work that
have 2 million locks and more... but then, we have upped the LWM/HWM
lock escalation points to avoid table locks when possible.

row level locking can eat up massive amounts of locks... something to
keep in mind when using DOL and APL tables.

jason


"Mark A. Parsons" <iron_horse Posted on 2010-09-21 17:48:42.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Finding Query causing locks to be used up
References: <4c984a61.6a60.1681692777@sybase.com>
In-Reply-To: <4c984a61.6a60.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c98effa$1@forums-1-dub>
Date: 21 Sep 2010 10:48:42 -0700
X-Trace: forums-1-dub 1285091322 10.22.241.152 (21 Sep 2010 10:48:42 -0700)
X-Original-Trace: 21 Sep 2010 10:48:42 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29582
Article PK: 78811

As Rob mentioned, you can look at periodically running a query against syslocks (or monLocks).

Another option is to keep an eye on monProcessActivity.LocksHeld. This seems to work fairly well in providing a count
of locks held by a spid.

'course, this may not be enough to track down the root cause of your out-of-locks issue. The spid with the highest
count may in fact be the culprit that uses up the last lock ... but why isn't it escalating to a table level lock? To
answer this question you need to know what else is running at the time.

I always recommend my clients have some sort of 24x7 monitoring going on, at least on their production dataservers. I
prefer a mix of continuous a) sp_sysmon (10 or 15 min sessions) plus b) a sysprocesses/monProcessActivity-based
monitoring script that takes snapshots of active spids at 10-second intervals.

Tim wrote:
> Hi ,
> We upgraded to ASE 15.0.3 and have been having ASE has run
> out of locks error almost everyday despite locks set at
> 750000.We have engines.number of worker processes 25
> max parallel degree 1
> max query parallel degree 1
> max scan parallel degree 1
> So not relying on high parallism either
> The lock promotion thresholds are at default values
> row lock promotion HWM 200
> row lock promotion LWM 200
> row lock promotion PCT 100
> So after 200 row locks i believe, table lock should be
> secured if its not conflicting with other existing locks
> .How do we figure out the exact query causing this as a
> bunch of queries are captured everytime in error log.However
> checking the query plan shows index usage. We have all DRL
> tables. Also we run reorg rebuild, update stats and
> recompiles daily