Locking while inserting new rows.

Hi all, In a RISC/6000 F50 under AIX with a Informix OnLine WorkGroup Server database Verion 7.20.UC2, we have a problem inserting a new row in a table. We obtain the followin errors: SQL statement error number -271. Could not insert new row into the table. SYSTEM error number -144. ISAM error: key value locked It is pobible that other rows could be locked, but, How is it posible if this new row doesn't exist? Tables have Row Level Locking and Locking to NO WAIT. How we could solve this problem? All help welcome. Thanks in advance. -- Jorge Palanques Spain Sent via Deja.com http://www.deja.com/ Before you buy.

While inserting the row then there is a good chance a lock exists so this is what you are seeing. What are the indexes on the table?, these will give locking issues while the row is being inserted. Try setting the lock mode to wait 5, this will the allow the engine to keep retrying for 5 seconds before an error is returned. If you can't get a lock within 5 secs and it is supposed to be OLTP(ish) then I'd look for an application design problem jps1x2 wrote: > > Hi all, > > In a RISC/6000 F50 under AIX with a Informix OnLine WorkGroup Server > database Verion 7.20.UC2, we have a problem inserting a new row in a > table. > > We obtain the followin errors: > > SQL statement error number -271. > Could not insert new row into the table. > SYSTEM error number -144. > ISAM error: key value locked> > It is pobible that other rows could be locked, but, How is it posible > if this new row doesn't exist? > Tables have Row Level Locking and Locking to NO WAIT. > How we could solve this problem? > > All help welcome. > Thanks in advance. > > -- > Jorge Palanques > Spain > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #

jps1x2 wrote: > Hi all, > > In a RISC/6000 F50 under AIX with a Informix OnLine WorkGroup Server > database Verion 7.20.UC2, we have a problem inserting a new row in a > table. > > We obtain the followin errors: > > SQL statement error number -271. > Could not insert new row into the table. > SYSTEM error number -144. > ISAM error: key value locked> > It is pobible that other rows could be locked, but, How is it posible > if this new row doesn't exist? Locking contention during INSERTs is actually to do with the indexes on the table (in fact, that's what "key value locked" in your ISAM error means). For example, to check uniqueness of the primary key during the INSERT, Informix has to compare it with other index values. However, if a competing session has just inserted a row, that index value is locked out, causing your original session to fail with a -271/-144. > > Tables have Row Level Locking and Locking to NO WAIT. The problem should go away if you allow your process to wait a bit for the competing process to commit (and thus free its index rows for examination). Do that by using the command SET LOCK MODE TO WAIT 2. If you have a slow machine or a lot of competing processes trying to insert rows with primary keys (or unique index columns) close in value to each other, you may have to bump up 2 a bit. HTH, Rudy