RE: lock table & update cursors problems

Manuel, Yes, this appears to be standard behavior. Your options include: 1) Lock the tables in exclusive mode. This will eliminate the need for row level locks. However, it will also prevent other transactions from accessing the tables. 2) Change the tables to page level locking, when you start the update process. Change the tables to row level locking after your processing completes. This technique will reduce the locks required. However, it will require exclusive access to the tables when the ALTER TABLE <table-name> commands are issued. If another task is accessing the table(s), your request to change the lock mode will fail. 3) Break you insert and deletes into multiple, smaller units of work (transactions). Issue a COMMIT WORK after each unit of work. This will reduce the number of locks required for each transaction. Your business requirements will likely dictate which of these options is best for your environment. Rick Bernstein -----Original Message----- From: Manuel A. Daponte Santiago [mailto:mdaponte@prtc.net] Sent: Sunday, January 16, 2000 12:29 PM To: informix-list@iiug.org Subject: lock table & update cursors problems Hi, We have a 4gl program that updates, inserts & deletes more than a million of records in 3 tables, and all the tables in our system have "lock mode row". Our server has 600.000 locks and the memory is scarce so we can't increase that number. The first thing we do after the "begin work" is lock the three tables in "share" mode. Then, we declare the update cursors and start the processing. But the onmode -k shows an increasing number of locks and the process fails with a "no more locks" error. The process is run with no other users logged. Seems that the "update cursor" uses a lock for each row without considering the "lock table" statement. Is this the standard behavior of the engine? We are using Informix Dynamic Server Version 7.31.UC2 on a SCO Release = 3.2v5.0.4 Thanks in advance for your help -- Manuel A. Daponte Santiago Systems Consultant, ICP