We encounter an error message while running session
tfgld4410m000 (Print Integration Transactions) on occassions since moving from B2 to C4
A Typical fix is to get our Finance users to stay out of Maintain Transactions
We also encountered the same error message in some queries
which had been migrated.
Is it a db_resource setting

I would appreciate if you could advise on what we need to do.
Error Record is locked - 107

Jordan,

I would advise to tackle the problem in different stages:

first:

check for application patch/update:
the majority of these problems tend to be caused by *bugs* in the session coding, meaning they create bad SQL queries that cause date to be retrieved or update in a wrong sequence etc.

then:

experiment with a customized db_resource:

the parameter you should look at int the first place:

lock_retry: for specifying how many times the system should retry to obtain a lock and what time it should wait in between. For Oracle, this parameter has been deprecated in favour of ora_timeout

You can ask some users to try to use this custom db_resource so that others are not affected.

If you are using Oracle, have you checked the ora_init setting? One of the parameters is the optimistic locking approach.

Furthermore you can have this problem if references have to be maintained, somewhere between c2 and c4 Baan has removed a lot of them because of this error and in order to get a better performance.

I have played with the db_resource settings
i.e. ora_timeout and lock_retry for specific users.
Nothing conclusive here.

Apart from moving to C4 from B2
We also move from Oracle 7.3.4 to 8.1.5

Aidan

Hi Jordan,

"Record is locked - 107" is pretty normal - and just means you have to "wait" until the other process is finished for the sake of data integrity.

You can change the 'behaviour' of this waiting using the db_resource file (or even a customised db_resource as Patrick mentions).

Regardless of this 'waiting' and 'retrying' behaviour, you always have to wait for the other session/process to do its' business.

Did you check with Baan Support for fixes on the session you mentioned?

Did the error occur after upgrading to Oracle8.1.5?

Yes,

I am working through this with Baan support ,
currently we are on SP6 for Baan C4

So apart from upgrading to Sp8, there have suggested changes to db_resource

Thanks , I will work through further with Baan on this

Aidan

Seams to be a problem of the Oracle optimizer. Work around is to dump the table, delete it, create it empty and bdbpost -A (append) the data.

Gerhard,

Regarding your post: do you mean that the Oracle Optimizer is choosing wrong execution paths? By suggesting to export and re-import the tables I would assume that this happens based on skewed histogram data which can occur if the tables are not regularly analyzed.

Aidan, do you run a compute or estimate statistics on a regular (weekly recommended) basis on your Baan tables?

There is something that also has to be checked, that is the value of ora_timeout in the $BSE/lib/defaults/db_resource.

If the value for selects is too small, then the driver will cancel the select and Baan will think that it didn't get the result because of a lock, and issues the error 107.

We encountered this same problem on various report sessions after migrating to Oracle 8.0.6. We reported it to Baan and they told us it would be fixed in the next porting set. Meanwhile we bumped up our ora_timeout setting in the db-resource file (2000, 600, 600, 600, 600). I also discovered this error occurs most frequently with Oracl 8 if your report is doing a select on multiple tables and has a group by clause in it.

Baan support never queried us about our porting set
Porting set : 6.1c.04.02

What's the latest version?

we changed our db_resource setting for ora_timeouts
but this made no difference.
We have tried different set of db_resource settings
It is an intermittent problem

Aidan

6.1c.06.02 is already released (27.7.2001)

Hi Aidan,

It is unlikely that this problem is related to Porting Set Version.

Like mentioned above, changing the ora_timeout setting in the db_resource will not solve the problem but will change only the behaviour of the problem.

Indeed the problem is intermittent since it is determined by other processes/sessions temporarily accessing and locking the required records.

Ensuring Finance users are not using sessions such as Maintain Transactions should solve the problem. Increasing the 'wait' times for the ora_timeout variable may alleviate the problem somewhat by allowing the session to wait for the lock to be released - but this variable is global and will affect all sessions. In some cases it is desirable that ora_timeout values are low and set so that no waiting occurs.

NB. I am unaware of the Oracle Optimiser problems Gehard mentions, but its maybe worth trying the suggestion of re-importing the finance table if you find the problem is unrelated to finance users locking records.