informix: 503 too many tables locked
An app on Informix 11.70FC3/AIX 6 began failing with error 503 "too many tables locked" after its list of tables to LOCK TABLE exclusively grew to 33. Replies confirmed the documented hard limit of 32 table locks per user, with no way around it other than submitting an IBM feature request. Others questioned the design, suggesting simply using BEGIN WORK or locking the whole database instead. The poster accepted the limit and said he'd push to have the application's locking logic dropped.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone! since couple of days one of our applications has started to recive this error message. The only information I could find about this error is that Informix user has a hard coded limit of the number of tables he/she can lock. It is 32 tables. Is it really so? Can we some how over come this limit? We are using Informix 11.70FC3 on AIX 6. The application that is getting this error functions in this way: It starts and tries to get an exclusive lock on the list of the tables (there are 33 tables now in this list). If it gets it, some INSERT operations begin. If not, it breaks. Thanks in advance for some help! Best regards! Oleg Popelyaev
Hello, Oleg. You should check for your machine notes file, in your product documentation, and look for recommended kernel tunning for parameters (eg fopen, open files, etc...). But you might also consider an application review, it´s not a normal way that a single process locks so much tables, isn´t it? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: o.registrator@gmail.com > Subject: informix: 503 too many tables locked [31492] > Date: Tue, 24 Sep 2013 08:14:11 -0400 > > Hi everyone! > > since couple of days one of our applications has started to recive this error > message. The only information I could find about this error is that Informix > user has a hard coded limit of the number of tables he/she can lock. It is 32 > tables. Is it really so? Can we some how over come this limit? > > We are using Informix 11.70FC3 on AIX 6. > > The application that is getting this error functions in this way: > It starts and tries to get an exclusive lock on the list of the tables (there > are 33 tables now in this list). If it gets it, some INSERT operations begin. > If not, it breaks. > > Thanks in advance for some help! > > Best regards! > Oleg Popelyaev > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
WHY in the name of all that's holy would you want an exclusive lock on 33 tables before starting insert operations? Either just BEGIN WORK or lock the whole friggin database dude! No sympathy from me. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 24, 2013 at 8:14 AM, OLEG POPELYAEV <o.registrator@gmail.com>wrote: > Hi everyone! > > since couple of days one of our applications has started to recive this > error > message. The only information I could find about this error is that > Informix > user has a hard coded limit of the number of tables he/she can lock. It is > 32 > tables. Is it really so? Can we some how over come this limit? > > We are using Informix 11.70FC3 on AIX 6. > > The application that is getting this error functions in this way: > It starts and tries to get an exclusive lock on the list of the tables > (there > are 33 tables now in this list). If it gets it, some INSERT operations > begin. > If not, it breaks. > > Thanks in advance for some help! > > Best regards! > Oleg Popelyaev > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d14ca7b94bf04e72174fc
Thanks Alexandre! Thanks Art! I know, that the logic of this application is... ahm... But it is an old application that is functioning since may be 10 years (I don't know exactly, it was written and implemented long before me). And I'm afraid nobody now really wants to make a big changes there. I've talked allready with responsible persons and tried to convince them to turn the locking off. Best regards Oleg Popelyaev
Original post: Hi everyone! since couple of days one of our applications has started to recive this error message. The only information I could find about this error is that Informix user has a hard coded limit of the number of tables he/she can lock. It is 32 tables. Is it really so? Can we some how over come this limit? We are using Informix 11.70FC3 on AIX 6. The application that is getting this error functions in this way: It starts and tries to get an exclusive lock on the list of the tables (there are 33 tables now in this list). If it gets it, some INSERT operations begin. If not, it breaks. Thanks in advance for some help! Best regards! Oleg Popelyaev Response: From this link http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.adref. doc%2Fids_adr_0722.htm you can see that the documented maximum number of table locks per user is 32. There isn't any way around that limit. If you would like the limit increased, you can submit a feature request using the following link http://www.ibm.com/developerworks/rfe Jacques Renaut IBM Informix Advanced Support APD Team
Thanks Jacques! Best regards Oleg Popelyaev
Hi, There is in fact a limit of the number of tables that a *single user* can lock using LOCK TABLE. The limit is 32. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 24/09/13 14:14, OLEG POPELYAEV a écrit : > Hi everyone! > > since couple of days one of our applications has started to recive this error > message. The only information I could find about this error is that Informix > user has a hard coded limit of the number of tables he/she can lock. It is 32 > tables. Is it really so? Can we some how over come this limit? > > We are using Informix 11.70FC3 on AIX 6. > > The application that is getting this error functions in this way: > It starts and tries to get an exclusive lock on the list of the tables (there > are 33 tables now in this list). If it gets it, some INSERT operations begin. > If not, it breaks. > > Thanks in advance for some help! > > Best regards! > Oleg Popelyaev > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >