Fetch out of Sequence
#1
Posted 24 February 2005 - 11:03 AM
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
Lee
#2
Posted 24 February 2005 - 12:02 PM
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?
#3
Posted 24 February 2005 - 01:26 PM
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
Posted 24 February 2005 - 03:29 PM
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
Lee
#5
Posted 24 February 2005 - 07:26 PM
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..)
#6
Posted 28 February 2005 - 10:18 AM
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
Posted 28 February 2005 - 12:32 PM
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
Lee
#9
Posted 28 February 2005 - 02:45 PM
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 standards 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)Sorry for being somewhat brusque with previous post, was in a spectacularly foul mood that night
#10
Posted 28 February 2005 - 02:55 PM
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.
#11
Posted 28 February 2005 - 03:24 PM
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.
Lee
#12 Guest_Guest_*
Posted 28 February 2005 - 05:16 PM
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?
#14 Guest_guest23_*
Posted 28 February 2005 - 05:52 PM
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
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 ..
Lee
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users