Locking Issues

[HomeDawwg]

Hi Guys, Il try and explain my situation as best i can.

I have Program A which calls Program B. Program A is basically a browse widget for Table A.

Program B runs in batch mode, independantly of Program A. Program B makes changes to Table A.

However within Program A, changes made to Table A cannot be viewed until program B has terminated. I want to be able to view these changes "as they happen" in program A.

Now Program A reads the table with NO-LOCK, As does program B. Program B only modifies Table A by calling another external procedure, which uses EXCLUSIVE-LOCK. Im assuming that when Program B makes a change to Table A (via a procedure call), the record doesnt get committed until program B terminates?

Can anyone shed some light on whats happeneing here, and how i might fix it?

Cheers,

Darren

 

[TomBascom]

Program B probably has poorly controlled record and transaction scope. If you compile with the LISTING option you will probably discover that there is a transaction scoped to the procedure block. That transaction won't commit until the procedure finishes without error.

Fixing it depends entirely on the details of what you're doing in that program.

 

[HomeDawwg]

Thankyou for your reply Tom.

You are indeed correct, Table A is scoped to Program B at procedure level.

This is the basic structure of program B.

Program B:
FIND FIRST Table A WHERE RECID(Table A) = p_TableA NO-LOCK NO-ERROR.

RUN Program C(updates a value in TABLE A).

REPEAT:
Do some processing.
Read values from Table A but doesnt edit
END.

RUN Program C(Again updates a value in Table A).

Can you offer any suggestions on how to "better" my record scoping?

 

[TomBascom]

Thankyou for your reply Tom.

You are indeed correct, Table A is scoped to Program B at procedure level.

This is the basic structure of program B.

Program B:
FIND FIRST Table A WHERE RECID(Table A) = p_TableA NO-LOCK NO-ERROR.

The FIRST is utterly pointless. I hope you aren't one of those people who automatically adds FIRST whenever they type FIND.

Furthermore, since you have the RECID for Table A and do not appear to actually be doing anything as a result of the FIND, the whole statement seems pointless. The main thing that it does, at least in this code, is to scope a Table A buffer to the procedure block.

RUN Program C(updates a value in TABLE A).

Presumably you passed the recid?

REPEAT:
Do some processing.
Read values from Table A but doesnt edit
END.

If there is no update occurring then the transaction implicit in REPEAT is not needed. You might replace the REPEAT with "DO FOR Table A" or "FOR EACH Table A" depending on what "some processing" is.

RUN Program C(Again updates a value in Table A).

Which table A record is being updated? The same as the first run of Program C?

Can you offer any suggestions on how to "better" my record scoping?

You generally want to use "strong scope" when updating records. That usually means that you code something like:

do for tableName transaction:
  find tableName where whatEver exclusive-lock.
  assign
    tableName.field = someValue
  .
end.

There should never be any code inside this block which waits for user input.

If the compiler complains about that code block it is because you have record or transaction scoping issues elsewhere in the program that you should fix.

 

[HomeDawwg]

The FIRST is utterly pointless. I hope you aren't one of those people who automatically adds FIRST whenever they type FIND.

Yes i do this, not because i think its good practise, but because im working on a very large and mature program. I have been asked by other developers to always use find first, i dont know the reasoning but thats what im told to do.

Furthermore, since you have the RECID for Table A and do not appear to actually be doing anything as a result of the FIND, the whole statement seems pointless. The main thing that it does, at least in this code, is to scope a Table A buffer to the procedure block.

In the example i gave i tried to keep it simple and left out a few things. I do Use the record as a result of the find.

You generally want to use "strong scope" when updating records. That usually means that you code something like:

do for tableName transaction:
  find tableName where whatEver exclusive-lock.
  assign
    tableName.field = someValue
  .
end.

There should never be any code inside this block which waits for user input.

If the compiler complains about that code block it is because you have record or transaction scoping issues elsewhere in the program that you should fix.

Thanks heaps for that. That makes a lot more sense. I have managed to rectify my issue, thanks for your help Tom. Much appreciated :awink:

I will remember to use better record scoping like you have shown here.

Cheers,

Darren

 

[TomBascom]

As a coding practice automatically adding FIRST to every FIND is misleading and dangerous. It's a bad habit and a dreadful coding standard. I'm sorry to hear that you have been saddled with it. In case you can't tell it's a pet peeve of mine

It is often believed that FIND FIRST is "faster". This is not true. It is only "faster" if the FIND is not a unique FIND (in which case you probably shouldn't be using FIND in the first place). And in that case you are masking the fact that multiple records were selected and you are possibly making one of those records "special" in a non-relational and unreliable manner. Especially if you update it. Using FIRST to deliberately and permanently band-aid a performance problem from a non-unique FIND in the code is simply pathetic.

It isn't that FIND FIRST is never useful. It does have uses. Such as initializing a walk through a set of records with FIND NEXT when a FOR EACH or OPEN QUERY isn't a good fit.

But if every FIND should automatically have a FIRST the language wouldn't have two keywords -- there would just be FINDFIRST...

 

[neilbert-berina]

hi i dont know if its right to interfere to another thread but i cant find any same thread about my problem . . . this is the only thread i found that is very similar to what my problem is . . . i am working on a company who develop banking system . . . i have a problem also about record locking and it involves updating the same database simultaneosly . . . . weve created many module on our system and when my problem is when the system will be use there are many programs will run simultaneosly and access the data simultaneosly also. . and it will also update the database by many programs i wolud like to know what will be the effect if all the program will access the same table and update it also . . we have a field on the table that will save all the transaction sequence made by the system and all of the module will access and update it also . . what if one module uses the table and other also will use it. . . on our query we use no-lock when we will access only the table but when we will update it or create record we use exclusive lock . . . i know that if someone is using that table the other transaction will just wait until the record is not lock . . . please help me what will be the most posible solution on my problem . . . tanx very much . . . . and im a newbie on this site tanx . . .