Alpha DevCon 2018
Results 1 to 28 of 28

Thread: Disable opportunistic locking with backend db's?

  1. #1
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Disable opportunistic locking with backend db's?

    Hi, all.

    Is the problem of table corruption with opportunistic locking only with native Alpha databases, or does it also exist with backend SQL databases like MySQL? The documentation doesn't give a definite answer.
    Jim

  2. #2
    Member
    Real Name
    Bruce Cavenaile
    Join Date
    Apr 2004
    Location
    Keremeos, BC
    Posts
    81

    Default Re: Disable opportunistic locking with backend db's?

    My read of the white paper on Opportunistic Locking (Dennis Piccioni May 14, 2002) would leave me to believe that applies to ALL database systems, not just Alpha. Disable it to be "better safe, than sorry" as your grandmother often told you????
    Bruce Cavenaile

  3. #3
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Hi, Bruce!

    Thanks for your response. The reason I ask is that the procedure for disabling opportunistic locking contains a scary warning about the possibility of ruining the operating system, so I want to avoid doing that if I can.

    I've located Mr. Piccioni's white paper. It looks like just what I need.

    Thanks again.
    Jim

  4. #4
    Member
    Real Name
    Jack DeBoer
    Join Date
    Oct 2006
    Location
    Houston, TX
    Posts
    452

    Default Re: Disable opportunistic locking with backend db's?

    It looks scary but really not that scary. Just do a backup of your registry before you make any changes. Before you enter or change anything sit & study for a little bit so you do not make a error. Once you get done you will realize it was easy to do.

  5. #5
    Member
    Real Name
    Bruce Cavenaile
    Join Date
    Apr 2004
    Location
    Keremeos, BC
    Posts
    81

    Default Re: Disable opportunistic locking with backend db's?

    Jim

    Don't be too concerned about the dire warnings from the Evil Empire. Heed the preceding warning about backing up and then go ahead as directed in that white paper.

    I found that some of the registry keys didn't exist and I had to create a couple but it isn't a huge deal.
    Bruce Cavenaile

  6. #6
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Thanks for the advice and encouragement, my friends.

    I've never edited the registry before, so I was more than a little wary. You've removed my hesitancy.
    Jim

  7. #7
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    In this thread on the v6 message board I offered to send my OpLocksOff.aex file to anyone interested. I still will if you still have need of it. (The neat thing is that it can be added to an app that is given to a customer and the customer's computer can be updated more or less automatically - it does give them the option just in case they don't want to do it.)

    Edit: I just restored the file to my website. You can download it from the link mentioned in the other thread. (Capitalization of the thread path is critical on that site.) PLEASE READ THE INFO I PRESENTED IN THAT THREAD BEFORE USING IT. If you have additional questions, post them here - and feel free to contact me directly if I don't reply right away.
    Last edited by CALocklin; 04-12-2007 at 11:13 PM.

  8. #8
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Hi, Cal!

    I was hoping you'd join in. (I know you've written a lot about this topic.)

    I've downloaded the aex file and hopefully will get to try it out soon. I'll keep you posted on this thread.

    On the other thread you mentioned, there was a comment that disabling opportunistic locking may degrade the performance of client-server applications. For the sake of argument, this mean that disabling is not required if I'm using a SQL database backend? (The Microsoft documents give this impression.)

    It's very kind of you to offer your addin for free. Thank you very much.

    Jim
    Jim

  9. #9
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by jmatienza View Post
    ... there was a comment that disabling opportunistic locking may degrade the performance of client-server applications. For the sake of argument, this mean that disabling is not required if I'm using a SQL database backend? (The Microsoft documents give this impression.)...
    That's my understanding. Although I suspect the degradation is minimal, I really have nothing to base that on except suspicions. If both SQL and .dbf files are in use, I'd opt for disabling the opportunistic locking. I'd trade a small hit on SQL performance for a larger hit in .dbf reliability.

  10. #10
    Member
    Real Name
    Peter Kraljic
    Join Date
    Feb 2006
    Location
    NY/NJ
    Posts
    227

    Default Re: Disable opportunistic locking with backend db's?

    Cal,
    Thanks for the OpLocksOff.aex file I downloaded and will test it out and report back. I share your concern for MS keeps changing registry names from OS to OS making you need to create the "shotgun" approach. Which leads me to the obvious question. How does one know Oppurtunistic Locking is on or off on any given server or pc?
    Thanks
    Peter
    See in Las Vegas

  11. #11
    Member
    Real Name
    Jack DeBoer
    Join Date
    Oct 2006
    Location
    Houston, TX
    Posts
    452

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by pk9 View Post
    Cal,
    How does one know Oppurtunistic Locking is on or off on any given server or pc?
    Thanks
    Peter
    See in Las Vegas
    I believe by not having any problems (bad way to find out) or looking at the registry

  12. #12
    Member
    Real Name
    Peter Kraljic
    Join Date
    Feb 2006
    Location
    NY/NJ
    Posts
    227

    Default Re: Disable opportunistic locking with backend db's?

    Turbojack,
    Looking at the registry does not definitely state it is on or off. A review of MS KB shows varied names. I assume one could place a registry entry that is not interpeted as anything and just bypassed by the OS. That is why there should be a way to test.
    Peter

  13. #13
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default Re: Disable opportunistic locking with backend db's?

    The problem with opportunistic locking does not affect MySQL or any other SQL client-server database. It only affects databases in which all the workstations have equal access to tables, e.g., Access, Foxpro, Alpha Five, Data Access.Goldmine, ACT, etc. In MySQL or other client-server databases all the database requests go through a single central server database engine, so the whole question of opportunistic locking becomes moot.

    - Peter

  14. #14
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Thanks!!!

    Peter, thank you for an answer that was concise, definite, and supremely enlightening. Your patients are fortunate to have a doctor who communicates like that.

    Thanks to all who posted. I've gotten what I came for, plus a free addin from Cal.

    What a nice forum this is!
    Jim

  15. #15
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by pk9 View Post
    How does one know Oppurtunistic Locking is on or off on any given server or pc?
    I executed Cal's OpLocksOff() function in the interactive window. A dialog box popped up informing me that the system is not optimized for dbf indexes (i.e., that opportunistic locking is enabled). So I made the selection to optimize (disable opportunistic locking).

    When I tried OpLocksOff() again immediately afterward, the dialog box no longer popped up. I guess that means that opportunistic locking has been turned off.

    Now, is there a way I can take a look at that dialog box again?

    Cal, are you there?
    Jim

  16. #16
    Member
    Real Name
    Peter Kraljic
    Join Date
    Feb 2006
    Location
    NY/NJ
    Posts
    227

    Default Re: Disable opportunistic locking with backend db's?

    Jim,
    A dialog box of Cal's script does not in my opinion indicate if at the OS level of XP, W2000K or W2003 if the Server to PC sessions have OpLock turned off. I think this trial and error approach is unexceptable and needs to be addressed by Alpha Software give that according to there own documentation of:

    "Multi-User Alpha Five Applications"
    states the potential of "corruption of shared (networked) tables" and

    "Experience shows that opportunistic locking should be disabled to prevent corruption when running with Windows 2000, XP, and 2003."

    I will request if Richard/Selwyn would be so kind to definatively resolve this issue and indicate exactly what is required to be done on both the server and PC side and a way to confirm that OpLocks is OFF. I think end users and developopers who put their trust in A5 expect more of this fine program. I thank Cal for his kindness as well, to help resolve this very important issue.
    Peter

  17. #17
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by pk9 View Post
    A dialog box of Cal's script does not in my opinion indicate if at the OS level of XP, W2000K or W2003 if the Server to PC sessions have OpLock turned off.
    My routine has to be run on specific machines to turn off Op Locks on that specific machine. If you want to turn off Op Locks on the server, then it must be run on the server. (I've never heard of any way to set the registry on a different computer over a network.) However, if the server is ONLY a server and not used as a workstation, it may not be necessary to turn off Op Locks on the server - I'm not sure.

    The dialog box will normally not appear if all the registry settings are set up to turn off OpLocks. However, it is possible to run the script in the "force" mode which always shows the dialog box (this would typically be for use on a button on a maintenance menu - not in the autoexec script) and, in that case, the user has to look at what the message says to determine if OpLocks is on or off.

  18. #18
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Hello, Cal.

    I didn't notice that there was an optional logical argument, "Forced", in OpLocksOff. Setting it to .T. redisplayed the dialog box.

    With regard to the question of disabling opportunistic locking on the server, my impression from reading other threads as well as the Microsoft documentation is that it does not matter if the server is used as a workstation or not.

    In response to Peter's concern, you're saying that in the OpLocksOff function (provided it is not run in forced mode), the presence or absence of the dialog box does serve as an indicator to the status of opportunistic locking, i.e., it first checks the registry entries involved before displaying the dialog. Correct?

    Perhaps you could display a different dialog box according to the status of opportunistic locking, just to make things clearer.

    P.S. Is OpLocksOff supported in Vista?
    Last edited by jmatienza; 04-17-2007 at 09:12 PM.
    Jim

  19. #19
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by jmatienza View Post
    So in response to Peter's concern, you're saying that in the OpLocksOff function (provided it is not run in forced mode), the presence or absence of the dialog box does serve as an indicator to the status of opportunistic locking, i.e., it first checks the registry entries involved before displaying the dialog. Correct?
    Yes.

    Quote Originally Posted by jmatienza View Post
    Perhaps you could display a different dialog box according to the status of opportunistic locking, just to make things clearer.
    I could but do you really think the user wants to see that dialog every time they start the app even when the OpLocks get turned off the first time? (And, if they don't turn them off, the message will keep appearing.) I know my customers would be screaming for me to get rid of a message like that.

  20. #20
    Member
    Real Name
    Bob Arbuthnot
    Join Date
    Apr 2005
    Location
    Belleville, KS
    Posts
    598

    Default Re: Disable opportunistic locking with backend db's?

    Cal,

    Thank you so much for this extremely useful function. It makes turning off oplocks a snap and removes the 'stress' involved with doctoring the registry.

    Thank you again for sharing this with the rest of the A5 community.

    Bob Arbuthnot

  21. #21
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by CALocklin View Post
    I could but do you really think the user wants to see that dialog every time they start the app even when the OpLocks get turned off the first time? (And, if they don't turn them off, the message will keep appearing.) I know my customers would be screaming for me to get rid of a message like that.
    Cal,

    You're right, of course! I was only thinking of executing it from the interactive window, not from within a script in an app.

    Maybe my question about Vista should be posted on the V8 forum.

    I'd like to second Bob now and thank you again for your generosity in sharing this "stress-removing" addin!
    Jim

  22. #22
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by jmatienza View Post
    I was only thinking of executing it from the interactive window, not from within a script in an app.
    Ahah! I figured either you were doing something different or were just "stuck in a rut" from working on one issue. (Luckily, I never get stuck in ruts - not that I mention on the message board anyway.)

  23. #23
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,140

    Default Re: Disable opportunistic locking with backend db's?

    Quote Originally Posted by CALocklin View Post
    In this thread on the v6 message board I offered to send my OpLocksOff.aex file

    Edit: I just restored the file to my website. You can download it from the link mentioned in the other thread. (Capitalization of the thread path is critical on that site.) PLEASE READ THE INFO I PRESENTED IN THAT THREAD BEFORE USING IT. If you have additional questions, post them here - and feel free to contact me directly if I don't reply right away.
    Cal,

    You say above that you just "restored the file" to your web site. Can you confirm this, because I can't find it.

    Ray

  24. #24
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    I got lazy - it's not listed on my web page. You had to read the fine print above: "You can download it from the link mentioned in the other thread."

    Since that thread was originally posted on a previous message board, the link wasn't "live". Here's the link: http://www.aimsdc.net/Tips_Funcs/OpLocksOff.aex

  25. #25
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: Disable opportunistic locking with backend db's?

    Disabling read cache on windows workstations is also suggested to insure the integrity of server based databases.This will slow down things a bit
    The default windows setting has it enabled.
    Leaving it enabled opens the door to corruption when a worksation reading and writing to the table has problems.

  26. #26
    Member
    Real Name
    Richard James
    Join Date
    Jun 2004
    Location
    Atlanta, Ga.
    Posts
    213

    Default Re: Disable opportunistic locking with backend db's?

    Does Cal Locklin's OpLocksOff.aex still work in Windows version 10 with Alpha Anywhere v12?

  27. #27
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,761

    Default Re: Disable opportunistic locking with backend db's?

    I have no idea. I can only say that it's been a long time since I've had any need to use it. If you're having an issue, I doubt this would help - but I can't say for sure.

  28. #28
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,890

    Default Re: Disable opportunistic locking with backend db's?

    I know this is an old post, but may I ask why to disable opportunistic record locking?
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

Similar Threads

  1. disable opportunistic locking question
    By Bob Arbuthnot in forum Alpha Five Version 7
    Replies: 1
    Last Post: 02-19-2007, 07:31 AM
  2. opportunistic record locking
    By martinwcole in forum Web Application Server v7
    Replies: 6
    Last Post: 01-15-2007, 01:22 AM
  3. opportunistic record locking
    By martinwcole in forum Alpha Five Version 7
    Replies: 0
    Last Post: 01-12-2007, 11:08 PM
  4. Newsletter - About Opportunistic Locking
    By Ray Fernandez in forum Alpha Five Version 5
    Replies: 8
    Last Post: 08-22-2004, 06:12 PM
  5. Opportunistic Locking
    By Rhett Scott in forum Alpha Five Version 4
    Replies: 6
    Last Post: 06-23-2002, 09:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •