Re: SQR v2.5 & Sybase v12.0 out of locks

[Arjan Hoornstra <ahoor@YAHOO.COM>]

First a question: did this happen once or is it a reproducable error?

If it happens sometimes but not always, here's a thing:

It is possible that Sybase is correct. What you need to know if there
were any concurrent processes running at the time. The main difference
between Sybase 11.0.3 and 12.0 is the availability of pagelocking (this
whas new in Sybase 11.5).
Suppose you do a select on a large table which results in shared
pagelocks of all pages that apply, and this may be a lot of pages. Then
your update comes, which usually results in a table lock. So then
Sybase has to 'promote' the shared pagelocks to a tablelock to prevent
the update from updating data you just selected resulting in dirty
data. This promoting results in temporary a lot more locks before the
actual table lock occurs, and this may overflow your 'number of locks'
setting.
Also, it is possible that the 'large select' I was talking about is
your sqr program itself (in this case it will happen all the time)

Solutions for this problem:
- make sure you run the sqr when there is no other proces running
- try the 'no holdlock' option in the select clause (not if you are
running within an explicit transaction)
- if you do a large select which then updates row by row, try to do the
select first, write the updateble data in a temptable and do the update
at once after the entire select has finished (look out for your
transactionlog)

!! do NOT just set the 'number of locks' to a higher value since this
will decrease memory and probably not help at all.

Good luck,

Arjan


--- Tony Martino <Tony.Martino@TRW.COM> wrote:
> We are currently running some old legacy SQR v2.5 programs under DOS
> 6.2 (not Windows) accessing Sybase SQL Server v11.0.3.2 on Windows NT
> 4.0 using DB-Library.  We are trying to upgrade to Sybase Adaptive
> Server Enterprise v12.0.0.2 on Windows 2000, but one of our SQR
> programs is getting an error that SQL Server has run out of locks
> (this can't be true, because the new server has twice as many locks
> as the old server, and the program only tried to update one row).
> Can anyone provide any assistance as to what is causing the error?
> Did this old unsupported version of SQR finally "break" with the
> Sybase upgrade?  Is there a simple supported version of SQR that we
> could try to see if this program will work without any rewrite?


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/