I would appreciate it if anybody can give any kind of input with 107 lock errors.

We get 107 lock errors very regularly while entering sales orders. Whenever a user gets stuck in sales order entry and has to kill the process, the process holds lock(s) on item master. Bt the time we realize and go into informix and clean this lock out, several other users are stuck due to contention with the same item.

Here is my question: Assuming a user is stuck in sales order entry, why should there be lock? All of baan is designed to work with delayed locking. So, if a user gets stuck, ideally there should be no locks on any tables as the commit has not happened yet and other users should not get affected. Can anybody explain this?

Something to test in Baan: Write a select on any table with "for update". Then write another select on any other table with "for update". let this program execute untill the second "for update" statement and dont commit. Do this in debugger mode. Then write another program with "for update" on the same table as the first "forupdate" of the first program. Execute the second program and try to commit ( while still sitting before commit on the first program) and you will get a 107 error. Why should the second program get a 107 error? Baan has delayed locking, so no physical locks should be placed untill we hit the commit on the first program.

example:
1) first script
db.retry.point()

select tcmcs010.*
from tcmcs010 for update
where tcmcs010._index1 = {"ABW"}
selectdo
tcmcs010.dsca = "ABCD"
db.update(ttcmcs010,db.retry)
endselect
select tcmcs021.*
from tcmcs021 for update
where tcmcs021._index1 = {"175"}
selectdo
tcmcs021.dsca = "Trinidad"
db.update(ttcmcs021,db.retry)
endselect |stop here
commit.transaction()

2) second script:
db.retry.point()

select tcmcs010.*
from tcmcs010 for update
where tcmcs010._index1 = {"ABW"}
selectdo
tcmcs010.dsca = "ABCDE"
db.update(ttcmcs010,db.retry)
endselect
commit.transaction() |fails here with 107 error.
Ramireddy

Gotta love Baan.. I couldn't find any information in the Tools help on why you'd get a 107 error in that situation. However, I did find some mention in the Baan Application Performance Guide (M2017B). Apparantly, a nested query can cause a physical lock to be set on the record before the commit.transaction(). Excerpt from Chapter 4 Database transaction handling:


4.2 When does a database transaction start?

Problem

As can be seen in the previous chapter, a commit.transaction() will:

Set a physical lock on the records from the before image
Do a comparison of the before image and the locked records
Update records
Perform a physical commit in the RDBMS

There are some situations where the first three points are carried out before the commit.transaction() is called, namely:

If a select is done in the select for update loop (nested queries)
if a db.insert() with flag db.return.dupl is implemented
If the number of updates reaches a certain maximum (depends on the size of the internal buffer)


Solution

It is not known in an application when a transaction starts.
A safe point of view is to assume a transaction starts at the statement select for update.

I just noticed that the second select in your first program is not even nested in the selectdo of the first.

Do you get the 107 error if you eliminate the query on tcmcs021?

Nope...If i take out the second select on tcmcs021, i do not get a lock. That is what perplexes me more.

I undestand that the transaction starts as soon the db.retry.point() statement is executed, but that does not mean that the updates with retry's should have actual physical locks on them.

Ideally the phyical locks should be there only for a fraction of a second when the actual commit happens into the database. And if that same second some other program is trying to commit then that select should fail with 107 eror, but i cannot believe that this could happen 10 times and come out with a fatal error, it is simple not possible. Of course, that is proved with the above piece of code that i wrote.

Ramireddy

We are trying to get rid off 107 error related problems for months. Even Baan Support had'nt resolved our problems completely, although they have done changes in our parameter files we didn't know were possible.
Baan doesn't rely on the database (we use Oracle) for its locking scheme. This is why it uses a "timeout" behavoir: to avoid deadlocks, because it doesn't know how to manage them in the program. This could be a "least worst" option when working with an isam like Triton, but it's almost inadmissible when you are using a true transactional database (such as Informix or Oracle).
This leads to many problems. We have high concurrency (about 47 users) on order entry, which causes 107 errors over the table who holds the "free numbers" of the order entry series, because the program locks the series record until the full order is done (to avoid "holes" in the serie), which in turn causes timeouts (107 errors) in pending requests over this record.
About your scripts, we suspect that, because the locking is managed via software, a "select for update", in Baan, is really a logical full lock table, managed via the application (I cannot imagine the alghoritm involved in accounting delayed locks on "selects for update" via application, but this is a bussines software trying to act as a database software).
We asked Baan Support about this. The haven't come with a reply yet.