Answered Transaction Isolation Level not working

[Time_Traveller]

Hello Progress Community,

im currently using progress 12.2 running on Linux Ubuntu 20.04.3 LTS (Focal Fossa) and access it via JDBC and ABL-Sessions.

(Promon screenshot showing the connections)

So i did the following test:
1) Open a transaction via JDBC, change a Record via SQL `UPDATE` and then wait 20 Seconds before rollback.
2) Read the same record (NO-LOCK) with the ABL-SESSION (via ABL `FIND`)
3) I can see the changes which should not be persistent at this time
4) After the Rollback the record shows no changes anymore.
I set transaction isolation level read commited.
I also disable auto commiting for the SQL Transaction.


Does the Isolation Level only control the READS from the Database? And not the Data which is currently written by my own session?
The definition of "TRANSACTION ISOLATION" from ibm says quiet differnt to the defintion of progress.

Any Ideas how to solve this? I don't want my changes to be visible from outside my own transaction ...

 

[Rob Fitzpatrick]

3) I can see the changes which should not be persistent at this time

I assume you mean "I can see the changes, with an ABL client, which should not be persistent at this time". If so, that makes sense.

4) After the Rollback the record shows no changes anymore.

That is expected.

I set transaction isolation level read commited.

You set the transaction isolation level for that SQL client.

If I understand you correctly, you set the transaction isolation level for a SQL client connection, but you expect to see the effects of transaction isolation in an ABL client.

An ABL client doing no-lock reads can read dirty (uncommitted) data.
https://community.progress.com/s/article/P7789

 

[Time_Traveller]

Thank you very much for the Information. I didnt knew that ABL would allow dirty reads by default.
Also I understand now that the SQL Client transaction is configurable in terms of what it can read and what not.

But there is still one thing that bothers me:
Let's say we have a table "someTable" with exactly one record which has "someField" set to "bar"

and i do a simple transaction A:

And at the same time i do this with a different ABL Client


I wont get "Foo". I will still see "bar".
Does Progress store its own transaction data in some hidden internal buffer?
And is there anything i can do to prevent Dirty Reads? I didnt find any startup parameter for the sessions / pasoe or db to prevent them.
I would have to refactor my Code to lock read? (which is not an option for me tbh since i work with legacy code from 20 years of development)

 

[Stefan]

-rereadnolock

Which will not really help in this case, sorry.

 

[Time_Traveller]

Thanks for the help so far. I will leave this thread open until i had the time to look up all documentations i can get and then post my solution to this problem.
Probably i will end up writing my own buffer logic and write the collected data in one swift commit. Probably i will lose some performance on the way but this is a price im willing to pay.

 

[Time_Traveller]

KB entry (Progress Customer Community) gave a little more insight into the problem but has one flaw. With my tests,
NO-LOCK reads from progress ABL will cause dirty-reads ONLY on SQL transactions. Cant agree on the statement that it can happen to ABL transaction aswell . Tried this and it never happend.

It took some time but basically i found a solution to this problem. Now i am able to securely read and write data to any Progress Database from and to NodeJS.

Cheers.