Shared-Lock issue - Forum - OpenEdge Development - Progress Community
 Forum

Shared-Lock issue

This question is not answered

I am seeing in promon that few users are locking certain tables for a longer periods (hours). in Promon option 4 its all shared lock with D. Is there an way to find out the program that causing this problem?


 7222       testuser       2      REC        6428            1105070604   309       SHR  D       None    FWD         1741851023
 7222       testuser       2      REC        8774              84382487   204       SHR          None    FWD         1732140956
 7222       testuser       2      REC        9836             613278489     8       SHR  D       None    FWD         1732140956

All Replies
  • If you're on a modern Progress version then Client Request Statement Cache is probably what you need: knowledgebase.progress.com/.../P150383

    Be careful with it - best to turn it on just for certain users and turn it off again as there are some bugs in some versions.

    A share lock often occurs when the code itself doesn't specify a lock. Run development and test with the -NL option which forces a no-lock. If there are places that don't have a specific lock specified then you'l know about them soon enough! Once you're happy you've ironed out all those issues you can run Production with -NL too.

  • Kinda doubt statement caching is is going to show anything meaningful here.

    Unless the client didn't have any database (so no record fetches or buffer releases etc. at all)  after the lock was obtained (for the middle row) or downgraded (for the other 2) it won't tell you where in the code the locking status changes.

    AFAIK Individual locks aren't timestamped either, so promon also won't tell you how old a lock is. (and neither will the VSTs).

    Still, these locks are held within an active transaction.(trans state is FWD, not DEAD/NONE) so it looks like these locks are held because of transaction scoping issue.

    Only reliable way to see how your transactions are scoped at runtime is to flip on the 4GLTrans logging on the clients. But word of caution: Logs can get large. Especially if you need to generate them in production.

  • If you’re running r-code, -NL has no effect. It only applies during compilation. No need for it in a production environment running r-code. 

    Rick Terrell 
    Principle Consultant, Professional Services 
    Progress

    Sent from my iPhone

    On Oct 5, 2018, at 3:40 AM, frank.meulblok <bounce-frankmeulblok@community.progress.com> wrote:

    Update from Progress Community
    frank.meulblok

    Kinda doubt statement caching is is going to show anything meaningful here.

    Unless the client didn't have any database (so no record fetches or buffer releases etc. at all)  after the lock was obtained (for the middle row) or downgraded (for the other 2) it won't tell you where in the code the locking status changes.

    AFAIK Individual locks aren't timestamped either, so promon also won't tell you how old a lock is. (and neither will the VSTs).

    Still, these locks are held within an active transaction.(trans state is FWD, not DEAD/NONE) so it looks like these locks are held because of transaction scoping issue.

    Only reliable way to see how your transactions are scoped at runtime is to flip on the 4GLTrans logging on the clients. But word of caution: Logs can get large. Especially if you need to generate them in production.

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.

  • Just out of curiosity, do you know if -NL works for dynamic queries that don’t have a lock status specified?
     
  • Dynamic queries that don’t have a specified lock status are always no-lock (per Mary Szekely as I learned years ago)
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     

  • Dynamic FINDs *do* default to share-locks though. Unless you specify -NL at runtime.

    A simple:

    BUFFER customer:FIND-FIRST("").

    PAUSE.

    is enough to prove that.

  • I was referring to create query, etc....
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     

  • And I was referring to other ways records can get locked.

    Nothing personal intended, just wanting to make sure this thread covers all bases.

  • Frank,

    totally agree with you. client statment cache is not helping here. and these locks are downgraded to sharelock. so we just have only one option left that us enabling 4GL Trans logging.

    Is there any other options? Please advise.

  • Use a tool like Riverside's Sonar plugin to analyze your code and find all of the places where your lock and  transaction scoping is messed up.

    It will likely result in lifetime employment cleaning it up ;)

    --
    Tom Bascom
    tom@wss.com

  • Depending on what those tables are, and how they are used, you may be able to narrow down their use in a handful of programs.

    No need to worry about refactoring for the next 10 years, or needing a third-party utility like Riverside's Sonar.  Not just yet.  This requires a significant time investment to install and master, though you may need to start thinking about that.  Good coding practices, standards, testing and code review may help avoiding this in the first place.

    If you are able to define the scope of this specific problem, basic transaction and locking knowledge will get you to the root cause in no time.

    Etienne