Hi guys and gals
At random points on our system we're getting "Fetch out of Sequence" errors and we can't pin down the cause.
The system is running on a W2k sp4 server with SQL Server sp3 on a 5.5 r333 kernel
We could be simply fetching data from the database for display or doing an update.
Any help would be appreciated at this point
cheres
Click the link below to see the new game I'm developing!
Fetch out of Sequence
Started by
TalentedFool
, Feb 24 2005 11:03 AM
23 replies to this topic
#2
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 24 February 2005 - 12:02 PM
At the risk of stating the obvious, the most likely explanation is that a commit is occurring for some reason (maybe explicitly in your code) and you are trying to continue fetching from a series of previously selected records after that commit.
Fetch-across-commit is not supported by most databases (I'm not familiar with SQL/Server though). It is implicitly supported by Oracle but is non-ANSI-compliant and should be avoided.
For example, widespread fetch-across-commit problems can occur if someone changes the code and causes a commit in a global logic or global function. You also need to be very careful of people programming "in the database" (triggers etc.) because ProIV needs to have complete control over its transaction boundaries.
Could this be your problem? Do you have any insight into what had changed when this started to happen?
Fetch-across-commit is not supported by most databases (I'm not familiar with SQL/Server though). It is implicitly supported by Oracle but is non-ANSI-compliant and should be avoided.
For example, widespread fetch-across-commit problems can occur if someone changes the code and causes a commit in a global logic or global function. You also need to be very careful of people programming "in the database" (triggers etc.) because ProIV needs to have complete control over its transaction boundaries.
Could this be your problem? Do you have any insight into what had changed when this started to happen?
Nothing's as simple as you think
#3
Dan Shannon
-
- Members
-
- 374 posts
ProIV Guru
- Gender:Male
- Location:Australia
Posted 24 February 2005 - 01:26 PM
Richard
Just to clarify:
Oracle handles read-only cursors happily across transaction boundaries, but not FOR UPDATE cursors obviously because they're clearly transaction based. And PRO-IV just loves a FOR UPDATE cursor - whenever the primary file of a non-one-time update, screen, report is in a record lock mode, you've got one.
You can't commit in a trigger, so you can't break transaction boundaries by writing triggers, but your point is valid in that PL/SQL procedures etc can do commits which break PRO-IV - just avoid them altogether and make sure that the application is doing the commit processing, not the individual components of code. In many cases it is a good idea to write code in PL/SQL, for example, as it's faster and simpler than PRO-IV (and you get lots of lovely access to the glorious power of the SQL engine that is denied to the PRO-IV purist).
MrFool if you have coders who like to put commits in global code, can I suggest you take them out the back and shoot them? I hope it's not that causing your problems. Look for those manual commits in any code where you're doing a commit every n rows, and then look for the FOR UPDATE cursor being raised in the code (primary file in change mode most often) and then, erm, shoot the programmer. After she (or he) has fixed the problem.
Just to clarify:
Oracle handles read-only cursors happily across transaction boundaries, but not FOR UPDATE cursors obviously because they're clearly transaction based. And PRO-IV just loves a FOR UPDATE cursor - whenever the primary file of a non-one-time update, screen, report is in a record lock mode, you've got one.
You can't commit in a trigger, so you can't break transaction boundaries by writing triggers, but your point is valid in that PL/SQL procedures etc can do commits which break PRO-IV - just avoid them altogether and make sure that the application is doing the commit processing, not the individual components of code. In many cases it is a good idea to write code in PL/SQL, for example, as it's faster and simpler than PRO-IV (and you get lots of lovely access to the glorious power of the SQL engine that is denied to the PRO-IV purist).
MrFool if you have coders who like to put commits in global code, can I suggest you take them out the back and shoot them? I hope it's not that causing your problems. Look for those manual commits in any code where you're doing a commit every n rows, and then look for the FOR UPDATE cursor being raised in the code (primary file in change mode most often) and then, erm, shoot the programmer. After she (or he) has fixed the problem.
#4
TalentedFool
-
- Members
-
- 43 posts
Member
- Gender:Male
Posted 24 February 2005 - 03:29 PM
I hear you about the commits ... its just not happening though.
We used to get a whole host of 'application transaction integrity errors' when we had commits in wrong places so we took them all out and now only commit at certain point when we're not in global functions - I Hope.
I'll go back and check the code thoug just to be on the safe side and get back with results. If in mean time you think of anything else then it would be appreciated.
Thanks guys
We used to get a whole host of 'application transaction integrity errors' when we had commits in wrong places so we took them all out and now only commit at certain point when we're not in global functions - I Hope.
I'll go back and check the code thoug just to be on the safe side and get back with results. If in mean time you think of anything else then it would be appreciated.
Thanks guys
Thanks
Lee
Lee
#5
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 24 February 2005 - 07:26 PM
Dan,
Just to clarify
TalentedFool,
Again to state the obvious, "DDL" statements typically cause an implicit commit. In Oracle that includes things like TRUNCATE TABLE that have been known to catch people out.
I'm intrigued by your "Application transaction integrity errors" so let me ask a dumb question or two.
Did you port this app from Oracle and if so did you have integrity errors only in Oracle or in SQL/Server too?
Has this app ever worked correctly on SQL/Server? (extra dumb, but I have to ask..)
Just to clarify
Not universally true - if you use a ProIV sort (yeah, yeah, I know) then you won't have one, and if you use ITER COUNT then I suspect something unpleasant is going on internally such as a hidden read-only cursor and reread-for-update. And yes, both these cases have nasty row-in-snapshot-not-found-any-more implications.And PRO-IV just loves a FOR UPDATE cursor - whenever the primary file of a non-one-time update, screen, report is in a record lock mode, you've got one.
Although not perhaps if you want your application to be portable to SQL/Server..In many cases it is a good idea to write code in PL/SQL, for example, as it's faster and simpler than PRO-IV (and you get lots of lovely access to the glorious power of the SQL engine that is denied to the PRO-IV purist).
TalentedFool,
Again to state the obvious, "DDL" statements typically cause an implicit commit. In Oracle that includes things like TRUNCATE TABLE that have been known to catch people out.
I'm intrigued by your "Application transaction integrity errors" so let me ask a dumb question or two.
Did you port this app from Oracle and if so did you have integrity errors only in Oracle or in SQL/Server too?
Has this app ever worked correctly on SQL/Server? (extra dumb, but I have to ask..)
Nothing's as simple as you think
#6
Dan Shannon
-
- Members
-
- 374 posts
ProIV Guru
- Gender:Male
- Location:Australia
Posted 28 February 2005 - 10:18 AM
Richard
Of course you're right, I must have been assuming that people gave up on PRO-IV sort ages ago if they'd moved to an RDBMS
And does the iteration count thingo work now? It didn't seem to last time I tried it... always implemented counters in code myself...
Sorry for being somewhat brusque with previous post, was in a spectacularly foul mood that night!
Of course you're right, I must have been assuming that people gave up on PRO-IV sort ages ago if they'd moved to an RDBMS
And does the iteration count thingo work now? It didn't seem to last time I tried it... always implemented counters in code myself...Sorry for being somewhat brusque with previous post, was in a spectacularly foul mood that night!
#7
TalentedFool
-
- Members
-
- 43 posts
Member
- Gender:Male
Posted 28 February 2005 - 12:32 PM
Richard
Yes we did port this over from Oracle. But that was 2.5 years ago. The integrity errors only ever occured in the Sequel port and they were down to commits being in the wrong place and the way that Pro-IV interacts with Sequel and the horrible little cursors that Pro-IV uses.
As for the fetches - they have only been recently happening. We've run a data take on on the system importing a whole load of old data from another system and it seems that since then .. down the pan. I've re-indexed the system, defragged the indexes, primary kets etc to rule them out but still they happen. As as of late we've been getting 018 and 019 errors (error read file and error writting to file)
Just can't pin it down
Yes we did port this over from Oracle. But that was 2.5 years ago. The integrity errors only ever occured in the Sequel port and they were down to commits being in the wrong place and the way that Pro-IV interacts with Sequel and the horrible little cursors that Pro-IV uses.
As for the fetches - they have only been recently happening. We've run a data take on on the system importing a whole load of old data from another system and it seems that since then .. down the pan. I've re-indexed the system, defragged the indexes, primary kets etc to rule them out but still they happen. As as of late we've been getting 018 and 019 errors (error read file and error writting to file)
Just can't pin it down
Thanks
Lee
Lee
#9
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 28 February 2005 - 02:45 PM
Dan,
ITER COUNT seems to work and there have even been times I advised people to use it because it was so much simpler than trying to chop transactions up "manually" (especially for those who don't fully understand the problem). However, because it uses fetch-across-commit, it doesn't solve the "snapshot too old" problem. It is now deprecated in systems I work on. As I mentioned above, it seems (to me) to use a "hidden" read-only cursor where the primary file is writeable.
Furthermore, both ITER COUNT (writable primary) and traditional ProIV sort (any primary I think) suffer from the following data integrity problems because they are both working from a "historical snapshot" when it comes to (re)reading the primary file:
(1) If any value affecting the ordering of rows has been updated by a concurrent transaction, the function may present or process row data that is not in fact in the specified (sort) order.
(2) If a row in the "snapshot" is deleted by a concurrent transaction ProIV seems to stop processing the whole LS as if end-of-fetch had been reached.
In any case you were right that triggers were a stupid example of programming in the database in this context of this problem. Strictly for pedantic completeness on proivrc; one can commit an autonomous transaction in a trigger (say to force debugging info to persist RIGHT NOW)
Yeah - I would always coerce people to use the database sort. However, code that has to supports ISAM and RDBMS, has a high EFILE# (ie. complex, possibly outer, join to replicate) and that doesn't handle too many records can perhaps be justified in persisting (for a while).Of course you're right, I must have been assuming that people gave up on PRO-IV sort ages ago if they'd moved to an RDBMS And does the iteration count thingo work now? It didn't seem to last time I tried it... always implemented counters in code myself...
ITER COUNT seems to work and there have even been times I advised people to use it because it was so much simpler than trying to chop transactions up "manually" (especially for those who don't fully understand the problem). However, because it uses fetch-across-commit, it doesn't solve the "snapshot too old" problem. It is now deprecated in systems I work on. As I mentioned above, it seems (to me) to use a "hidden" read-only cursor where the primary file is writeable.
Furthermore, both ITER COUNT (writable primary) and traditional ProIV sort (any primary I think) suffer from the following data integrity problems because they are both working from a "historical snapshot" when it comes to (re)reading the primary file:
(1) If any value affecting the ordering of rows has been updated by a concurrent transaction, the function may present or process row data that is not in fact in the specified (sort) order.
(2) If a row in the "snapshot" is deleted by a concurrent transaction ProIV seems to stop processing the whole LS as if end-of-fetch had been reached.
No problem, hardly brusque by internet standardsSorry for being somewhat brusque with previous post, was in a spectacularly foul mood that night
In any case you were right that triggers were a stupid example of programming in the database in this context of this problem. Strictly for pedantic completeness on proivrc; one can commit an autonomous transaction in a trigger (say to force debugging info to persist RIGHT NOW)
Nothing's as simple as you think
#10
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 28 February 2005 - 02:55 PM
TalentedFool,
Does your app have your own C code LINK()ed to the ProIV kernel (I imagine this is maybe unlikely on Windows servers given the multithreaded ProIV kernel)? I ask only because bizarre errors and other spurious behaviour can be caused by careless C programming.
Sounds a bit odd. Were you running on Oracle with SQL_TRANSACTION_ERROR set to Y?Yes we did port this over from Oracle. But that was 2.5 years ago. The integrity errors only ever occured in the Sequel port and they were down to commits being in the wrong place and the way that Pro-IV interacts with Sequel and the horrible little cursors that Pro-IV uses.
Very odd. I think I should stop guessing at this point as I know so little about SQL/Server.As for the fetches - they have only been recently happening. We've run a data take on on the system importing a whole load of old data from another system and it seems that since then .. down the pan. I've re-indexed the system, defragged the indexes, primary kets etc to rule them out but still they happen. As as of late we've been getting 018 and 019 errors (error read file and error writting to file)
Does your app have your own C code LINK()ed to the ProIV kernel (I imagine this is maybe unlikely on Windows servers given the multithreaded ProIV kernel)? I ask only because bizarre errors and other spurious behaviour can be caused by careless C programming.
Nothing's as simple as you think
#11
TalentedFool
-
- Members
-
- 43 posts
Member
- Gender:Male
Posted 28 February 2005 - 03:24 PM
Richard
Nope
NeilIV
In some places we are using INSERTS but not on any of the files that we are seeing errors with. We are however using UPDATE statements to update records. These updates are on files that we are seeing the 018/019 Pro-IV Errors but not the fetch out of sequence errors.
Yes we were and we are under the sequel environment as well.Sounds a bit odd. Were you running on Oracle with SQL_TRANSACTION_ERROR set to Y?
Does your app have your own C code LINK()ed to the ProIV kernel (I imagine this is maybe unlikely on Windows servers given the multithreaded ProIV kernel)? I ask only because bizarre errors and other spurious behaviour can be caused by careless C programming.
Nope
NeilIV
In some places we are using INSERTS but not on any of the files that we are seeing errors with. We are however using UPDATE statements to update records. These updates are on files that we are seeing the 018/019 Pro-IV Errors but not the fetch out of sequence errors.
Thanks
Lee
Lee
#12
Guest_Guest_*
Guest_Guest_*
-
- Guests
Posted 28 February 2005 - 05:16 PM
Weird s**t TalentedFool.
These records you're UPDATEing with explicit SQL - are they guaranteed not to be ones ProIV is updating itself?
Can't you get more detailed diagnostics from SQL/Server (@SYSERRTEXT etc.) for the read and write errors?
These records you're UPDATEing with explicit SQL - are they guaranteed not to be ones ProIV is updating itself?
Can't you get more detailed diagnostics from SQL/Server (@SYSERRTEXT etc.) for the read and write errors?
#13
Richard Bassett
-
- Members
-
- 707 posts
ProIV Guru
- Gender:Not Telling
- Location:Rural France
Posted 28 February 2005 - 05:23 PM
I'd second that.Weird s**t TalentedFool.
This system of yours - how big is it in terms of users & transactions-per-second?
Are these problems non-reproducible and only happening on a heavily-loaded system?
Just wondering..
Nothing's as simple as you think
#14
Guest_guest23_*
Guest_guest23_*
-
- Guests
Posted 28 February 2005 - 05:52 PM
Just a suggestion - transaction isolation levels and row locking - enable 'dirty reads' and check locks are at row-level.
To state the obvious: just after a data-take-on you most likely have much larger volume of data or else you have
'dirty data' - that may explain something that may be time consuming to deal with...
To state the obvious: just after a data-take-on you most likely have much larger volume of data or else you have
'dirty data' - that may explain something that may be time consuming to deal with...
#15
TalentedFool
-
- Members
-
- 43 posts
Member
- Gender:Male
Posted 02 March 2005 - 02:00 PM
Weird s**t TalentedFool.
These records you're UPDATEing with explicit SQL - are they guaranteed not to be ones ProIV is updating itself?
Can't you get more detailed diagnostics from SQL/Server (@SYSERRTEXT etc.) for the read and write errors?
I'll third that one
@SYSERRTEXT is blank which is even more wierd. I think I've cured the 018/019 errors. The transaction log file was around 20Gb in size so i truncated that to around 10mb and we haven't seen them since.
We're not guaranteed that the files Pro-Iv are updating are not in the update statements - one thing to note here is that we can get a fetch out of sequence even when we're just simply fetching data. I'm in the processes of checking out our indexes to make sure they are al in place and oK.
I'll keep you updated ..
Thanks
Lee
Lee
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
