Lock table record (-L) startup parameter

[philipped]

Hi,

I'm using Syteline 5 ERP with a Progress 9.1D database wich is ancient, obsolete, unsupported and buggy. Yes, I know Tom!! .

A user is performing an operation that result in a "Increase -L parameter" error. After investigation, I have found that the program (wich is a standard program, not a custom one) need to lock 57000 record in one table. So I would have to set the -L to 60000 (at least) for the operation to succeed.

My question is: Do increasing that parameter affect database performance?

 

[TomBascom]

Awareness is the first step

There is no direct performance impact.

Indirectly you're using a little bit of memory that could, perhaps, be put to better uses but that's really kind of immaterial.

 

[rzr]

do you "really" need the 57000 records locked at a time?

 

[rstanciu]

to known the value of -L
promon database
Option: 6 (Shared resources) and
look to -> Locking table high water mark

 

[MaximMonin]

we use sometimmes -L 150000 + for documents with 1000+ lines

 

[philipped]

do you "really" need the 57000 records locked at a time?

Sadly, yes.

We are a air-jetted bath manufacturer. We offer many option with our bath (lights, armrest, overflow...) wich result in more than 50k different baths created in the database since we starts using SyteLine.


Since we want to modify the recipe of an item that is used in all baths (a certification label that is fixed to the bath), almost all baths must be marked as "to be updated" by the BOM cust roll-up program that runs every night. In fact, the main cause for that issue is the fact that SyteLine doesn't ignore baths that have an Obsolete status.

But I have another option : I can easily modify the item directly in the database. But I'm not a fan of modifying data directly in the backend. In fact I want to avoid that as much as possible.

 

[philipped]

to known the value of -L
promon database
Option: 6 (Shared resources) and
look to -> Locking table high water mark

There is something i definitely miss about that parameter:

Remember that I have less than 70k items in my database.

The following code:

FOR EACH ITEM EXCLUSIVE-LOCK WHERE ITEM.net-change = TRUE:
    ASSIGN ITEM.net-change = FALSE.
END.

Result in this in the Locking and waiting Stats option (was 30 before the execution of the code):

As I can understand, this is an accumulation of lock, not a total number of lock at the same time. But I can't undestand how it went > 100k with 70k items in the table?

And the Locking table high water mark value is:

Where does this come from?

Can I use this number to evaluate what should be my -L parameter?

I tough a minimum -L 100000 would have to be 57000 in the first place, by looking at Locking and waiting Stats screen. I think I was wrong..?

 

[MaximMonin]

FOR EACH ITEM EXCLUSIVE-LOCK WHERE ITEM.net-change = TRUE:
    ASSIGN ITEM.net-change = FALSE.
END.

FOR EACH ITEM WHERE ITEM.net-change = TRUE:
  do transaction:
    ASSIGN ITEM.net-change = FALSE.
  end.
END.

 

[TomBascom]

That's not going to change anything.

 

[TomBascom]

Depending on the index used Progress may need to lock multiple records.

If your record scope or transaction scope are wider than the snippet of code shown then you may also need more locks.

For setting -L the high water mark is the metric to look at.

-L needs to be set large enough for all users -- not just one particular user.