Detecting and avoiding table locks

Observer

Does anyone have any code that can detect a table locks and return this information in code, i.e. without generating a user visible error.

i.e Result =fn(Table)

Where fn returns the lock status of a table, Result being Boolean.

This is required for Native database, Financials pre 3.7.

Background:-

We have a site with 120+ users and a large number of stock transactions being enacted by 60+ of them simultaneously. Table locks of item related tables appear to be an inevitability and slow up the system. Currently this is manageable but the plan is to bring a significant number of additional users online.

Rather then leaping to SQL and record level locking one option is to detect if a table is locked and not attempt a write to it postponing the transaction to a less busy period.

The problem is I understand that the LOCKTABLE function does not return a variable that can be read in code and acted upon.

Any suggestions would be appreciated.

Regards,

Andy :?:

Marco_Ferrari

As far as I know, there's no way to know if a table is locked, so you must choose another way to solve your problems....

Marco

Savatage

Marco Ferrari wrote:

As far as I know, there's no way to know if a table is locked, so you must choose another way to solve your problems....

Marco

Like is the server as powerful as it needs to be and is it set up to be as efficient as it could be.

RobertMo

Savatage,
your detailed answer could be helpful here:
http://www.mibuso.com/forum/viewtopic.php?t=3854

Observer

For completeness......

We have recieved the feedback below:-

We were thinking along these lines and this appears to work pre 3.xx where the feature of code units it uses has been removed.

The appears to detect a lock but not maintain it, in fact a number of operations will release it. But this does not matter as the requirement was to detect the lock and as long as it near the writing code it should work.

Reply recieved.....

I think you are right. I doubt it is possible, but I know a trick you might be able to use. You can use the fact that a "IF CODEUNIT.RUN(xx) THEN" hides any errors - including lock errors.
So if you make a codeunit like this:

OnRun()
Item.LOCKTABLE(FALSE,FALSE);
Item.FIND('-');select all

And call it like this:

OnRun()
IF CODEUNIT.RUN(87001) THEN
MESSAGE('Ok')
ELSE
MESSAGE('locked');select all

However there are two very important things you should be aware of !

First of all does it ALSO send a COMMIT to the database upon success !!
Secondly is the lock released due to the commit, so you will need to lock the table again right away with risk of it being locked in between.

Use at own risk...

---

Regards
Peter

pdj

Observer wrote:

We were thinking along these lines and this appears to work pre 3.xx where the feature of code units it uses has been removed.

What has been removed? I made it using 3.70 and 4.00...