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.

Error 1204 Locks

6 posts in Administration Last posting was on 2005-06-13 09:51:01.0Z
Philip Washington Posted on 2005-06-10 04:36:03.0Z
From: Philip Washington <phwashington@comcast.net>
User-Agent: Mozilla Thunderbird 0.8 (X11/20040913)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.administration
Subject: Error 1204 Locks
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 67.187.118.186
X-Original-NNTP-Posting-Host: 67.187.118.186
Message-ID: <42a918b3$1@forums-1-dub>
Date: 9 Jun 2005 21:36:03 -0700
X-Trace: forums-1-dub 1118378163 67.187.118.186 (9 Jun 2005 21:36:03 -0700)
X-Original-Trace: 9 Jun 2005 21:36:03 -0700, 67.187.118.186
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17243
Article PK: 35430

After setting up a sybase server ASE_12.5.2 on a linux server we ran
some tests and receive the following error.

Warning: sybase_query(): Sybase: Server message: SQL Server has run out
of
LOCKS. Re-run your command when there are fewer active users, or contact
a
user with System Administrator (SA) role to reconfigure SQL Server with
more
LOCKS. (severity 17, procedure N/A)

[ Okay let me preface this with I don't know much if anything about how
Sybase handles locks]

At the time of the error only 4 people were using the database (only 3
were operating code that affect this table) through a
PHP interface and the SA says that the number of locks never went above
300. Currently Sybase is set for 5000.

The logs at the time shows
Error 1204 Error 1204, Severity 17, State 2 o
. This is the second time this error has occurred. It does appear to
be happening at roughly the same time in the code though, but I would
guess that the periodicity of the problem is only once in 50 times this
code is ran. Any ideas on what to look for? Could this actually be a
lock contention where the same record is trying to be locked by 2
different transactions? I really would be surprised if we actually had
5000 locks created by 4 users, which is what Error 1204 indicates.

In both cases I believe that an update on a recordset was trying to
be performed.

After looking at the code and operation I don't see where any 2 users
could be locking the same record. But there is a page where multiple
records are updated individually and then one of these records is
updated again in a following sql statement. Could a lock still exist
for that recordset.


I'll try and summarize the code

1. Update valuee in fields for Records with LotNO = 1011

2. Select Records with this value LotNo = 1011( 15 may be returned )

3. Update some of these records by incrementing the LineNo by 1

4 .Insert a new record with LineNo=1

LineNo is not a primary key or unique index.



I think that in step 3 is where the problem is occurring. Is it
possible that a lock is still on one of the recordsets and creating this
error.

If it is a lock, is there a way I could send a message to the server
saying, hey I really am finished with those records drop the lock or
make sure you are finished with the records. I am kind of pressed for
time and hoping I don't have to resort to a stored procedure right now.
Even with a stored procedure I would need some help in making sure I
didn't create the same problem.


Navneet Chola Posted on 2005-06-10 05:48:53.0Z
From: "Navneet Chola" <nachola2003@yahoo.co.uk>
Newsgroups: sybase.public.ase.administration
Organization: 203.99.212.25
References: <42a918b3$1@forums-1-dub>
X-Newsreader: AspNNTP 1.50 (Takeshi Eto)
Subject: Re: Error 1204 Locks
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: web110.discountasp.net
X-Original-NNTP-Posting-Host: web110.discountasp.net
Message-ID: <42a929c5$1@forums-1-dub>
Date: 9 Jun 2005 22:48:53 -0700
X-Trace: forums-1-dub 1118382533 64.79.161.40 (9 Jun 2005 22:48:53 -0700)
X-Original-Trace: 9 Jun 2005 22:48:53 -0700, web110.discountasp.net
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17244
Article PK: 35429

Dear Philip ,

This is error happens when ASE runs out of the Locks.
Locks are handled internally by ASE. It depends upon the type of transaction
you are doing . ASE decides what type of locks would be required .
Well , you need to look into the code of the procedure .
For temporary solution ask the SA to increase the number of locks by another 2k
or 3k . This does not require much chunck of memory .

1. Do you have a index on LineNo or does your update covers the Index covering .
if No , you should have one . Bcoz what happens is when you try to update based
on line number , it tries to hold lock on different pages before escalating that
to the table lable lock ( Table scan will make it happen ).
In that process , number of locks run out .

you can use

