How do you guys handle record locking when it comes to ProIV and a database? 

We're currently running in ProIsam but are working towards moving to Postgres.

With ProIsam, if I have a paging screen with a file in add/change/delete mode and lock a record, a second session will not be able to edit that record (obviously) until the first session is finished with it. What I am noticing in our Postgres environment is, in the same scenario, the record stays locked until the first session exits the paging cycle, which isn't ideal for us. In the Postgres environment I've added the commit() statement in after write of the file and now it behaves the same as the ProIsam environment, which is what we want. The ProIV documentation, however, states "It is recommended practice to structure application functions around the automatic commits and to use the COMMIT() statement only when necessary". What do you guys do? 

This is a very simple example. We have other functions that call many other functions which call many other functions, etc. many levels deep. What do you guys do in those scenarios? Ideally we would like to avoid restructuring our functions as much possible. That may result in orphan records being produced but that would be no different than what we have now. Not ideal but it is reality at the moment.

Thanks.

Mike,

The first SQL table encountered in a function group which is being modified, starts the transaction boundary. Every subsequent SQL table to be modified prior to the commit of the transaction will remain locked even though PROIV has written them out in a cycle. When the transaction  cycle is committed which is when that 1st SQL table is written and the cycle iteration completed, all the SQL tables within that transaction are committed and locks released.

In your paging example above, if your paging screen is the first screen to modify SQL tables, they will be committed in the paging cycle after each paging record is written. If on the other hand, you have a SQL table in a header cycle which is the parent to the paging cycle, nothing is committed until the header cycle tables are written.

Attached is a very good document produced by PROIV some years ago on this topic.

Hi Mike

Lewis made reference to a document, but I couldn't see it attached, so I have attached two, one of which I'm sure will be the right one, more than likely the one written by this forum's very own Ralph Gadsby.

 PRO-IV Relational Database Overview.pdf   647.87KB   119 downloads

 Converting from PRO-ISAM to a RDBMS.pdf   335.23KB   101 downloads

In my experience, upgrades from Pro-ISAM to a RDBMS are usually done for a reason, maybe to expose the data directly to other technologies, reduce complexity, break-out the business logic, perhaps with a view to replacing the UI, often to increase throughput, and, occasionally, for a much better reason, to achieve better ACID¹ compliance.

We all know that it's usually the time and cost components of the time, cost, quality triangle which dictate how much appetite an organisation has for this sort of upgrade, but based on yours, what I just mentioned above, and what's discussed on page 4 of "Converting a PROIV Application from Pro-ISAM to an RDBMS", what do you think best describes your situation?

If you haven't already, try to get a handle on all the transactions that make up the application, at least you'll then know roughly what you are working with. You may find that you can go with the default commit behaviour for 80% of transactions, disabled autocommit with explicit commit and rollback handling for 15% of transactions, being the large (multi-function) ones, and use of native RDBMS objects, like sequences, plus global and/or local temporary tables to remove the transaction boundary entirely, for the remaining 5%.

Regards

¹ - https://en.wikipedia.org/wiki/ACID

Thanks guys, that gives us a lot of information to think about/digest.

Am I safe in assuming that, as far as record locking is concerned, if we merely add calls to COMMIT() in before write of all files in any mode except look, things should work the same way we are used to with ProIsam?  Eventually we would like to make the necessary changes to take advantage of the additional benefits that a database can offer but initially we would be happy if 95% of our system worked the way it currently does.

If you issue a COMMIT() at the wrong point in a cycle, you will get a missing cursor error. It is best to structure your function so PROIV commits because you are writing out the table/file which started the transaction. That being said, we often use the COMMIT command and have no issues with it. Most often we use it after global updates where we want to release locks.

Sorry the doc did not upload correctly. Should be good now. I see it in the reply.

Something which could affect you is PostgreSQL's lack of (native) support for autonomous¹ transactions.

Any rollback has positive, neutral and negative side effects. Consider an application which writes audit records as it works through transactions. If a rollback occurs, everything since the last commit is lost, including the audit records ... and they could contain the reason for that failure. If your application uses auditing, you might want to leave the audit tables as Pro-ISAM. 

¹ - http://www.orafaq.co...ous_transaction

Mike,

Don't forget that proiv lock logic doesn't work with postgresql so any lock will result in a spinning session until the lock owner releases. NG were going to look into handling this but no news yet.

I ended up having to use a pro-isam lock file where I really need critical tables to be managed properly.

Doing this also has the advantage that you only have to 'B' mode the pro-isam file with the keys and the lock logic gives you an opportunity for the user to respond to the lock or a timed/count retry prior to any table access. The pro-isam file can double as an audit as Neil B has suggested.

 By using this method iscollect will clean up the pro-isam lock on a session failure no need for user/admin lock screens etc. 

Mike,

 

Don't forget that proiv lock logic doesn't work with postgresql so any lock will result in a spinning session until the lock owner releases. NG were going to look into handling this but no news yet.

I ended up having to use a pro-isam lock file where I really need critical tables to be managed properly.

Doing this also has the advantage that you only have to 'B' mode the pro-isam file with the keys and the lock logic gives you an opportunity for the user to respond to the lock or a timed/count retry prior to any table access. The pro-isam file can double as an audit as Neil B has suggested.

 

 By using this method iscollect will clean up the pro-isam lock on a session failure no need for user/admin lock screens etc. 

