TechTip: Preventing Record Lock, Part 1

[MCWebsite.Staff]

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
** This thread discusses the Content article: TechTip: Preventing Record Lock, Part 10

[WoofDog]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Although your suggested code WILL prevent a record lock, I think it is a little too restrictive. Why generate an error if any field in the MASTER file record has changed since its first retrieval? In my oppinion it is only relevant to check for changes to the fields that are present in the display record, ie. the fields MFPart and MFPrtDes. Granted I don't know if those fields are the only fields in the MASTER file, but if not it would be irrelevant to check for changes made to all the other fields.

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
User may decide to change certain fields based on other fields in the same record format. Generally speaking you have to check for the whole record format. In some rare cases I can see the benefit of checking for changed values of just a subset of fields in the record (especially with non-normalized table design).

[K.Forsythe]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Kaj, You stole the thunder form my next tip in the series! It shows how to select only a few fields for conflict rather than the whole record. Dmitriy is also right, you cannot only test the fields that you are updating, you have to test every field that could have played a role in the decision making that led to the change. In some cases that might lead you back to checking the whole record. The key is, that you have to understand your application, and the specific program that you are implementing this in. When in doubt, check the whole record. But, if you are sure that you understand the issues, you can reduce the frequency of conflicts by testing only selected fields. Thanks for the great thoughts! Kevin

[DaleB63]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
The problem with record locks has been around for years. We've found that it really comes down to presentation. Do you try to lock the record, then time out (default is usually sixty seconds) and respond to the error? Or do you read with no lock, saving a copy of the original record, and present display to user. Then, when user is done and edits passed, you have to reread the record (required before you try to update anyway), and compare the new read with the original copy. If they're different (how different is dependent on the particular application, as noted by others, but potentially the entire record is compared). Here's where the presentation problem comes in. User may have been interacting with this display for several minutes. If you now detect that the underlying record was changed by another job, how do you present to the user that all of their work was for naught? In most cases my customers have chosen to shorten the default wait to, say, 15 seconds, and then get the lock right up front. Presentation is much simpler, and you don't have any issues with someone else changing the record on you.

[Robert McCarty]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
EXCELLANT!

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Our company has used a similar technique in our product for all screens that has a similar logic. One additional thing we add is a call to another program if there are differences that displays a window that explains to the user that the record they are trying to update has changed. They are told their change will not take place and to try again. The main program then replaces the screen values and re-directs the program back to the display file loop. This has worked great for us over the years and very appears.

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Hi, I often read the recommendation to use the (N) extender or unlock operation code to unlock the record. This will only work if the file is not defined with commit in the F-Specifiations. If the file is used under commitment control only an update, the read of an other record or a commit or rolbk can unlock the record. Just an other solution for display programs: In display program we reduce the record wait time, by overriding the update file. If a user tries to update a record that is locked, we trap the message the iSeries sends and show it on the user's screen. This message contains the qualified job name, so the user can see who holds the record. In most cases, the user either changes to display mode, or waits some moments or calls the user that locks the record. Birgitta

[K.Forsythe]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Dale and Birgitta both made excellent comments. Am I missing something though? You both talked about locking the records before the screen I/O and shortening the wait time so that the error was less annoying. That is fine, but it does not eliminate the problems that occur when a user leaves that screen up for an extended period of time (Such as overnight) and causes other programs (especially batch programs), such as nightly processes to fail. You may see some parts of what you are talking about in the next couple tips. But I did not show anyone how to shorten the wait time on the record lock. Great comments! Kevin

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Another technique I've seen used is to define a locking serial# (like 10 i0 or 7s 0) in the master file record. The program takes the current serial#, adds 1 to it, and updates the record. Then when the program later goes to actually update the record with data, compare the saved serial# with the current serial# in the file. If they are the same, ok. If not, something changed. My personal preference is to never hold a system record lock for more than a second or 2. Purposely holding a record lock for an extended period of time causes problems for batch jobs. Should the batch job program just spin in a loop until the record lock is released? Spin for how long? And do what if the loop times out? I set up some proxy record locking utilities for one client a few years ago (idea from Gene Gaunt). This is nice to have when you only want one user maintaining a given sales order, or purchase order or a work order, or whatever at a time. A proxy record locking file would hold the file name, library, key, job info, etc and a time-out date/time stamp. If the user was finished with the sales order, the program would delete the proxy record lock. If the user held the proxy record lock too long (like 12 hours!), the sales order became available (proxy record lock record release date/time expired). If the user's job abended, the proxy record lock would expire too (logic checked to see if the locking job was still active). Chris

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Great article, but why did you tag it as unsophisticated in the final paragraph? I checked in the dictionary, and unsophisticated means "not technologically advanced". I am guessing that 99% of the screen programs spinning on AS/400 disks lock records during screen I/O. Only 1% of screen programs are advanced enough to NOT lock records during screen I/O, which means that they ARE sophisticated. And of the tiny fraction of pgms that do it correctly, most of them are written by code generators such as Progen or Synon. I have seen very few hand-written pgms which are considerate to users and DONT lock records during screen I/O. By definition, the technique you describe should be labeled "sophisticated"!! Great article. I wonder why in the AS/400 world so many pgmrs are careless about record locking. That would never fly in the real world! Can you imagine Ebay or Yahoo locking records? They would go out of business!

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
This technique may have been OK years ago, but I cant see using it today. Many records get updated by PC updates, web updates, EDI updates, SQL updates, etc. Unless EVERY pgm adheres to updating the serial #, the system will fail. Dont you think this is FAR to risky??

[dmjae2004@yahoo.com]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
In the world of persistance layer software a number of persistance layer software providers do use an Object Version ID (serial number) to detect a change. The benefit comes when you have a multi-record update across a numbe of tables. You can check against the root parent record rather then each record in each table. David

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Gosh, it just depends on the situation. If you're using n-tiered architecture to update a certain file, then the user interface -- whether it's PC, or web or whatever -- doesn't matter because all the logic should be centralized in stored procedures that adopt authority(typically some flavor or RPG or service programs). If you're letting your UI directly update iSeries files, you've got a security problem. So, when you say EVERY program, hopefully you're not talking about a lot of them. I'm a certainly fan of pessimistic record locking. Even if a file has a serial# in it, you don't have to use it and can still compare record images, ignoring the serial# field. I just wanted to offer the serial# technique as another possiblity, if the situation applies. Chris

[Guest.Visitor]

TechTip: Preventing Record Lock, Part 1

** This thread discusses the article: TechTip: Preventing Record Lock, Part 1 **
Actually, one trigger program could update the serial#. A regular program could retrieve the current serial#, later re-read the record, and if the serial# is the same, update the record. Then the trigger program could compare the before/after record images to see if anything changed and increment the serial#. So, even if someone updated the record through SQL, DBU, JCBC or whatever, the serial# would automatically increment. Chris