sp_setpglockpromote {"database"|"table"},objname, new_lwm, new_hwm, new_pct

to decide upon when to escalate the locks to table label . you have ASE level
settings for them also . ask your SA if he can look into the same.

try some ways that your transaction is small and the updates have index covering
.

Hope this should help you .

Regards
Navneet

>
>
>I'll try and summarize the code
>
>1. Update valuee in fields for Records with LotNO = 1011
>
>2. Select Records with this value LotNo = 1011( 15 may be returned )
>
>3. Update some of these records by incrementing the LineNo by 1
>
>4 .Insert a new record with LineNo=1
>
>LineNo is not a primary key or unique index.
>
>
>
>I think that in step 3 is where the problem is occurring. Is it
>possible that a lock is still on one of the recordsets and creating this
>error.
>
>If it is a lock, is there a way I could send a message to the server
>saying, hey I really am finished with those records drop the lock or
>make sure you are finished with the records. I am kind of pressed for
>time and hoping I don't have to resort to a stored procedure right now.
>Even with a stored procedure I would need some help in making sure I
>didn't create the same problem.
>
>


mpeppler Posted on 2005-06-10 08:57:58.0Z
Sender: 3429.42a954c4.1804289383@sybase.com
From: mpeppler@peppler.org
Newsgroups: sybase.public.ase.administration
Subject: Re: Error 1204 Locks
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42a95616.3434.1681692777@sybase.com>
References: <42a918b3$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Jun 2005 01:57:58 -0700
X-Trace: forums-1-dub 1118393878 10.22.241.41 (10 Jun 2005 01:57:58 -0700)
X-Original-Trace: 10 Jun 2005 01:57:58 -0700, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17245
Article PK: 35431


> After setting up a sybase server ASE_12.5.2 on a linux
> server we ran some tests and receive the following error.
>
> Warning: sybase_query(): Sybase: Server message: SQL
> Server has run out of
> LOCKS. Re-run your command when there are fewer active
> users, or contact a
> user with System Administrator (SA) role to reconfigure
> SQL Server with more
> LOCKS. (severity 17, procedure N/A)
>

To add to what Navneet says - you can use sp_monitorconfig
to see how many locks (and other resources) are currently in
use, and how many have been used (i.e. max number used)
since the server was booted.

As to configuration - 5000 is a fairly small number of locks
and increasing this to 20000 or so won't affect memory use
very much.

Michael


Philip Washington Posted on 2005-06-10 18:22:28.0Z
From: Philip Washington <phwashington@comcast.net>
User-Agent: Mozilla Thunderbird 1.0.2-1.4.1.centos4 (X11/20050323)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.administration
Subject: Re: Error 1204 Locks
References: <42a918b3$1@forums-1-dub> <42a95616.3434.1681692777@sybase.com>
In-Reply-To: <42a95616.3434.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 64.221.219.104.ptr.us.xo.net
X-Original-NNTP-Posting-Host: 64.221.219.104.ptr.us.xo.net
Message-ID: <42a9da64$1@forums-1-dub>
Date: 10 Jun 2005 11:22:28 -0700
X-Trace: forums-1-dub 1118427748 64.221.219.104 (10 Jun 2005 11:22:28 -0700)
X-Original-Trace: 10 Jun 2005 11:22:28 -0700, 64.221.219.104.ptr.us.xo.net
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17250
Article PK: 35437


mpeppler@peppler.org wrote:
>>After setting up a sybase server ASE_12.5.2 on a linux
>>server we ran some tests and receive the following error.
>>
>>Warning: sybase_query(): Sybase: Server message: SQL
>>Server has run out of
>>LOCKS. Re-run your command when there are fewer active
>>users, or contact a
>>user with System Administrator (SA) role to reconfigure
>>SQL Server with more
>>LOCKS. (severity 17, procedure N/A)
>>
>
>
> To add to what Navneet says - you can use sp_monitorconfig
> to see how many locks (and other resources) are currently in
> use, and how many have been used (i.e. max number used)
> since the server was booted.
>
> As to configuration - 5000 is a fairly small number of locks
> and increasing this to 20000 or so won't affect memory use
> very much.
>
> Michael

Appreciate mpeppler's and Navneet's advice and information. I'll
definitely ask SA to bump up the lock numbers and start monitoring.
See if I can't tweak the code.