Thanks Neil, that was news to me. I tried it here and can confirm that is definitely still an issue as of 7.1.55. Are you aware of any other "gotchas" with ProIV + Postgres?

Mike,

I don't know if you have decided on Postgres for price alone or not. We use SQL Server exclusively. They have an express version which is free. Microsoft limits it to 1GB memory and 10 GB database. Locking on it works very well with PROIV. We have some global logics which we insert in lock logic which display person and session information to user as to who is locking the table. Obviously pros and cons with either db. The only PROISAM file we have to use is for odometers. I have no experience with Postgres so can't help you there.

Lew

Mike,

No real gotchas with Postgresql. I did get caught out  when moving from 8.1 to 9.2 and experienced initial cache building on my whole inventory. which turned out to be a bad index that behaved differently between versions.

When creating/converting tables I don't use the script creator within Proiv as this is a bit loose I just define it properly in pgAdmin.

I chose to use Postgresql to reduce cost of deployment on smaller user systems and provide a cross platform compatibility for Linux and Windows. The use of a ISO SQL also means that an up scale to Oracle is pretty straight forward.

I also chose to hybrid my data between pro-isam and the database. Most new stuff is mainly DB and designed to take advantage of it. Legacy only gets migrated when I need it - usually for more efficient retrieval or during larger changes.

My application uses a blend of technologies for Proiv/java/C/php/javascript and postgress works very well here and this gives me a choice as trends/demands arise. Proiv is still the fastest method of development !

Mike,

 

I don't know if you have decided on Postgres for price alone or not. We use SQL Server exclusively. They have an express version which is free. Microsoft limits it to 1GB memory and 10 GB database. Locking on it works very well with PROIV. We have some global logics which we insert in lock logic which display person and session information to user as to who is locking the table. Obviously pros and cons with either db. The only PROISAM file we have to use is for odometers. I have no experience with Postgres so can't help you there.

 

Lew

We only run Linux servers so SQL Server is out of the question for us and Oracle is way too expensive for a lot of our customers so Postgres it is. I actually really like Postgres from what I've been exposed to. Unfortunately ProIV "supports" it but to the bare minimum from what I can tell.

Mike,

 

No real gotchas with Postgresql. I did get caught out  when moving from 8.1 to 9.2 and experienced initial cache building on my whole inventory. which turned out to be a bad index that behaved differently between versions.

 

When creating/converting tables I don't use the script creator within Proiv as this is a bit loose I just define it properly in pgAdmin.

 

I chose to use Postgresql to reduce cost of deployment on smaller user systems and provide a cross platform compatibility for Linux and Windows. The use of a ISO SQL also means that an up scale to Oracle is pretty straight forward.

 

I also chose to hybrid my data between pro-isam and the database. Most new stuff is mainly DB and designed to take advantage of it. Legacy only gets migrated when I need it - usually for more efficient retrieval or during larger changes.

 

My application uses a blend of technologies for Proiv/java/C/php/javascript and postgress works very well here and this gives me a choice as trends/demands arise. Proiv is still the fastest method of development !

Thanks Neil. You may have seen this in one of my other posts but have you used the "UpdatableCursors=0" setting in odbc.ini? Still waiting to hear from ProIV to find out if they officially support this or not. Either way it makes a huge performance increase from what we have seen so far.

Mike,

 

Don't forget that proiv lock logic doesn't work with postgresql so any lock will result in a spinning session until the lock owner releases. NG were going to look into handling this but no news yet.

I ended up having to use a pro-isam lock file where I really need critical tables to be managed properly.

Doing this also has the advantage that you only have to 'B' mode the pro-isam file with the keys and the lock logic gives you an opportunity for the user to respond to the lock or a timed/count retry prior to any table access. The pro-isam file can double as an audit as Neil B has suggested.

 

 By using this method iscollect will clean up the pro-isam lock on a session failure no need for user/admin lock screens etc. 

Hi Neil,

ProIV just showed me something that can be added to your login script to get lock logic working. If you add "POSTGRESQL_NOWAIT=Y" to your login script lock logic works (at least from what I have been able to test so far). I don't see mention of this anywhere in the documentation so I've asked them to fill  me in as to what this actually does. I'll let you know once I hear back from them.

 

Mike,

 

Don't forget that proiv lock logic doesn't work with postgresql so any lock will result in a spinning session until the lock owner releases. NG were going to look into handling this but no news yet.

I ended up having to use a pro-isam lock file where I really need critical tables to be managed properly.

Doing this also has the advantage that you only have to 'B' mode the pro-isam file with the keys and the lock logic gives you an opportunity for the user to respond to the lock or a timed/count retry prior to any table access. The pro-isam file can double as an audit as Neil B has suggested.

 

 By using this method iscollect will clean up the pro-isam lock on a session failure no need for user/admin lock screens etc. 

 

Hi Neil,

 

ProIV just showed me something that can be added to your login script to get lock logic working. If you add "POSTGRESQL_NOWAIT=Y" to your login script lock logic works (at least from what I have been able to test so far). I don't see mention of this anywhere in the documentation so I've asked them to fill  me in as to what this actually does. I'll let you know once I hear back from them.

 

Please disregard, I jumped the gun on this one. Setting this option will essentially dself the record if a lock is encountered, which is not what we want in 99% of our functions.