locking error (problem)

Hi guys, Sorry for this stupid question, but I’m searching now for several hours and I don’t understand it. My situation is: IDS 9.4 I’m doing a “begin work”…updates or writes some data in a table….. Another guy uses the same program, doing also a ‘begin work”….updates or writes some other data to another record in that table….and he gets the error SQL statement error number -244 Could not do a physical-order read to fetch next row SYSTEM error number -107 ISAM error: record is locked. I don’t see why?? And how can I arrange this. Thanks. Danny

Hi Danny, The problem is that the second user probably is investigating the rows that the first user changed. This can be avoided by using more indexes. Normally sequential scans result a lot in this behaviour. With commited read all rows that are browsed through are examined for locks. And the row must be read to see if it satisfies the where clause. Kidn regards, Rob Prop "DANNY DE KOSTER" <ddk@fidelity-soft.be> Sent by: ids-bounces@iiug.org 06-02-2008 13:52 Please respond to ids@iiug.org To ids@iiug.org cc Subject locking error (problem) [11176] Hi guys, Sorry for this stupid question, but I’m searching now for several hours and I don’t understand it. My situation is: IDS 9.4 I’m doing a “begin work”…updates or writes some data in a table….. Another guy uses the same program, doing also a ‘begin work”….updates or writes some other data to another record in that table….and he gets the error SQL statement error number -244 Could not do a physical-order read to fetch next row SYSTEM error number -107 ISAM error: record is locked. I don’t see why?? And how can I arrange this. Thanks. Danny ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

On 06/02/2008, DANNY DE KOSTER <ddk@fidelity-soft.be> wrote: > Hi guys, > > Sorry for this stupid question, but I'm searching now for several hours and I > don't understand it. > > My situation is: > > IDS 9.4 > > I'm doing a "begin work"…updates or writes some data in a table….. > > Another guy uses the same program, doing also a 'begin work"….updates or > writes some other data to another record in that table….and he gets the error > > SQL statement error number -244 > > Could not do a physical-order read to fetch next row > > SYSTEM error number -107 > > ISAM error: record is locked.> > I don't see why?? And how can I arrange this. > > Thanks. > Danny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > Danny The table you are using, is it ROW level locking or PAGE level locking. If the latter then the first session will be locking more than just the records being updated. Try changing the locking level on the table to ROW and test again. SET LOCK MODE TO WAIT will also help stop the session failing due to alocked record but will stop one session till the other has completed. You can see sessions waiting for locked records with onstat -u. Keith

Rob, The first and the second user are dealing other data.... and Keith, ALL my tables are ROW level locking. Perhaps I need so see that I try first to update a particular row and when it doesn't exist, I do an insert. I do not try to lock first my row before the update. So the second does the same thing.....perhaps he stucks on the insert of the first one??? Thanks. Danny

Hi, You ran into a lock (either page or row lock). In the reading session, the read behaviour can be controlled by setting the isolation level. E.g. we mostly use "set isolation to dirty read" to read also uncommitted data without running into a lock. Other isolation levels are defined in the manual. Marcus -----Original Message----- From: DANNY DE KOSTER [mailto:ddk@fidelity-soft.be] Sent: Wednesday, February 06, 2008 1:47 PM To: ids@iiug.org Subject: locking error (problem) [11176] Hi guys, Sorry for this stupid question, but Im searching now for several hours and I dont understand it. My situation is: IDS 9.4 Im doing a begin workupdates or writes some data in a table.. Another guy uses the same program, doing also a begin work.updates or writes some other data to another record in that table.and he gets the error SQL statement error number -244 Could not do a physical-order read to fetch next row SYSTEM error number -107 ISAM error: record is locked. I dont see why?? And how can I arrange this. Thanks. Danny ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Danny, I believe, that your app/script needs to query some data before doing the insert and that it runs into the lock on this statement. Is that possible, or does your app/script really only insert data ? Marcus -----Original Message----- From: DANNY DE KOSTER [mailto:ddk@fidelity-soft.be] Sent: Wednesday, February 06, 2008 2:24 PM To: ids@iiug.org Subject: Re: locking error (problem) [11182] Rob, The first and the second user are dealing other data.... and Keith, ALL my tables are ROW level locking. Perhaps I need so see that I try first to update a particular row and when it doesn't exist, I do an insert. I do not try to lock first my row before the update. So the second does the same thing.....perhaps he stucks on the insert of the first one??? Thanks. Danny ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

On 06/02/2008, DANNY DE KOSTER <ddk@fidelity-soft.be> wrote: > Rob, > > The first and the second user are dealing other data.... > > and Keith, ALL my tables are ROW level locking. > > Perhaps I need so see that I try first to update a particular row and when it > doesn't exist, I do an insert. I do not try to lock first my row before the > update. So the second does the same thing.....perhaps he stucks on the insert > of the first one??? > > Thanks. > Danny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > Danny Second alternative is that there is an index lock somewhere. You say the first session is inserting or updating data. Is the data being inserted/changed to adjacent 'key wise' on any of the indexes to any data being accessed by the second session? From recollection of an internals course, an insert/update will lock the key value of each index being changed, and also the key value immediately to either side of it in order to ensure index integrity. More indexes, more potential problems. I do not believe there is any way to amend this behaviour, either LOCK MODE WAIT in the session to pause until the first completes, or reduce the length of time the record is locked for update. Keith

