Lost data
A user reported sporadically "losing data" with no error returned by the affected transactions, and asked whether switching tables from page-level to row-level locking (already done) would help. Art Kagel and Fernando Nunes replied that the engine doesn't lose data and that lock granularity won't fix it: the cause is almost certainly application-side transaction handling — e.g. reading rows without locking so an UPDATE's WHERE clause no longer matches (and the app never checks rows affected), or the app swallowing errors (classic 4GL WHENEVER ERROR misuse). They recommended optimistic locking/transaction protocol and reviewing application logic; row-level locking is still advised for OLTP. No confirmation of the actual cause or a fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello: We are losing data sporadically so due to this we hace change our lock table mode from page to row on affected tables. Is there any thing more can be done to resolve this? Affected transaction doesn't return any error code. Thank you. --20cf307f380e4fea2b04c7ec9596
Informix does not "lose data". When I have seen this it is almost always because the applications are not using proper transaction management. Look up Optimistic Locking Protocol or Optimistic Transaction Protocol. I have posted descriptions of the method on the forums many times over the years, so you could search the forum or the newsgroup. 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, Aug 23, 2012 at 6:47 AM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > Hello: > > We are losing data sporadically so due to this we hace change our lock > table mode from page to row on affected tables. Is there any thing more can > be done to resolve this? > > Affected transaction doesn't return any error code. > > Thank you. > > --20cf307f380e4fea2b04c7ec9596 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba3fcba38b364504c7eed01d
Hello: Thank you very mucha. I'll search the forum. Regards 2012/8/23 Art Kagel <art.kagel@gmail.com> > Informix does not "lose data". When I have seen this it is almost always > because the applications are not using proper transaction management. Look > up Optimistic Locking Protocol or Optimistic Transaction Protocol. I have > posted descriptions of the method on the forums many times over the years, > so you could search the forum or the newsgroup. > > 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, Aug 23, 2012 at 6:47 AM, Juan Francisco González Navarro < > jfrancisco.navarro@gmail.com> wrote: > > > Hello: > > > > We are losing data sporadically so due to this we hace change our lock > > table mode from page to row on affected tables. Is there any thing more > can > > be done to resolve this? > > > > Affected transaction doesn't return any error code. > > > > Thank you. > > > > --20cf307f380e4fea2b04c7ec9596 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba3fcba38b364504c7eed01d > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00248c6a66c227f8c404c7eef307
Unless there is some sort of corruption (which you would not solve that way) and as Art already replied, neither Informix nor any other database should "loose data". And if you don't get an error I doubt that the change will solve the issue. Having said that: - OLTP applications should never use page level locking. The downside of row level locking is a bit (probably not noticeable) performance hit and some memory consumption (typically residual relative to the instance memory usage) - If you don't receive an error it may be because of two reasons (the ones I can remember now, but maybe there are others): 1- You're reading the data without locking it. Someone changes it. When you try to update it using "WHERE condition", the condition is not valid anymore because someone else changed the data and the application does not verify the number of affected rows 2- The application may be catching the error and ignoring it. This is very common with 4GL applications that (mis)use the WHENEVER ERROR *compiler directive* In either case you won't solve it by changing the table lock granularity So, you really should check the application logic because I'd say it's wrong. Regards. On Thu, Aug 23, 2012 at 11:47 AM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > Hello: > > We are losing data sporadically so due to this we hace change our lock > table mode from page to row on affected tables. Is there any thing more can > be done to resolve this? > > Affected transaction doesn't return any error code. > > Thank you. > > --20cf307f380e4fea2b04c7ec9596 > > > > ******************************************************************************* > 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... --00248c768d86628dfc04c7eef667
I thought I said that. ;-) 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, Aug 23, 2012 at 9:38 AM, Fernando Nunes <domusonline@gmail.com>wrote: > Unless there is some sort of corruption (which you would not solve that > way) and as Art already replied, neither Informix nor any other database > should "loose data". > And if you don't get an error I doubt that the change will solve the issue. > > Having said that: > > - OLTP applications should never use page level locking. The downside of > row level locking is a bit (probably not noticeable) performance hit and > some memory consumption (typically residual relative to the instance memory > usage) > > - If you don't receive an error it may be because of two reasons (the ones > I can remember now, but maybe there are others): > > 1- You're reading the data without locking it. Someone changes it. When > you try to update it using "WHERE condition", the condition is not valid > anymore because someone else changed the data and the application does not > verify the number of affected rows > > 2- The application may be catching the error and ignoring it. This is > very common with 4GL applications that (mis)use the WHENEVER ERROR > *compiler directive* > > In either case you won't solve it by changing the table lock granularity > > So, you really should check the application logic because I'd say it's > wrong. > Regards. > > On Thu, Aug 23, 2012 at 11:47 AM, Juan Francisco González Navarro < > jfrancisco.navarro@gmail.com> wrote: > > > Hello: > > > > We are losing data sporadically so due to this we hace change our lock > > table mode from page to row on affected tables. Is there any thing more > can > > be done to resolve this? > > > > Affected transaction doesn't return any error code. > > > > Thank you. > > > > --20cf307f380e4fea2b04c7ec9596 > > > > > > > > > > ******************************************************************************* > > 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... > > --00248c768d86628dfc04c7eef667 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340c3b87bcbb04c7ef5be0
You said something, specifically that Informix does not loose data. And in past posts I'm sure you explained a lot more in a lot more detail. And I wrote you already said it. I just gave some clues to specific problems that could cause the effect mentioned by the OP... Probably I wasted bandwidth but I've been spending most of the day with company bureaucracy and I needed some technical stuff to survive trough the rest of the day :) Regards. On Thu, Aug 23, 2012 at 3:05 PM, Art Kagel <art.kagel@gmail.com> wrote: > I thought I said that. ;-) > > 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, Aug 23, 2012 at 9:38 AM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > Unless there is some sort of corruption (which you would not solve that > > way) and as Art already replied, neither Informix nor any other database > > should "loose data". > > And if you don't get an error I doubt that the change will solve the > issue. > > > > Having said that: > > > > - OLTP applications should never use page level locking. The downside of > > row level locking is a bit (probably not noticeable) performance hit and > > some memory consumption (typically residual relative to the instance > memory > > usage) > > > > - If you don't receive an error it may be because of two reasons (the > ones > > I can remember now, but maybe there are others): > > > > 1- You're reading the data without locking it. Someone changes it. When > > you try to update it using "WHERE condition", the condition is not valid > > anymore because someone else changed the data and the application does > not > > verify the number of affected rows > > > > 2- The application may be catching the error and ignoring it. This is > > very common with 4GL applications that (mis)use the WHENEVER ERROR > > *compiler directive* > > > > In either case you won't solve it by changing the table lock granularity > > > > So, you really should check the application logic because I'd say it's > > wrong. > > Regards. > > > > On Thu, Aug 23, 2012 at 11:47 AM, Juan Francisco González Navarro < > > jfrancisco.navarro@gmail.com> wrote: > > > > > Hello: > > > > > > We are losing data sporadically so due to this we hace change our lock > > > table mode from page to row on affected tables. Is there any thing more > > can > > > be done to resolve this? > > > > > > Affected transaction doesn't return any error code. > > > > > > Thank you. > > > > > > --20cf307f380e4fea2b04c7ec9596 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > 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... > > > > --00248c768d86628dfc04c7eef667 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340c3b87bcbb04c7ef5be0 > > > > ******************************************************************************* > 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... --20cf3005de806cea9104c7ef71bd
Fernando: Switch to decaf dude. I wasn't chiding you, just trying unsuccessfully to be funny. (8^( Actually I appreciate that you went into more detail for the OP. I might have, but I'm feeling lazy this morning after working to 4AM recovering whatever data I can from a server that lost 5 blobspace chunks with no backups (long story) when a disk array without RAID10 had a hard crash. 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, Aug 23, 2012 at 10:12 AM, Fernando Nunes <domusonline@gmail.com>wrote: > You said something, specifically that Informix does not loose data. And in > past posts I'm sure you explained a lot more in a lot more detail. > And I wrote you already said it. > I just gave some clues to specific problems that could cause the effect > mentioned by the OP... Probably I wasted bandwidth but I've been spending > most of the day with company bureaucracy and I needed some technical stuff > to survive trough the rest of the day :) > Regards. > > On Thu, Aug 23, 2012 at 3:05 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > I thought I said that. ;-) > > > > 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, Aug 23, 2012 at 9:38 AM, Fernando Nunes <domusonline@gmail.com > > >wrote: > > > > > Unless there is some sort of corruption (which you would not solve that > > > way) and as Art already replied, neither Informix nor any other > database > > > should "loose data". > > > And if you don't get an error I doubt that the change will solve the > > issue. > > > > > > Having said that: > > > > > > - OLTP applications should never use page level locking. The downside > of > > > row level locking is a bit (probably not noticeable) performance hit > and > > > some memory consumption (typically residual relative to the instance > > memory > > > usage) > > > > > > - If you don't receive an error it may be because of two reasons (the > > ones > > > I can remember now, but maybe there are others): > > > > > > 1- You're reading the data without locking it. Someone changes it. When > > > you try to update it using "WHERE condition", the condition is not > valid > > > anymore because someone else changed the data and the application does > > not > > > verify the number of affected rows > > > > > > 2- The application may be catching the error and ignoring it. This is > > > very common with 4GL applications that (mis)use the WHENEVER ERROR > > > *compiler directive* > > > > > > In either case you won't solve it by changing the table lock > granularity > > > > > > So, you really should check the application logic because I'd say it's > > > wrong. > > > Regards. > > > > > > On Thu, Aug 23, 2012 at 11:47 AM, Juan Francisco González Navarro < > > > jfrancisco.navarro@gmail.com> wrote: > > > > > > > Hello: > > > > > > > > We are losing data sporadically so due to this we hace change our > lock > > > > table mode from page to row on affected tables. Is there any thing > more > > > can > > > > be done to resolve this? > > > > > > > > Affected transaction doesn't return any error code. > > > > > > > > Thank you. > > > > > > > > --20cf307f380e4fea2b04c7ec9596 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > 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... > > > > > > --00248c768d86628dfc04c7eef667 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --14dae9340c3b87bcbb04c7ef5be0 > > > > > > > > > > ******************************************************************************* > > 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... > > --20cf3005de806cea9104c7ef71bd > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bac1dbd31e404c7ef83f2
I did think you were joking... In any case decaf is a good idea... Next week I'll travel to the neighbor country... usually the coffee there is not good so possibly I'll move to nocoffee... But since it varies with the region, I'm still hoping for the best... You'll notice from my posts how it goes ;) On Thu, Aug 23, 2012 at 3:17 PM, Art Kagel <art.kagel@gmail.com> wrote: > Fernando: > > Switch to decaf dude. I wasn't chiding you, just trying unsuccessfully to > be funny. (8^( > > Actually I appreciate that you went into more detail for the OP. I might > have, but I'm feeling lazy this morning after working to 4AM recovering > whatever data I can from a server that lost 5 blobspace chunks with no > backups (long story) when a disk array without RAID10 had a hard crash. > > 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, Aug 23, 2012 at 10:12 AM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > You said something, specifically that Informix does not loose data. And > in > > past posts I'm sure you explained a lot more in a lot more detail. > > And I wrote you already said it. > > I just gave some clues to specific problems that could cause the effect > > mentioned by the OP... Probably I wasted bandwidth but I've been spending > > most of the day with company bureaucracy and I needed some technical > stuff > > to survive trough the rest of the day :) > > Regards. > > > > On Thu, Aug 23, 2012 at 3:05 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > I thought I said that. ;-) > > > > > > 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, Aug 23, 2012 at 9:38 AM, Fernando Nunes <domusonline@gmail.com > > > >wrote: > > > > > > > Unless there is some sort of corruption (which you would not solve > that > > > > way) and as Art already replied, neither Informix nor any other > > database > > > > should "loose data". > > > > And if you don't get an error I doubt that the change will solve the > > > issue. > > > > > > > > Having said that: > > > > > > > > - OLTP applications should never use page level locking. The downside > > of > > > > row level locking is a bit (probably not noticeable) performance hit > > and > > > > some memory consumption (typically residual relative to the instance > > > memory > > > > usage) > > > > > > > > - If you don't receive an error it may be because of two reasons (the > > > ones > > > > I can remember now, but maybe there are others): > > > > > > > > 1- You're reading the data without locking it. Someone changes it. > When > > > > you try to update it using "WHERE condition", the condition is not > > valid > > > > anymore because someone else changed the data and the application > does > > > not > > > > verify the number of affected rows > > > > > > > > 2- The application may be catching the error and ignoring it. This is > > > > very common with 4GL applications that (mis)use the WHENEVER ERROR > > > > *compiler directive* > > > > > > > > In either case you won't solve it by changing the table lock > > granularity > > > > > > > > So, you really should check the application logic because I'd say > it's > > > > wrong. > > > > Regards. > > > > > > > > On Thu, Aug 23, 2012 at 11:47 AM, Juan Francisco González Navarro < > > > > jfrancisco.navarro@gmail.com> wrote: > > > > > > > > > Hello: > > > > > > > > > > We are losing data sporadically so due to this we hace change our > > lock > > > > > table mode from page to row on affected tables. Is there any thing > > more > > > > can > > > > > be done to resolve this? > > > > > > > > > > Affected transaction doesn't return any error code. > > > > > > > > > > Thank you. > > > > > > > > > > --20cf307f380e4fea2b04c7ec9596 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > 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... > > > > > > > > --00248c768d86628dfc04c7eef667 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --14dae9340c3b87bcbb04c7ef5be0 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > 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... > > > > --20cf3005de806cea9104c7ef71bd > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --e89a8f3bac1dbd31e404c7ef83f2 > > > > ******************************************************************************* > 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... --485b397dce03bbf17104c7f19e15