Create, exclusive-lock

[GregTomkins]

Given this code:

REPEAT:
    CREATE foo.
    ASSIGN
        foo.p_dt = TODAY
        foo.p_logical = YES
        foo.p_int = 0.
    UNDO, LEAVE.
END.

Assume 'foo' has a unique index defined as the three key fields listed above, and no triggers or anything like that. If you run this code more than once simultaneously, you are obviously at risk of getting error 132.

The question is: under what circumstances will you also get a lock conflict? The answer is not "none"; if you run this simultaneously and repeatedly, you get continuous error 132's and scattered lock conflicts (seemingly, one every few seconds).

Why is that? Does Progress do some kind of internal FIND EXCLUSIVE-LOCK prior to a CREATE? Any ideas? (If it were as simple as that - I tried running a separate procedure that does a CREATE and then blocks, then ran the above code - you'd expect continuous lock conflicts but in reality, it didn't get any at all).

This is 9.1E - sorry, Tom!! - I could try this on a more recent version, but that is a bit of a hassle and I doubt it would make any difference.

 

[TomBascom]

At least it's 9.1E

I don't have an answer for you. It looks like an interesting and exotic problem that I'd like to dig into another time though

 

[GregTomkins]

Based on P37194 and 9.1E experimentation, here is the answer, or at least my theory of an answer:

1. Session 1 creates record X and assigns key Y.

2. Session 1 UNDOes the create.

3. This causes Progress to take a table (TAB), not record, lock:

Record Locking Table:
Usr Name     Chain #    Rec-id    Table Lock Flags
 41 gregt      [B]TAB[/B]  6662         0        1 IX    L

4. While session 1's transaction is still open, session 2 creates record Z and also attempts to assign key Y to it (eg. the identical key as session 1 used).

5. Session 2 will hang on error 121 until Session 1's transaction completes. Voila, a lock conflict on CREATE (actually, on ASSIGN).

Other notes:

A. A table lock implies (to me) that nobody else can do anything with the table. However, P37194 suggests that TAB locks are only relevant to P4GL DELETE operations. It appears they also pertain to UNDOne CREATEs, which I suppose are a type of DELETE.

B. P37194 also implies that other users would be prevented from getting table locks, and thus doing DELETEs or CREATE UNDOs; however, it appears from experimenting that other users are generally not affected and can do normal CREATE, UPDATE, DELETE operations despite the existence of the TAB lock.

C. The exception appears to be an attempt to create a new record with the same key as another record that is in the process of being DELETEd or UNDOne.

D. If you attempt to do a FIND on the waiting-to-be-deleted record (eg. the one created by session 1 above), it will fail. And, of course, there is no such thing as an 'ASSIGN NO-WAIT', so, there is no apparent way to prevent this problem, other than, of course, by being careful about key value assignment and delete / undo logic