Matthew Vienneau (Customer) asked a question.

Edited June 19, 2026 at 2:54 PM
Unexpected record locks - hoping that someone can help explain what's going on.

Environment is AIX Unix v12.2.17, CHUI.

 

I’ve encountered an issue that’s confounding me and it’s a bit complicated. I’ve spent a few hours distilling it down to the core code and I’m hoping someone has the curiosity (and knowledge!) to try and help me out. I've been doing this for 30+ years so it's kind of exciting to find something I haven't seen before, though I suspect I've seen hints of it over the decades (some index corruption) and not realized it.

 

The essence of the issue is that I create a record in one session and lock it, then I create a record in a separate session and try to find the second record. When I do that, the second session gets held by the first session, but only if if the first session also ran a few find statements (confused yet?).

 

First, let's set up the problem:

 

Create a table called “ve” with two fields:

 

Vehicle-no (integer)

Vin-no (char)

 

Add two indexes:

 

Vehicle-no (primary, unique, on vehicle-no)

Vin-no (unique, on vin-no).

 

In one session, run this code:

  1. create ve.
  2. ve.vehicle-no = 1.
  3.  
  4. run du18r( recid( ve)).

du18r:

  1. def input parameter ve-recid# as recid no-undo.
  2.  
  3. find ve
  4. where recid( ve) = ve-recid#.
  5.  
  6. run du18s( recid( ve)). // "GREEN"
  7.  
  8. ve.vin-no = "ABC". // "BLUE"
  9.  
  10. update ve.vin-no. // Holds the record lock
  11.  

du18s:

  1. def input parameter ve-recid# as recid no-undo.
  2.  
  3. find ve no-lock
  4. where recid( ve) = ve-recid#.
  5.  

This creates ve record #1 and locks it. It also does a bunch of record finds for the same record, which seems meaningless, but appear to be core to the issue.

 

In a separate session, run this code:

  1. create ve.
  2. ve.vehicle-no = 2.
  3.  
  4. run du18f( recid( ve)).

du18f:

  1. def input parameter ve-recid# as recid no-undo.
  2.  
  3. find ve no-lock
  4. where recid( ve) = ve-recid#.

This creates a ve record #2 and attempts to find it. But when it does, and this is the issue, you get this:

 

ve in use by mviennea on pts/1. Wait or press CTRL-A to stop. (121)

 

The record is locked by the other session. But not only are we searching with “no-lock”, we’re looking for record #2 and it’s record #1 that’s locked. Record #1 is somehow locking record #2.

 

Furthermore, and this is where things get really crazy:

 

If you comment out the "Green" line, the record doesn’t get locked.

If you comment out the "Blue" line, you get a different failure and vehicle 2 isn’t created:

 

** ve already exists with VIN "". (132)

 

If you move the Blue line above the Green line, then you don’t get the record lock.

If you change the vin-no index so that it’s not unique, then you don’t get the record lock.

 

To summarize without the code:

 

  1. Create record
  2. Call a .p and find the same record
  3. Call a .p and find the same record, then lock it.
  4. Create second record.
  5. Call a .p find record #2

 

…you get locked. But if you skip step #3, or assign the vin-no before step #3, you don’t get locked.

 

This greatly confuses me. How does the number of times I call separate .p code impact the lock status of a record?

 

And why does ve.vin-no having a value impact the lock status, but only when there’s a unique index on the field?

 

For that second question, I have a vague idea of an answer. Somehow the “find” statements are validating the vin-no index even though they’re not using the vin-no index. I’m guessing a metadata table is doing a check and saying, “hold on, we don’t have uniqueness while we wait for record #1 to be saved so let’s lock everything until that’s resolved”.

 

But why does it only happen when I nest two .p “find” calls in the original record creation? Why doesn’t it happen when I just do it once? Does OE have a rule where if you go two levels down in .p code and run a find statement then index validation is performed?

 

Anyway, as I said, I'm hoping someone has the patience to wade through this and offer insight or advice. Hopefully I learn something new or realize that I've misunderstood something all these years!

 

 

  • 18 answers
  • 240 views
