Getting Error -271 On Batch Insert Randomly ~ Answered: amber (solid confidence) — Multiple experts converge on page-level locking contention from concurrent inserts as the likely cause and recommend row-level locking; Fernando Nunes also offers a diagnostic assert-fail trap (onmode -I) to pin down the exact ISAM error, but explicitly warns it can cause severe performance degradation and must be tested off production first. The asker's last message says only that he will gather more data.

Advisory only.

Hi, I'm getting error -271 when I'm inserting into this certain table. The primary key used is SERIAL8 and the lock mode is page lock. The error -271 occurs erratically, I can't determine the exact ISAM though since the stored procedure program is only returning the sql error code. It only occurs on production environment and it's quite hard to reproduce. From the things I've mentioned, is there a way that I could determine any possible cause? This is a batch program in which this statement is happening. From googling around, -271 seems to be either a locked table or is the insert producing an equivalent primary key? It's quite weird that we're getting this during Inserts. there is no table locking and the dbspace is still sufficient. Any clues? Thanks Horacio

Btw, the version of Informix is 11.10 THanks

You are probably getting hit with a transient lock from another session. In the session execute SET LOCK MODE TO WAIT nsec; after connecting to the database. That will cause lock requests to hang for nsec seconds and wait for transient locks to be released before returning an error. Art Art S. Kagel 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 Thu, Jan 5, 2012 at 1:59 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > I'm getting error -271 when I'm inserting into this certain table. The > primary > key used is SERIAL8 and the lock mode is page lock. The error -271 occurs > erratically, I can't determine the exact ISAM though since the stored > procedure program is only returning the sql error code. It only occurs on > production environment and it's quite hard to reproduce. > > >From the things I've mentioned, is there a way that I could determine any > possible cause? This is a batch program in which this statement is > happening. > >From googling around, -271 seems to be either a locked table or is the > insert > producing an equivalent primary key? It's quite weird that we're getting > this > during Inserts. there is no table locking and the dbspace is still > sufficient. > > Any clues? > > Thanks > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93405377c22f904b5ccd646

Hi, Thanks for the reply. My question is, there's only inserts. Probably another session is also trying to insert at the same time since this is a multiuser application. Does Informix lock the table during an insert? What are transient locks? Does it matter that i'm using page lock instead of row lock? What can be done to prevent this? Could this be due to the fact that an insert is slow (table is quite large)? Thanks Horacio

You have indicated you are using page level locking. This will prevent two different users from inserting rows on to the same page at the same time. If you have multiple users inserting into the same table you might want to consider row level locking. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic16888.gif) ids-bounces@iiug.org wrote on 01/05/2012 11:29:58 AM: > From: "NATYURAL HORACIO" <horacio.natyural@gmail.com> > To: ids@iiug.org > Date: 01/05/2012 11:30 AM > Subject: Re: Getting Error -271 On Batch Insert Randomly [25829] > Sent by: ids-bounces@iiug.org > > Hi, > > Thanks for the reply. My question is, there's only inserts. Probably another > session is also trying to insert at the same time since this is a multiuser > application. Does Informix lock the table during an insert? What aretransient > locks? Does it matter that i'm using page lock instead of row lock? What can > be done to prevent this? Could this be due to the fact that an insert is slow > (table is quite large)? > > Thanks > Horacio > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Horacio, Row level locking will lock a single row when inserting/updating/ or deleting a row ( and of course the respective index locks for the given row ) So yes, altering the table to row level locking will help avoid lock contention. Think and adjust if needed, row level locking will use more locks then page level locking in almost all cases. The size of a table should not have a large effect on the speed of inserts. However the number of indices on a given table will have a larger effect on insert speed as will the number of referential constraints that will need to be checked when a row is inserted, and any triggered events ( insert triggers) A transient lock is a lock that is there for a short period of time. If the inserts are not in a transaction all locks will be very short lived in time, they will be there only while the updates of the row and indices that support the row are updated, and any time for any insert triggered events that are required to execute, and all checks that are required for referential consistence constraints. For transactions that are under the control of the application, all locks will exist from the time they are created, after a begin work, until the application issues a commit work or rollback work and completes the commit or rollback. The above is a very bland answer. More details on your part will yield more interesting points from my part. George. ids-bounces@iiug.org wrote on 01/05/2012 01:29:58 PM: > From: "NATYURAL HORACIO" <horacio.natyural@gmail.com> > To: ids@iiug.org > Date: 01/05/2012 01:30 PM > Subject: Re: Getting Error -271 On Batch Insert Randomly [25829] > Sent by: ids-bounces@iiug.org > > Hi, > > Thanks for the reply. My question is, there's only inserts. Probably another > session is also trying to insert at the same time since this is a multiuser > application. Does Informix lock the table during an insert? What aretransient > locks? Does it matter that i'm using page lock instead of row lock? What can > be done to prevent this? Could this be due to the fact that an insert is slow > (table is quite large)? > > Thanks > Horacio > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

hi, thanks a lot for your answer. it was very informative. I would get back to you once I gather more data. Thanks! Horacio

Trying to find out the cause of an error without the error is difficult to say the least. And in this case, the error I'm talking about is the ISAM error. -271 just means the INSERT was not done. Yes, locking of key conflict can be the most common issues, but you'll never know... Ther is a possibility, but you should measure all the implications... Informix allows you to "trap" errors for all, or for a specific session. When the defined error occurs, the engine will assert fail (but will keep online). The problems in doing this are: 1- The assert fail generation can cause severe performance degradation for as much as a few minutes 2- You'd need to identify the session ID after it's created and specify it. Asking just for the error is possible, but you may be flooded by assert fails if many of your sessions hit the same error The assert fail should contain enough info to allow you to understand the cause. The way to establish the trap is "onmode -I error,sessionid" error would be 271 sessionid would be the SID for the session running the INSERTs Please note that the correct syntax includes a comma "," and not a space as indicated in "onmode --" (this must be a bug and should be reported and fixed) Don't do this in production without testing it and understanding how it works in a dev/test environment. One way to ease the assert fail generation would be to avoid shared memory dump. But even so it will be slow and cause problems in a busy system. To avoid shared memory creating you could use "onmode -wm DUMPSHMEM=0" (not sure if that's supported on 11.10) But my first advise would be to properly get the ISAM error by changing the code... Regards On Thu, Jan 5, 2012 at 6:59 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > I'm getting error -271 when I'm inserting into this certain table. The > primary > key used is SERIAL8 and the lock mode is page lock. The error -271 occurs > erratically, I can't determine the exact ISAM though since the stored > procedure program is only returning the sql error code. It only occurs on > production environment and it's quite hard to reproduce. > > >From the things I've mentioned, is there a way that I could determine any > possible cause? This is a batch program in which this statement is > happening. > >From googling around, -271 seems to be either a locked table or is the > insert > producing an equivalent primary key? It's quite weird that we're getting > this > during Inserts. there is no table locking and the dbspace is still > sufficient. > > Any clues? > > Thanks > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00235429e1641267c404b5cf160e

I should apologize for answering without refreshing... there were a lot of answers already, and I assumed you had only one session INSERTing. If the are more than one, then, as it was already said, you should use row locking. Regards. On Thu, Jan 5, 2012 at 8:25 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > hi, > > thanks a lot for your answer. it was very informative. I would get back to > you > once I gather more data. > > Thanks! > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c6a6a427c52e904b5cf3aea