Progress OpenEdge JDBC No Lock Driver

Questions and answers regarding general SQL and backend databases

Progress OpenEdge JDBC No Lock Driver

Postby sbutler » Thu Aug 06, 2020 11:56 pm

We are beta testing this with some customers now and its working well. If you are a Progress/OpenEdge customer, you may notice Servoy can have trouble querying the data in the tables directly because of the locking that's happening in your existing backend Progress/OpenEdge apps. When reviewing the Servoy logs, you may see errors like this:
Failure to get record lock on a record from table <tablename>


Our Progress OpenEdge JDBC No Lock Driver implements a work around for this, allowing Servoy to continue to query those rows and only retrieve the committed data while avoiding the lock.

If you are interested, we have a 30 day free trial and more information available at http://servoycomponents.com/components/ ... ock-driver.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 728
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Progress OpenEdge JDBC No Lock Driver

Postby pitc » Mon Feb 08, 2021 11:53 pm

Scot,
is this still an issue?
The info here: https://knowledgebase.progress.com/articles/Article/P12158
suggests other solutions or workarounds.
Tom
Tom
prospect-saas.com
pitc
 
Posts: 87
Joined: Thu Nov 14, 2019 2:22 pm
Location: Ottawa, Ontario, Canada

Re: Progress OpenEdge JDBC No Lock Driver

Postby sbutler » Tue Feb 09, 2021 4:36 am

Hi Tom. It is still an issue for Servoy or any other JDBC connection connecting to OpenEdge. The driver we created in the link above solves the problem by implementing their suggested resolution. It is currently being used in production by several large OpenEdge installs with Servoy. There is a 30day trial option at the link above if you are interested.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 728
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Progress OpenEdge JDBC No Lock Driver

Postby ptalbot » Tue Feb 09, 2021 5:12 am

Hi Scott, you say the solution is "allowing Servoy to continue to query those rows and only retrieve the committed data while avoiding the lock."

Problem is that what they say in the Progress knowledge base this implements dirty reads, meaning that it also retrieves uncommitted data, so it's not safe and could lead to clients' cache being out of sync with the actual state of the database (in case of concurrent transaction rollbacks).

How do you solve this issue in your current applications currently being used in production?
Wouldn't a better solution be to implement catch/retry when reading data?
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1636
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: Progress OpenEdge JDBC No Lock Driver

Postby sbutler » Wed Feb 10, 2021 10:29 pm

ptalbot wrote:Problem is that what they say in the Progress knowledge base this implements dirty reads, meaning that it also retrieves uncommitted data, so it's not safe and could lead to clients' cache being out of sync with the actual state of the database (in case of concurrent transaction rollbacks). ?


In the rare case of concurrent transaction rollbacks, yes, thats possible. Thing is, there is no magic option to make an old database like progress suddenly become ACID compliant for multiple users over JDBC. So you have to pick your poison. With Progress, it seems to lock rows even when doing selects through the legacy app. So the act of a user just sitting on a record can lock a row. This might be application specific, so you should test what happens in the legacy app when accessing Progress. In the testing we've done with our clients, it locks when doing reads, but the legacy apps themselves did very short transactions (ie, they had an edit/save type button, and on save would start/commit/end the transaction), so there was little chances of concurrent transaction rollbacks Again, this might differ based on what the legacy apps do against the progress db.

ptalbot wrote:Wouldn't a better solution be to implement catch/retry when reading data?

I don't think so. Servoy already does a try/catch it looked like. If I remember correctly going over the logs, they would try 3 times (but its been a while, so you should verify that). You might also have legacy progress apps that start a transaction when the edit button is clicked, and then the user goes off to lunch, so this could result in locking out the rest of the users for a long period of time.
Its also more complex than just one record for one table. In most Servoy apps, it loads lots of related data, and forms have value lists based on other tables, so it hit many tables just to show the record the user clicks on. So waiting over a try/catch for each of these would seem disastrous and make the Servoy app unusable.

Also, keep in mind you still have the general Servoy caching issue to deal with. The legacy app that connects to progress is probably still actively used and inserting/updating/deleting data while the Servoy app is in use. So you also have to implement background workers to scan those tables by modification date and broadcast out the changes to Servoy users. So even if a dirty read did happen, it will probably be corrected up during this process and update the users cache correctly. Of course you can also implement real-time updates over a web-service implementation that the legacy app hits, but most people don't want to deal with that.

So, if you combine our driver, with the background workers for cache updates, it will be correct most of the time, and even if it does do a dirty read, that will only exist until the background worker runs and broadcasts the change. So, I think thats the best possible approach to get progress to scale with Servoy to a large number of users, and still have a nice fast user experience.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 728
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Progress OpenEdge JDBC No Lock Driver

Postby ptalbot » Thu Feb 11, 2021 7:40 pm

Thanks for your reply, Scott! That's helpful.

At this stage, I believe that the best course of action is to push the client to move away from Progress if possible and adopt a proper ACID compliant database.
If not, we will explain the risks to them, and it will be their choice...
Using short transactions is always best in any case, so we can try and minimize the issues, and I will also advise against having legacy apps hitting the same database concurrently, perhaps devising a synchronization process if needed.
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1636
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: Progress OpenEdge JDBC No Lock Driver

Postby sbutler » Thu Feb 11, 2021 9:29 pm

Good Luck. My experience with Progress customers has been that they usually have some huge off-the-shelf ERP or custom thing they have developed for 25 years and they can't get away from it easily. So they just want to run Servoy along side it to offer a modernized interface to their users. It also has business logic coded within the database, so its hard to port to something else. So, my expereince is they are happy with the compromise I described. If you can get them to switch from Progress to something else, even better!
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 728
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests