Record Lock after read

195 pts.
Tags:
Record lock
We are facing a scenario where more than one person use the database. If a person read the record, I need to lock it exclusively for that user and other users are not allowed to access it. We can't grant whole file exclusively for one user as we need to allow more than one user access this file at a time. How can I achieve this in RPG?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    As far as I know, you can't do it no matter what programming language is used. There is no such thing as an exclusive record lock.

    That is, you can't do it simply by basic programming methods. I suppose it could conceivably be done by creating a method that all other processes had to call in order to access any row in the table. The method would log the job name, the program and the row number. It would then return the requested row to the caller. The requesting process would also have to have a way to send a 'release' notice.

    If the same record is requested before the first process signals that it's finished with it, the request would be rejected.

    This 'method' would somehow have to be invoked by every function in your system that you want to control. I don't know if you want various query tools and commands like CPYF or others to be locked out as well as all programs in your application. The only possible normal way that I can think of to do that would be with the ADDPFTRG TRGTIME(*AFTER) TRGEVENT(*READ) command. That would only handle 'read' requests, so other triggers might also be needed.

    The "trigger" program could send an *ESCAPE message that caused a failure in the higher process. I don't know how you'd want to handle that, but you'd have to figure out how to handle any kind of lock in all your programs (and query tools and commands) anyway.

    Be aware that adding a *READ trigger can disable most optimization facilities including blocking, double-buffering and others. That means that programming for sequential access could run an order of magnitude or more slower.

    A much better idea might be to tell us what business problem you are trying to solve. You are asking about a technical problem that might not have a good solution nor any solution at all. But business problems can often be resolved by changes to procedures.

    Tom

    125,585 pointsBadges:
    report
  • Babs312
    Hi Tom, Thanks for the information and clarifications. Following is the business problem we are facing. 1. More than one user can access creation of sales invoice function. 2. Each user can select the criteria to display outstanding transactional information for generating the invoice. 3. If two users select the same criteria at the same time, the outstanding information show to both users is same. 4. If both of them selected the same information and proceed to generate sales invoice, double invoice has been generating for a single transaction. 5. We would like to prevent this from happening as business need to go through lot of processes to reverse this invoice. Any suggestion from your end.
    195 pointsBadges:
    report
  • TomLiotta

    If both of them selected the same information and proceed to generate sales invoice, double invoice has been generating for a single transaction.

    How is the generated invoice linked back to transaction data? Does the transaction data get updated to mark it as "Invoiced(Yes/No)"? Does the transaction data get marked in any way at all by the invoicing process?

    Tom

    125,585 pointsBadges:
    report
  • Babs312
    Yes, transaction data updated by Invoice process to protect from selecting again.
    195 pointsBadges:
    report
  • TomLiotta

    Then there shouldn't be a problem.

    A transaction row that is updated has to be "read for update" first. And that will lock the row to prevent any other process from doing the same thing at the same time.

    How is your programming getting around that problem?

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb
    In RPGLE -- F spec can specify file as U - update.  Then an Update lock is placed on the record on read.  Any other access with update capability will time out.
    50,860 pointsBadges:
    report
  • TomLiotta

    It might be an embedded SQL SELECT, later followed by a SQL UPDATE. Technically, that could provide a non-lock 'read'. I wouldn't expect it since the question specifically asks about RPG.

    Or it might involve reading transaction data, writing invoice data, then updating transaction data without setting a commitment boundary. The invoice data might effectively be 'orphaned' in such a case.

    Without knowing why the problem exists today, we can't know an appropriate remedy.

    Tom

    125,585 pointsBadges:
    report
  • Babs312
    Hi Tom,

    Following is the way the program behaves.

    1.  There are 2 screen involved in invoice process.
    2.  Users will select outstanding transaction on first screen and upon confirmation the information will be summarized and transferred to second screen.
    3.  On second screen users need to input some additional information relating to invoice and confirm for creation of invoice.

    4.  What actually happening is first user complete the process on first screen and go to second screen and due to some work commitment hold on the second screen without completing the process.

    5.  As the process was not completed by first users, no update has been happened to transaction data.  Another users go to first screen and select outstanding information.  Information shown will be similar to the first user and second user will select all the outstanding information and proceed to second screen.

    6.  Second user complete the process on second screen and generate invoices and update all databases accordingly.  

    7.  Now first user also goes and complete his process which creates another invoice and update transactional database accordingly.  

    8.  There is a possibility that both users carry out the same process at same time too.

    We need to avoid it.  Any suggestion?
    195 pointsBadges:
    report
  • philpl1jb

    Yes, it sounds like the second screen doesn't lock the record until it's actually going to update it. 

    When this screen gets the record the first time store the user change date and time in hold fields.  When this screen gets the record the second time to upate it and before it processes the invoice check the records change data and time and if it isn't the first change date and time do not proceed because someone has changed the record.

    Don't have a change date and time then get an external data structure with the prefix of hold_ and one with no prefix

    When you first get the record set the hold_ ds to the no prefix ds.  When you get the record for update compare the ds's if they are different someone updated the record .. don't proceed.

    Phil

    50,860 pointsBadges:
    report
  • TomLiotta

    It might depend whether "second screen" means "second program".

    Regardless, at the point where the record is read for update, it is locked. The record should be tested at that point to see if it's been marked as 'Invoiced'. If it has been marked, there shouldn't be any output for a new invoice.

    It seems either that there is no test or that the new invoice is written before the critical read is done. One or the other of those has to be done.

    If the writing of the invoice is done before that read, it could be done under commitment control. That way it might be subject to a later ROLLBACK to undo the new invoice. The whole transaction shouldn't be COMITted until the test for a previous invoice and a successful update both are completed.

    Tom

    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following