23 replies to this topic

[TalentedFool]

well, I've check all my indexes and they are fine so dead end there.

anybody got anything further?

[TalentedFool]

OK, wild stab in the dark here but the main file we are getting these errors on is opening in lookup mode in the primary LS and we change is it subsequent LS's

Now in the Pro4.ini file we've got

SQLSERVE_SELECT_FOR_UPDATE=TRUE

set. Could that be a problem. Because to me and my way of thinking is that this is going to select the data for update.

Also - if we use embedded sql with where clause on - I've been reading on the net that in theory we should have an index for that where clause ... anybody agree with that?

[Richard Bassett]

TalentedFool,

(Usual caveat, not a SQL/Server user)

I think SQLSERVE_SELECT_FOR_UPDATE=TRUE is just to tell ProIV that SQL/Server supports the FOR UPDATE clause in SELECT statements and it can acquire locks that way. It seems very unlikely indeed to me that ProIV would apply a FOR UPDATE clause in Lookup mode (assuming that's what you meant). I doubt there are any ProIV apps that could cope with that.

However, re-reading records that you have modified (in the same transaction) is a "grey area" in many databases. In Oracle for example writes will not be visible to a read-only cursor established before the writes were made (eg. primary file in Lookup mode in LS1 will retrieve the "old version" of a record you have already updated).

I don't how SQL/Server deals with these issues of "read consistency" and "intra-transaction write visibility" - you might want to do some research. Please let us know if you find any good resources

Indexes should only ever be a performance issue, not alter the behaviour of your app.

[Richard Bassett]

I don't how SQL/Server deals with these issues of "read consistency" and "intra-transaction write visibility" - you might want to do some research. Please let us know if you find any good resources

OK, forget that. Looks like this issue is all over the Web like a rash as far as Oracle vs. SQL/Server comparisons are concerned.

Seem SQL Server 2005 "Yukon" introduces some kind of snapshot isolation (row versioning).

[TalentedFool]

Richard

Thanks for your input on this. I thought that was the case with the setting in the INI file just wanted somebody to confirm that for me.

What we have done now though is to place commits in strategic positions. As our screen is in lookup mode and we have other functions to insert/update/delete the data when we come back into the screen and refreshing the data that, that was the main area of the fetch out of sequence. We've also added an index to a different table that we were reading data on with where clause that didn't have an index on and was cauing trouble.

Problem appears to have gone away - but - just in case - will let you know any more updates.

Once again - thanks.

[TalentedFool]

Richard

Something I thought you might be interested in ..

www.oracle.com/technology/deploy/performance/pdf/9ivsSS2000forPerformanceV22.pdf

[Richard Bassett]

Thanks for that TalentedFool, should be interesting reading.
It'll be interesting to see an Oracle 10g / SQLServer 2005 comparison too when there is one

Problem appears to have gone away - but - just in case - will let you know any more updates

Thanks. Also please do let us know if you come across a specific explanation why the problem occurs in SQL/Server.

What we have done now though is to place commits in strategic positions

So, did you basically change things so that you were no longer reading stuff that might have been modified in the same transaction? Or does "strategic" mean something more subtle than that?

[TalentedFool]

So, did you basically change things so that you were no longer reading stuff that might have been modified in the same transaction? Or does "strategic" mean something more subtle than that?

It means something more strategic than that. Since the screen was in lookup mode anyway, there wouldn't be a transaction against the database. We found that once we modified the data, i.e. added a record, modified a record the transaction remained open until we exited the screen in which case we performed a commit.

The strategic part was placing the commit back in the screen where they are in lookup mode just before we go back and refresh the screen thus writting the data to file, closing the transaction and allowing a clean refresh of the data.

[TalentedFool]

I found a resolution on this if anybody is interested.

We were getting a fetch out of sequence when we were using embedded SQL on a large table. We were reading that table on fields that weren't defined within an index and so causing us a problem.

We added an index for the fields we were reading on and hey presto - problem went away.

Thanks for your input on this.