How to find out if record is locked

B

Brandlhuber Florian

New Member
Hello community,

by a PHP Application i want to update a dataset inside a Progress-Table, that is working generally.

But it is possible to work on the same dataset inside our EPR-System that locks the dataset. Now when i want to update the dataset this i get this error.

SQL error: [unixODBC][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table PUB.PP_Auftrag

My question now is, how can i find out, if that dataset is locked?
I already tried to read this statement:

SELECT TOP 100
*
FROM PUB."_Lock"

After searching for solutions i found a lot of hints which says that this is not a good idea?

Is there any other system table i can select?

Best Regards
 
That is not how locks and transactions work.

Querying system tables is not an appropriate strategy. Aside from all of the performance issues and the difficulty of actually finding the records associated with your dataset -- the lock state of the records can very easily change between the time when you check _LOCK and when you actually update your dataset.

You need to handle lock conflicts like any other error condition.
 
I know that i should not use that system table and i know that there are performance issues.

My question was how to find out by SQL if the record is locked by another transaction.

I need to know, when the update fails by the Apache side in order to display that to the user.
On our error lock of course i see that the record might be locked, but not the user!!
 
You cannot succeed by checking for locks before executing your update. There is always a period of time between the checking and the update when someone else could lock the data. The reverse is also true - any locks that you happen to find could be released right after you find them and you would be falsely telling the user that they cannot execute their transaction.

(That doesn’t stop people from trying to do such things... and it might even “work” a lot of the time. But you still need to be aware of and be ready to handle the potential errors. And if you do that the properly the “pre-check” is kind of pointless.)

The proper pattern is to request the locks and process the update if the locks are obtained (this is implicit in your unshown SQL, in 4gl code it tends to be more explicit). If the update fails because you did not get the locks you handle that the same as you would any other error. (Presumably you are not merely allowing errors to be propagated directly back to users and your PHP has some sort of error detection and handling.)
 
You must log in or register to reply here.
Back
Top