7 replies to this topic

[Wim Soutendijk]

I have encountered the following problem using pro-iv 5.5 and SQL server

I have a main screen in which the auto commit is disabled, where a record is added into a hotel database and in a paging screen (from the same function) a room type for that specific hotel is added
Even though there is no commit statement in the function, the new hotel and new room type are directly visible in another session.
If I cancel out of the function and issue a roll-back, the hotel and its room types are not visible anymore in the other session.
That other session could very well have been a booking process, resulting in a booking record for a hotel and room type that does not exist anymore in the database! (data integrity is lost)

Even more, on Query Analyzer if I issue a “SELECT * FROM HOTELS” while adding a hotel using an uncommitted screen, Query Analyzer holds the screen, waiting for the session to either terminate with a COMMIT or a ROLL-BACK.

The problem has been reported to pro-iv.

[Bill Loven]

Just a suggestion. The way we got around commits and having rows available to other screens is to use memory files. If you cancel, nothing is written to the database. If you Ok, then write your menory files to the database. This will prevent orphans from happing.

HTH, Bill

[Richard Bassett]

Wim,

Whilst I know little next to nothing about SQL Server, in general database terms this is simply an issue of transaction 'isolation level'. The SQL standards define several isolation levels and there used to be mention of them at the end of ProIV's transaction processing environment guide. Support for isolation levels of course varies between databases as do the performance and locking implications..

If uncommitted writes are observable by another session, you must be using an isolation level equivalent to 'dirty read' (read uncommitted). I would imagine there is a way to reconfigure this in ProIV and/or SQL Server on a systemwide and/or per-session basis but I wouldn't know what that might be.

Note that this problem cannot arise with Oracle because it has 'read consistency' provided by its underlying 'multiversioning' mechanism (I know that doesn't help you but it might preempt an obvious question).

[Wim Soutendijk]

The only setting I found in the pro4.ini file is TP_ROLLBACK=Y, but I am not even sure if this setting is still used for the SQL databases

On the database settings in SQL server the recovery model is set to Full and the compatibility level to 80, which are both the recommended settings

[Joseph Bove]

Wim,

ProIV 5.5 does dirty reads against SQL Server.

SQL Server has a feature (for lack of a better way to describe a complete violation of SQL rules) called dirty reads. This allows you to read uncommitted data.

The last we heard on this issue was that it was being discussed in the UK.

Regards,

Joseph

[Wim Soutendijk]

Do I understand you correctly in concluding that with SQL server the auto commit flag should always be turned on in PRO-IV to avoid data integrity problems?

Is this specific to SQL Server?

How does pro-iv handle this with an Oracle database?

[Richard Bassett]

No, It seems to me Joseph is suggesting that ProIV is deliberately reading from SQL Server with 'dirty read' isolation ?!

I don't know if that's correct, but if it is and it cannot be overridden then I'm not surprised it's "being discussed in the UK" - I for one would consider that totally unacceptable if I had to use it.

For sure SQL Server can provide better isolation than that (cf. your experience with Query Analyzer) and I would be shocked if in fact the default was not better than that.

As I pointed out, ProIV doesn't need to worry about this aspect of reads on Oracle because, regardless of application technology, Oracle's multiversioning provides a guaranteed minimum level of isolation (actually 'statement level read consistency') that is much better than 'dirty read'.

It pays to be ALWAYS very wary of isolation and locking semantics when porting any software to a different database or filesystem (the importance of setting SQL_TRANSACTION_ERROR when running ProIV against Oracle is a good example).

I think I can safely say that almost no two of the many filesystems and databases that have been supported by ProIV over the years have provided exactly the same semantics in this area.

[Joseph Bove]

Wim,

Richard is correct in his read on what I've said. It does not matter what you do, ProIV on SQL Server does dirty reads. (Richard has the better vocabulary for describing this!)

You can replicate the behavior on the simplest of paging screens.

In one session, call up a paging screen on a very small table. Add one row to the table, but don't leave the screen.

Look at the table through any SQL tool. You should not see the row

In your other session, open the same paging screen and you will see the row.

Now, just to prove to yourself that the row really hasn't been committed, go back to your original session and . This will generate a rollback.

Go back into the paging screen and the row is not there, it never existed.

We reported this problem on ProIV 5.5 325 and have not heard of a way to resolve it.

You will not observe this behavior with Oracle or PostgreSQL. I've only seen this on SQL Server.

We found out about this because one of our clients had some tables with foreign keys that pointed to rows that never existed...

Regards,

Joseph