Hi All,

We are experiencing a table-locking problem. Has anyone experienced something similar and if so what would you suggest.
Thanks in advance. We are running ERP IVb, Oracle 8, on a SUN Solaris box.

Problem:

We've verified the first table involved in the problem is the "Numbers" table, TTCMCS050100. A shared company 100 table that supplies unique sequential numbers for documents such as work orders and purchase orders. The first user that locks this table never releases the lock and every user after the first can't continue because the first user is not releasing the lock. The first user never releases the lock for some reason.

We've tried to correlate the network slowdowns with the occurrence of the problem, but there does not appear to be any correlation.


We intend running the following trace :

DBSLOG=3001570

Row action info
Table action info
Transaction info
DBMS input/output data
DBMS SQL statements
General debug statements
Lock retries logged (includes session name)
Logs successful locks and longest lock duration in a transaction

First impression: strange.

Baan uses delayed locks so locks should be hold only very short.

In IVb there was a problem with this due to the many references it had, in IVb4 it was solved (if I remember well). At that time I had to install many fixes. However the locks were given free as soon as the transaction was committed.

It this a new problem since an upgrade? IVb doesn't sound like a new installation.

Can the problem be reproduced? Do you actual see that locks are held if you query v$lock?

Han

v$locked_object is usually more helpful in determining on what object and by who the lock was created

Hi Han, Patrick,

Thanks for the replies. Basically we cannot reproduce the problem as there are a no of plants which access the table 24/6. ERP IVb was the original installed version of BaaN. Han you mentioned table locking problems in ERP IV and the fact that you installed a no of fixes, have you any more info on this problem ?
Thanks in advance.

Kevin

Kevin,

As far as I can remember the problem was caused by the fact that the references to the common tables (like countries etc) were to strict.

Han

Kevin,

I agree with Pat that the first thing you should check is the V$LOCKED_OBJECT table. The DBSLOG you are suggesting will generate a huge trace file.

What I find curious is that in the last few weeks I have had 3-4 customers all ask questions about problems with the delayed locking. I wonder if Baan recently introduced a problem in the latest porting sets -- what port are you running on?

-- Jim

Found an interesting debug option on BGS. Haven't tried it but I guess it could be helpfull.

Han

Hi Jim, Han

Thanks for the info. Our port set is 6.2a.03.03. What's unusual about the problem is that it only occurs in one company, All other companies access this table but they don't get the locking problem. We are able to see who is locking the table, and what is being locked but we have to kill it manually. Any insight is much appreciated !

Rgds,

Kevin

Kevin,

Is this one company by any chance linked to a different VRC? In that case it might be using a different code base to access that table.

The DBS_LOCK_PROF variable sets the threshold for lock logging which must be turned on in the DBSLOG.

One company -- can you describe the multi-company model?

-- Jim

Hi Jim, Pat,

We have 6 production companies ( so we are 1 of 6). Originally I thought it might be the VRC but we are all using the same one, no customizations. Looking a the problem again it seems to coincide with companies overseas coming online, and as mentioned focuses around table TTCMCS050100 - a shared table from our company 100. Thanks for the responses.

Rgds,

Kevin