I included a little better summary of the information and included the
index on this table. If you have time to look at this and see if there
is something here that warrants a
" Hey knuckle head your doing this part wrong or this would be better"
I'd appreciate it.

I'm learning as I step away from SQL Server to ASE that I need to learn
a lot more about what the system is doing (which is a good thing) and I
appreciate the help.

The table that is being manipulated looks something like this

create table LotLineInfo(
LineIDNo numeric (10, 0) identity ,
LotID int NULL,
LineNo int NULL,
Step varchar (30) NULL,
StepTime varchar (10) NULL,
StepDuration varchar (10) NULL,
ModUser varchar (25) NULL,
ModDate datetime NULL,
AddUser varchar (25) NULL,
AddDate datetime NULL )


Index 'nonclustered located on default' on LineIDNo,LotID

<loop depending on data 1-15, once for each line >
Update LotLineInfo set Step='$Step',
StepTime='$StepTime',
StepDuration='$StepDuration',
ModUser='$ModUser',
ModDate='$ModDate'
where LotNo = '$LotNo' and LineNo ='$LineNo';
</loop>

Select * from LotLineInfo where LotID = 99999 order by LineNo;

<loop depending on data returned from select usually 1-15 recordsets>
$LineNo = $rs['LineNo'] + 1;
Update LineNo='$LineNo' where LineIDNo = $rs['LineIDNo'];
</loop>

Insert into LotLineInfo (LotNo,LineNo) values ($LotNo,1);


There are different variations of this which allow inserting lines in
the middle and copying lines. I did note that in that first update I
was using LotID and LineNo, need to check the code and see why I wasn't
using LineIDNo.

Thanks Again


Navneet Chola Posted on 2005-06-13 09:50:31.0Z
From: "Navneet Chola" <nachola2003@yahoo.co.uk>
Newsgroups: sybase.public.ase.administration
Organization: 203.99.212.25
References: <42a918b3$1@forums-1-dub> <42a95616.3434.1681692777@sybase.com> <42a9da64$1@forums-1-dub>
X-Newsreader: AspNNTP 1.50 (Takeshi Eto)
Subject: Re: Error 1204 Locks
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: web110.discountasp.net
X-Original-NNTP-Posting-Host: web110.discountasp.net
Message-ID: <42ad56e7$1@forums-1-dub>
Date: 13 Jun 2005 02:50:31 -0700
X-Trace: forums-1-dub 1118656231 64.79.161.40 (13 Jun 2005 02:50:31 -0700)
X-Original-Trace: 13 Jun 2005 02:50:31 -0700, web110.discountasp.net
Lines: 95
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17255
Article PK: 35441

Hi Philip ,

if you can create nonclustered Index on LotNo , LineNo & another on
LineNo,LineIDNo .
This will help you . But before that you should keep in mind these
indexes has its overhead . Check how much time its taking
to insert and other processes too.

Regards
Navneet


On 10 Jun 2005 11:22:28 -0700,
in sybase.public.ase.administration