Peter Judge and Patrice Perrot like this.

  • Dmitri Levin (Customer)

    The minimal code that reproduces the issue:

    Session #1:

    1. CREATE ve.
    2. ASSIGN ve.vehicle-no = 1.
    3. FIND CURRENT ve EXCLUSIVE-LOCK. // Comment out this line
    4. ASSIGN ve.vin-no = "ABC".
    5. MESSAGE "Run session #2" VIEW-AS ALERT-BOX.

    Session #2:

    1. CREATE ve.
    2. ASSIGN ve.vehicle-no = 2.
    3. FIND CURRENT ve NO-LOCK.

    Session #2 issues the message 2624 on FIND CURRENT:

    ve in use by <user> on <tty>. Wait or choose CANCEL to stop. (2624)

     

    But we will not get this message if FIND CURRENT in the first session is commented out.

    FIND CURRENT creates the key for ve.vin-no with default value.

    ASSIGN ve.vin-no deletes this "just-created" key.

    But a transaction is not yet committed.

    Selected as Best

  • Dmitri Levin (Customer)

    > ve.vin-no = "ABC". // "BLUE"

     

    ** ve record has NO-LOCK status, update to field not allowed. (396)

    ** Unable to update ve Field. (142)

     

    Even without 'run du18s( recid( ve))'.

    • Matthew Vienneau (Customer)

      Oops! My typo. I've edited the du18r code to not have the "no-lock" on the ve find.

  • Frank Meulblok (Customer)

    All this is probably due to difference in buffer scopes and record scopes, and how those align with transaction scopes.

    The symptom that in some cases you can update a record despite explicitly reading the buffer with no-lock within the same routine is a tell-tale sign of that.

     

    Is any of this in different external/persistent/super procedures/classes by any chance ? Because that can complicate these scenarios further.

     

    > How does the number of times I call separate .p code impact the lock status of a record?

    This can affects the number of buffers the record is in, and the scope of those buffers starts and ends, and when implicit validates/index updates etc, occur.

     

     

    If you don't feel like untangling this, but want to solve the issues it causes:

    Rework the code to do the creates and updates in a temp-table first, and only when it's complete create it in the database. (or in architect terms, seperate your business logic from your database access). That makes it much easier to control/scope when the database activity occurs.

     

    • Matthew Vienneau (Customer)

      Yeah, I'm guessing that it has to do with scoping. I'm not a master at defining scopes but that's because it rarely comes up for me. That's why this is so intriguing.

       

      Each of these code snippets are simple .p code that call each other. Nothing is being defined as persistent or anything.

       

      30+ years ago I thought it would be a lot more efficient (less memory required for variables and work tables, as temp-tables weren't necessarily a thing) to update the DB fields directly. These days I'm more prone to updating a temp-table and then doing a single write.

       

      But as a result of that earlier design, it's complicated to separate the updates out like that now.

       

      I can definitely figure out a different way to do this. The issue was triggered because I re-factored some code and centralized some logic. I can always de-centralize it again. But I hate reversing work. And I really do want to understand how this works.

       

      Fundamentally, I get the gist of it, I think. I can't search for a new (unsaved) record that is not unique if there's another new (unsaved) record that's not unique, even if if I'm not using the unique index. And this relates to searching in a separate procedure from the one that created the second record. I just wish I had the specifics so that I could see if there's a workaround trick, and to make sure the rest of my system doesn't have the issue.

      • Frank Meulblok (Customer)

        Well, after having a second look, and piecing together various details, I think I know what's happening.

         

        At certain times, such as when a buffer gets released or goes out of scope, the index keys for a record get created (at least for unique indexes). This also happens for new records, where those keys are effectively placeholders.

        If this happens when the key values in the record are not populated yet, you end up with an index key for the record's default values.

         

        This opens up a potential timing issue:

        • session A creates a new record, does not populate the key fields, triggers the creation of index keys, and pauses/delays.
        • session B creates a a new record, does not populate the key fields, triggers the creation of index keys.
        • The key creation from session B is triggered while session A is delaying, so now there are two sessions trying to create the same key based on the same default values.
        • And creating the same key twice in a unique index is the point where the error 132 is raised.

         

        Setting the key values immediately after the initial CREATE would avoid creating the keys for the default values, so that would solve the illigitemate/unexpected 132 errors.

         

         

      • Matthew Vienneau (Customer)

        I agree with your analysis for why the 132 error shows up if I don't assign a unique value to at least one of the two records.

         

        It's the 121 error that's the issue. The 132 error info is extra detail in case it helps people figure out why the 121 is happening.

         

        Why is record #2 getting locked by record #1 when I call a separate procedure and do a no-lock search on record #2?

         

        Expanding on you're describing, my guess is that the session B is trying to create an index key while session A still has an unsaved (but non-blank) index key value. Because it's non-blank, it's not a conflict like the scenario triggering the 132. Instead it's a comparison of a blank value (session #2) and an unknown value (session #1) and so OE waits for the lock to resolve to avoid a future conflict if the unknown session #1 value ends up being blank (matching session #2).

         

        I'm not familiar with how/when index keys are created, but what you describe certainly seems reasonable and appears to fit what I'm seeing. Doing a no-lock search on the record in session #2 must trigger an index key creation/validation step, even if that's not the index being used. Merely searching for it must require its creation and/or validation across the board.

         

  • Dmitri Levin (Customer)

    Progress works as expected.

     

    du18r procedure is called inside a transaction block.

    The find operator releases a newly created record with the empty vin-no filed.

    The lock is downgraded to the exclusive limbo lock.

    Then the find operator re-reads the same record and updates its lock back to the exclusive lock.

    The assign operator (ve.vin-no = "ABC") updates the local copy of the record - the copy on the client's side.

    Transaction is not yet committed and a database still keeps the record with empty ve.vin-no field.

     

    Second session creates another record.

    The find operator in the du18f procedure tries release a newly created record with the empty vin-no filed but the index on vin-no filed is unique and the record with empty key is already exits.

    You are getting the error:

    ** ve already exists with vin-no "". (132)

     

  • Dmitri Levin (Customer)

    > It's the 121 error that's the issue

    1. promon Status: Lock Table by user number for all tenants
    2. Usr:Ten Trans id Type Table:Part ROWID Flags Trans State Ref_Counts
    3. 1 416 REC 10:0 805 X Active FWD 1 6
    4. 1 416 PART 10:0 0 IX L Active FWD 0
    5. 1 416 TAB 10:-32767 0 IX L Active FWD 0
    6.  
    7. 2 417 REC 10:0 806 X Active FWD 0 3

    It's a table lock that causes the error.

     

    What can cause a Table Lock (TAB IX)?

    https://community.progress.com/s/article/P37194

     

    First session creates a table lock right after 've.vehicle-no = 1'. But the second session will rise the error 132 at any time before 've.vin-no = "ABC"'. After this assign statement you will get the error 121.

    • Matthew Vienneau (Customer)

      Wow - good searching to find that KB from 2012.

       

      I think it's a bit different (but surprisingly close) as it seems specific to the delete command. In particular, I get error 132 after ve.vin-no = "ABC" unless that line happens before the call to du18s. And the lock is only triggered in the second session if I first do a search for the record via du18s in the first session. It's that find by recid statement in du18s and it's intersection with vin-no = "" at the time that's the issue.

       

      This did give me the idea of running promon to see the exact locks. But it's surprisingly not much help:

      1. Record Locking Table: by user number for all tenants
      2. Usr:Ten Name Domain Chain # Rowid Table:Part Lock Flags Tran State Tran ID
      3. 1 mviennea 1 REC 77 131840513 261 EXCL Active FWD 1005928208
      4. 2 mviennea 1 REC 78 131840514 261 EXCL Active FWD 1005928209

      I'm not really familiar with this display, but it seems to be showing two separate exclusive locks. And even if I comment out the call to du18s so that the lock doesn't happen, this record locking table doesn't change. Maybe I'm missing something.

       

      My guess is that it relates to buffer scope and index values. By calling du18s, the session #1 scope expands in some way that requires an index key to be written, which locks the table such that session #2 can't access it.

      • Dmitri Levin (Customer)

        > I think it's a bit different (but surprisingly close) as it seems specific to the delete command.

         

        Your code deletes an index key with ve.vin-no = "" when it assigns ve.vin-no = "ABC".

        The code in the knowledge base also deletes index keys in addition to deleting the record itself.

10 of 18