Perhaps something usefull.... Before I start my program, my table contains NO rows...... So the programs first does 'update .....' but founds nothing....so I insert a row. The second user does the same...... Is the second user falling over the insert of the first one?? Danny

Keith, I even dropped my indexes. Still the problem.... Danny

DANNY DE KOSTER wrote: > Perhaps something usefull.... > > Before I start my program, my table contains NO rows...... > > So the programs first does 'update .....' but founds nothing....so I insert a > row. > > The second user does the same...... Is the second user falling over the insert > of the first one?? > Danny, here's what's happening. Your table is empty so even if you had updated statistics on the table the stats will tell the optimizer that the table is empty (and it will guess that it's really just nearly so) so the query plan of hte update, if you SET EXPLAIN it, will include a sequential scan on the table to find the row you are trying to find and update. The second user finds that the only row in the table, or after a while at least one of the rows, is locked so it returns an SQL error -244 with an ISAM of -107 physical row is locked. That's how I know for sure that an indexed read wasn't attempted, the ISAM error would have been different. You have several problems here. For the first there's a simple fix, already mentioned. Your sessions are returning an error immediately when they encounter a locked row or index key. The fix is to SET LOCK MODE TO WAIT <Nsecs> in each user session immediately after connecting to the database. That will cause the session that encounters a lock to wait for Nsecs seconds before returning an error (the SQL error will be a lock timeout error then instead of a -244). As long as the first transaction is committed or rolled back withing Nsecs seconds after the second user attempts the read, all will be well. Second problem, attempting the update first is a good strategy if at least 1/3 of the actual transactions will end up being updates and 2/3 or less end up being inserts. When more than about 2/3 of the transactions are inserts, trying the insert first will be faster. You can try to code the app to dynamically switch between trying the insert first and trying the update first depending on how the previous N transactions ended up being completed. This will give you a robust application that will perform well whether the data needs to be added or updated. Third problem, maybe, is that you may be holding the locks too long be making a single transaction for many many rows. This will tend to greatly reduce concurrency in the applications and if you tried to run two or more of these same large transaction applications, even SET LOCK MODE TO WAIT won't help. Carefully design your apps for concurrency. Art S. Kagel Oninit ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

Art, First of all thanks for your comment....off course all the others also manny thanks. What I have done now..... I do first of all an insert....chech on status....eventually do an update. Then the first and second user can work normally. But a bit later in my program I did an update again...there is still fails. I now I need to minimize a transaction but in this case the user needs to control an invoice with changes of their price, etc..... So it can take a time before they finish there job because at the same time they need to pick up the phone and help there customers, etc... They needed the possibility at the end of the control not to accept there modifications. So I thought to use rollback work to not accept there modifications. Any other ideas? Or working with tempory tables. Danny

Hi Danny, Before starting to completely re-writing your application. Is it an option to upgarde to IDS 11.10. In this version there is a new isolation level called commited read read last commited. With this new isolation level it is guaranteed that writers don't block readers. This can solve the whole issue I think... Kind regards, Rob Prop "DANNY DE KOSTER" <ddk@fidelity-soft.be> Sent by: ids-bounces@iiug.org 06-02-2008 15:46 Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: locking error (problem) [11190] Art, First of all thanks for your comment....off course all the others also manny thanks. What I have done now..... I do first of all an insert....chech on status....eventually do an update. Then the first and second user can work normally. But a bit later in my program I did an update again...there is still fails. I now I need to minimize a transaction but in this case the user needs to control an invoice with changes of their price, etc..... So it can take a time before they finish there job because at the same time they need to pick up the phone and help there customers, etc... They needed the possibility at the end of the control not to accept there modifications. So I thought to use rollback work to not accept there modifications. Any other ideas? Or working with tempory tables. Danny ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

On 06/02/2008, DANNY DE KOSTER <ddk@fidelity-soft.be> wrote: > Art, > > First of all thanks for your comment....off course all the others also manny > thanks. > > What I have done now..... > > I do first of all an insert....chech on status....eventually do an update. > Then the first and second user can work normally. > But a bit later in my program I did an update again...there is still fails. > > I now I need to minimize a transaction but in this case the user needs to > control an invoice with changes of their price, etc..... So it can take a time > before they finish there job because at the same time they need to pick up the > phone and help there customers, etc... > They needed the possibility at the end of the control not to accept there > modifications. So I thought to use rollback work to not accept there > modifications. > > Any other ideas? Or working with tempory tables. > > Danny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > Danny You're getting these issues with 2 concurrent users, when you get to 10, 15, 30 or higher they will multiply many times. I think you need to rethink your application design. In which language are you coding? You may need to look at some really horrible machanisms for controlling this, thus:-- Read all the detail of an Invoice into application memory structures and set a 'soft' lock flag (perhaps with date, time and user as well) on the Invoice 'header' record, this is checked by other sessions and they refuse to open it for update if set. Change your memory invoice as required (or not) and then write the changes back to the database (or not) and reset the 'soft' lock. you will also need a small program to reset the 'soft' lock where sessions have terminated untidily. Keith

Keith, We are using Informix 4GL. I was just thinking of using temporary tables to put the modified things in it and than at the end treath all the records at once. Temporary tables because lots of changes can happen during the control of the invoice. The invoice it self is already loaded into memory. Danny

DANNY DE KOSTER wrote: > Art, > > First of all thanks for your comment....off course all the others also manny > thanks. > > What I have done now..... > > I do first of all an insert....chech on status....eventually do an update. > Then the first and second user can work normally. > But a bit later in my program I did an update again...there is still fails. > > I now I need to minimize a transaction but in this case the user needs to > control an invoice with changes of their price, etc..... So it can take a time > before they finish there job because at the same time they need to pick up the > phone and help there customers, etc... > They needed the possibility at the end of the control not to accept there > modifications. So I thought to use rollback work to not accept there > modifications. > > Any other ideas? Or working with tempory tables. > No. You need to modify your applications to use what's known as an optimistic locking protocol. I've posted something like this once or thrice before, but here are the steps: 1- fetch the data, if it exists, without locking (so no FOR UPDATE clause in the SELECT). 2- present the fetched data or a blank new record to the user for modification IN MEMORY to a copy of the original record ONLY - it's critical to keep an unmodified copy of the data. 3- ONLY after all data entry or modifications to all parent and child records have been made, then refetch the record using the keys on screen - so if it's a new record you just pretend it already existed but was all blank - into a separate memory structure. This fetch is made inside a transaction with a lock - so it should include a FOR UPDATE clause. 4- Compare the refetched data to the original unmodified data, and if there have been no changes by another user - that's the optimism part - you optimistically assume that only one user will be working on any record at a time - then you update or insert the row or rows and COMMIT WORK immediately. Rows are only locked momentarily and transactions are never delayed while the user answers the phone, goes to the rest room, or out to lunch. If the records do not match, then you ROLLBACK the transaction freeing the locks even faster and tell the user that someone else has modified the record he/she was trying to work on. Now, to simplify all this and ease both the memory needed to handle the three copies of the data and the time spend verifying that there hasn't been an interfering update, you can use a timestamp column in each table, or at least in the parent table, (defined as YEAR TO FRACTION(3) and with USEOSTIME turned on in the ONCONFIG file) that is always updated when the record (or one of its children) is modified. This can be done with a DEFAULT CURRENT YEAR TO FRACTION(3) option on the column so that the column is auto-populated at INSERT time and by an UPDATE TRIGGER on the table and/or any of it's children that will update the timestamp column in the row (or the parent row) when one of those records is modified. Then you ONLY have to compare the original timestamp to the one you refetch just before attempting the updates. This is guaranteed to work EVEN if users modify the rows outside of your application since the timestamps are being maintained by the engine. Optimistic locking works because in the real world of business, it is rare for more than one user to modify the same records at the same time. It might happen if say two contacts at a customer site contacted your company at the same instant both wanting to modify the same order at the same moment, so the software handles it, but it is safe to assume that this will be rare enough to not interfere with the normal daily work flow. Art S. Kagel > Danny > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

Me again, I had 2 indexes on that table. first index was a unique one. The one I used to do my update. the second was on a column nothing to do with my update. Now I deleted the second index....and it works!! So it was that index who blocked me?? Danny

On 06/02/2008, DANNY DE KOSTER <ddk@fidelity-soft.be> wrote: > Me again, > > I had 2 indexes on that table. > > first index was a unique one. The one I used to do my update. > the second was on a column nothing to do with my update. > Now I deleted the second index....and it works!! So it was that index who > blocked me?? > > Danny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > Danny And you said you dropped the indexes !! :-)) Does the second index have a field that is likely to have the same contents in both records? Can you drop, or amend this index (permanently)? keith

On 06/02/2008, DANNY DE KOSTER <ddk@fidelity-soft.be> wrote: > Keith, > > We are using Informix 4GL. > > I was just thinking of using temporary tables to put the modified things in it > and than at the end treath all the records at once. > > Temporary tables because lots of changes can happen during the control of the > invoice. The invoice it self is already loaded into memory. > > Danny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > Danny Temp tables would be OK, however if your users have a tendancy to just terminate sessions rather than logging out tidily you could end up with a temp space 'leakage' which will require a database 'bounce' to clear. just be aware of the advantages/disadvantages of both ways, test with many sessions and see what works for your situation. keith

Indeed I dropped both the indexes!! Now I only dropped the second one. The second has indeed the same contents. I find it strange (but who am I) that Informix struggles with that.