update - locking
Danny hit ISAM error -107 (record is locked) when updating a row in a row-level-locked table on IDS 9.40/Linux: another session held a lock on a different row, but the update still tried to read it. Respondents explained this happens when the engine does a sequential scan rather than indexed access, and suggested checking SET EXPLAIN/onstat -k, ensuring a usable index and current statistics/OPTCOMPIND, using SET LOCK MODE TO WAIT n, dirty read, or (in 11.x) last committed read. They stressed that long-held locks usually indicate poor application design. Danny accepted this and said he would rework his code to shorten lock times; no other fix was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, For quite a while I have the problem of updating a row in my table. I have 2 records in my table. The first is locked by the program and I want to update the second in my table. But I can't reach the second row because the first is locked. How to avoid this? The message I got is: Could not to a physical-order read to fetch next row Isam error 107: record is locked. statement that I execute: update table set column = column + 1 where field1 = xx and field2 = yy I use IDS 9.40 on Linux SuSe. My table has lock mode (row) thanks!
Is the query using an indexed access or a sequential scan? In a sequential scan it's normal you hit the error because it's trying to read the locked row. You should access it using an Index. If you don't have an index for it, consider creating one. If you already have it, check your OPTCOMPIND parameter, your statistics... Depending on the table size it could still try to sequential scan the table... You can also: - SET LOCK MODE TO WAIT n where "n" is a number of seconds to wait for the lock release - change the isolation level to dirty read Regards. On Thu, Apr 12, 2012 at 10:51 AM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > Hi all, > > For quite a while I have the problem of updating a row in my table. > I have 2 records in my table. The first is locked by the program and I > want to > update the second in my table. But I can't reach the second row because the > first is locked. How to avoid this? > > The message I got is: Could not to a physical-order read to fetch next row > Isam error 107: record is locked. > > statement that I execute: > update table > set column = column + 1 > where field1 = xx > and field2 = yy > > I use IDS 9.40 on Linux SuSe. > My table has lock mode (row) > > thanks! > > > > ******************************************************************************* > 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... --20cf3074d87c8741ee04bd79092e
The where-clause are the index fields. So normally no sequential scan.
Do you have an index on the columns that you are filtering on? Khaled Bentebal de mon portable Le 12 avr. 2012 à 11:51, "DANNY DE KOSTER" <ddk@fidelity-soft.be> a écrit : > Hi all, > > For quite a while I have the problem of updating a row in my table. > I have 2 records in my table. The first is locked by the program and I want to > update the second in my table. But I can't reach the second row because the > first is locked. How to avoid this? > > The message I got is: Could not to a physical-order read to fetch next row > Isam error 107: record is locked. > > statement that I execute: > update table > set column = column + 1 > where field1 = xx > and field2 = yy > > I use IDS 9.40 on Linux SuSe. > My table has lock mode (row) > > thanks! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Do you have an index on field1 and field2? Are the data distributions for the table up to date? In your update session do you set LOCK MODE TO WAIT? 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, Apr 12, 2012 at 5:51 AM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > Hi all, > > For quite a while I have the problem of updating a row in my table. > I have 2 records in my table. The first is locked by the program and I > want to > update the second in my table. But I can't reach the second row because the > first is locked. How to avoid this? > > The message I got is: Could not to a physical-order read to fetch next row > Isam error 107: record is locked. > > statement that I execute: > update table > set column = column + 1 > where field1 = xx > and field2 = yy > > I use IDS 9.40 on Linux SuSe. > My table has lock mode (row) > > thanks! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340661b379d004bd7a5775
If these are the only two rows in the table, or if there are not too many more, or if the data distributions are stale or non-existent; then a sequential scan is still possible. What does SET EXPLAIN produce as the query plan. 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, Apr 12, 2012 at 7:30 AM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > The where-clause are the index fields. So normally no sequential scan. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8c0c7b9b2404bd7a6062
I find it (if I may see) strange that a database like IDS can't deal with this. set lock wait... resolves a lot but when my record stays locked I'm lost. Thanks for your tips Guys!!
Records stay locked when your applications are not designed properly. Properly designed applications only lock rows for fractions of a second. Study up on Optimistic Locking Protocols. The difference between Informix and say Oracle is that if you write a poor applications against Informix you get lock errors. If you apply the same bad application design to an Oracle database, there are no lockout issues but your data can become trashed when two users both update old copies of a row and stomp on each other's updates. 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, Apr 12, 2012 at 8:50 AM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > I find it (if I may see) strange that a database like IDS can't deal with > this. > set lock wait... resolves a lot but when my record stays locked I'm lost. > Thanks for your tips Guys!! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406615497fd04bd7afdc3
Err... the situation is not as simple as you put it... Lets see... - Other databases (I suppose you're thinking about Oracle) would read the previous value in the locked row.... If the row validates the condition it would try to update, but it couldn't because the row is already locked - If you want a similar behavior, informix can provide it, in 11.x with LAST COMMITTED READ, but you're on 9.x.... So I don't see much difference... And as a general rule, you should understand why the records stay locked...? Regards. On Thu, Apr 12, 2012 at 1:50 PM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > I find it (if I may see) strange that a database like IDS can't deal with > this. > set lock wait... resolves a lot but when my record stays locked I'm lost. > Thanks for your tips Guys!! > > > > ******************************************************************************* > 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... --20cf3074d87cdf990a04bd7b9adf
It is important to understand what the engine is doing?
You can do a set explain on to see the path that the engine takes and also run
onstat -k to see what locks are being used at the time it happens.
Informix has its way of doing things and it is usually good and safe.
Most of the concurrency problems are application related .
There must be a reason why it is doing it this way.
Ids 9.30 is about 10 years old.
Version 11 allows you to also see the last committed row. Check it out.
But first it is important to understand what is going on with a Explain On and
with onstat - k when things happen
Khaled Bentebal de mon portable
Le 12 avr. 2012 à 14:50, "DANNY DE KOSTER" <ddk@fidelity-soft.be> a écrit :
> I find it (if I may see) strange that a database like IDS can't deal with
> this.
> set lock wait... resolves a lot but when my record stays locked I'm lost.
> Thanks for your tips Guys!!
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I completely agree with this poor piece of code. So I started to build another way to limit the lock time. Thanks for your answers!! Danny
Related threads
- record locked
- who locks a record?
- Regarding Non-Default Page Sizes
- Don't Understand Table's Space Requirement