Hi All -
I read the very helpful posts on record lock 107, and they reference changing the db_resources file / lock_retry setting. Our systems currently have 2 versions. One db_resources has no lock_retry, which should indicate the default (10?) The other has lock_retry:0, does that also mean the default?
Does anyone have an example of what we might change this to if we were trying to increase the retry wait? Or a Baan document number to review? During the holiday, our db_resource resource is out.

Lisa

Hi Lisa,

the BaaN standard/default value is 10.

And its not mandatory to modify the $BSE/lib/defaults/db_resource file. Here is our system settings and we dont have it and it still works fine.

$ cat db_resource
dbsinit:01
ora_max_array_fetch:5
ora_max_array_insert:5
nls_lang:american_america.we8iso8859p1
nls_sort:binary
oracle_home:/app/common/oracle/product/8.1.7.2
ora_temporary_tablespace:TEMP

During the holiday, our db_resource resource is out.
I am guessing you mean your baan administrator ?

You can also use the Bshell command options and increase/decrease its values -

TEST_RETRY=X --> This variable indicates how often the system must go back to a retry point at the moment of committing. This cannot be used when testing different sessions with retry points parallel.
MAX_RETRY=X --> This variable indicates how often the system may return to a retry point as a result of an abort in an update action. Default: 10


I also found that many developers dont use this option - order by with retry which might also help in these kinds of cases.

Please refer to - ORDER BY --- with retry (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_order_by)

Thanks NP
Yes, I would not modify it just for the heck of it! A client is having table locks (Error 107) during large processes. I know we need to monitor the process, and see what else is running, etc. Our db_resource resource is a fellow consultant out on vacation. Our humble test boxes here do not have the load on them that our client's machines do.
Just hoping for an example, as listed in the attached thread, of what the syntax would be for the lock_retry parameter in the db_resources file. It's not a development thing that I can change the script for.

http://www.baanboard.com/baanboard/showthread.php?s=&threadid=119&highlight=107

The client is using MSSQL, not Oracle, so the ora_* examples don't help us.

Hi Lisa,

Sorry I dont have access to MS-SQL servers we are on Oracle here. But I believe
those 2 BW options might still work for you.

I found only this 1 line from the tracing document I have -

Because the lock-retry mechanism is disable by "lock_retry:0", the number of
retries equals 1.

The syntax must be the same as other settings.

A client is having table locks (Error 107) during large processes. I know we
need to monitor the process, and see what else is running, etc. Our db_resource
resource is a fellow consultant out on vacation.

1. I guess you might have some performance monitors, tracing tools/options
similar to Oracle, Spotlight, top etc.

2. You might try to use the "as prepared set" for your selects.

Prepared set.
With this option, the entire set is retrieved before the first record is returned.
The set is temporarily stored. This option is useful when a process simultaneously
selects and maintains (or deletes or adds) records. In this case, changes must not
be visible in the selected records. The prepared set option forces a consistent read.
The syntax is as follows:
SELECT ... [from][where] AS PREPARED SET

Maximum set size and prepared set.

and tweak the BW command parameter
n -set RDS_FULL=value to define max. nr. rows transfered between client and
server as one block (good for imports/exports)

to get the maximum performance.


I would not modify it just for the heck of it!


Instead of changing the original file you can create a user specific file and test it out. Francesco, gave a new tip recently at - Environment Variables (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=446)

I think thats the best I can suggest others might have some more ideas.

For Oracle folks you can take a look at solution 159170 on
the Baan KB for help with error 107.

lock_retry:0 effectivly disables the lock_retry interface
and for Oracle defaults back to ora_timeout. I believe that there is a similar sql_timeout value as well.

The default values for lock_retry:5*100, 5*500
I belive, which is try 5 time waiting 100 ms, then trying 5 more
times waiting 500 ms, etc.

The default values for ora_timeout are {0,5,10,15,0}

The first value is for "select for updates" which is where most of your 107 errors will come from. With a 0 it will try once and immedatly error (with 107) if no lock is available.

So ideally you want something like
lock_retry:0
ora_timeout:{300,120,120,120,120}

You can't / do not want do lock_retry and ora_timeout at the same time..

I hope this helps!

Dave

Actually, come to think of it. Baan will try 11 times when ora_timeout is set.

But still 10 times with 0 wait isn't going to be that long.