Philip Washington <phwashington@comcast.net> wrote:
>mpeppler@peppler.org wrote:
>>>After setting up a sybase server ASE_12.5.2 on a linux
>>>server we ran some tests and receive the following error.
>>>
>>>Warning: sybase_query(): Sybase: Server message: SQL
>>>Server has run out of
>>>LOCKS. Re-run your command when there are fewer active
>>>users, or contact a
>>>user with System Administrator (SA) role to reconfigure
>>>SQL Server with more
>>>LOCKS. (severity 17, procedure N/A)
>>>
>>
>>
>> To add to what Navneet says - you can use sp_monitorconfig
>> to see how many locks (and other resources) are currently in
>> use, and how many have been used (i.e. max number used)
>> since the server was booted.
>>
>> As to configuration - 5000 is a fairly small number of locks
>> and increasing this to 20000 or so won't affect memory use
>> very much.
>>
>> Michael
>Appreciate mpeppler's and Navneet's advice and information. I'll
>definitely ask SA to bump up the lock numbers and start monitoring.
>See if I can't tweak the code.
>
>I included a little better summary of the information and included the
>index on this table. If you have time to look at this and see if there
>is something here that warrants a
>" Hey knuckle head your doing this part wrong or this would be better"
>I'd appreciate it.
>
>I'm learning as I step away from SQL Server to ASE that I need to learn
>a lot more about what the system is doing (which is a good thing) and I
>appreciate the help.
>
>The table that is being manipulated looks something like this
>
>create table LotLineInfo(
> LineIDNo numeric (10, 0) identity ,
> LotID int NULL,
> LineNo int NULL,
> Step varchar (30) NULL,
> StepTime varchar (10) NULL,
> StepDuration varchar (10) NULL,
> ModUser varchar (25) NULL,
> ModDate datetime NULL,
> AddUser varchar (25) NULL,
> AddDate datetime NULL )
>
>
>Index 'nonclustered located on default' on LineIDNo,LotID
>
><loop depending on data 1-15, once for each line >
>Update LotLineInfo set Step='$Step',
>StepTime='$StepTime',
>StepDuration='$StepDuration',
>ModUser='$ModUser',
>ModDate='$ModDate'
>where LotNo = '$LotNo' and LineNo ='$LineNo';
></loop>
>
>Select * from LotLineInfo where LotID = 99999 order by LineNo;
>
><loop depending on data returned from select usually 1-15 recordsets>
>$LineNo = $rs['LineNo'] + 1;
>Update LineNo='$LineNo' where LineIDNo = $rs['LineIDNo'];
></loop>
>
>Insert into LotLineInfo (LotNo,LineNo) values ($LotNo,1);
>
>
>There are different variations of this which allow inserting lines in
>the middle and copying lines. I did note that in that first update I
>was using LotID and LineNo, need to check the code and see why I wasn't
>using LineIDNo.
>
>Thanks Again


mpeppler Posted on 2005-06-13 09:51:01.0Z
Sender: 7363.42ad5518.1804289383@sybase.com
From: mpeppler@peppler.org
Newsgroups: sybase.public.ase.administration
Subject: Re: Error 1204 Locks
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42ad5705.737c.1681692777@sybase.com>
References: <42a9da64$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Jun 2005 02:51:01 -0700
X-Trace: forums-1-dub 1118656261 10.22.241.41 (13 Jun 2005 02:51:01 -0700)
X-Original-Trace: 13 Jun 2005 02:51:01 -0700, 10.22.241.41
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.administration:17256
Article PK: 35442


> I included a little better summary of the information and
> included the index on this table. If you have time to
> look at this and see if there is something here that
> warrants a " Hey knuckle head your doing this part wrong
> or this would be better" I'd appreciate it.
>
> I'm learning as I step away from SQL Server to ASE that I
> need to learn a lot more about what the system is doing
> (which is a good thing) and I appreciate the help.
>
> The table that is being manipulated looks something like
> this
>
> create table LotLineInfo(
> LineIDNo numeric (10, 0) identity ,
> LotID int NULL,
> LineNo int NULL,
> Step varchar (30) NULL,
> StepTime varchar (10) NULL,
> StepDuration varchar (10) NULL,
> ModUser varchar (25) NULL,
> ModDate datetime NULL,
> AddUser varchar (25) NULL,
> AddDate datetime NULL )
>
>
> Index 'nonclustered located on default' on LineIDNo,LotID
>
> <loop depending on data 1-15, once for each line >
> Update LotLineInfo set Step='$Step',
> StepTime='$StepTime',
> StepDuration='$StepDuration',
> ModUser='$ModUser',
> ModDate='$ModDate'
> where LotNo = '$LotNo' and LineNo ='$LineNo';
> </loop>

If the only index on the table is on LineIDNo,LotID then
this update statement will most likely result in a table
scan, and hence lock the entire table.

> Select * from LotLineInfo where LotID = 99999 order by
> LineNo;

This is also going to be slow, because LotID is the second
column of the index.

>
> <loop depending on data returned from select usually 1-15
> recordsets> $LineNo = $rs['LineNo'] + 1;
> Update LineNo='$LineNo' where LineIDNo = $rs['LineIDNo'];
> </loop>
>
> Insert into LotLineInfo (LotNo,LineNo) values ($LotNo,1);
>
>
> There are different variations of this which allow
> inserting lines in the middle and copying lines. I did
> note that in that first update I was using LotID and
> LineNo, need to check the code and see why I wasn't
> using LineIDNo.

Right.

I guess you need to review what the primary key is for the
table, and then use that consistently.
And make sure that you have an index that can be used for
each of the queries that you use so that each query can be
satisfied with a minimum of IOs.

Michael