Hello all,
I have the following Issue on my Production server:
In some cases, from the database side, I see various blocking locks. This is, some sessions are blocking each to other. To solve this problem I have to kill the blocking session. I perfectly know that the locking process are a normal issue on Oracle databases and in others. But also I know that the locking mechanism is managed from the session´s code... So my question(s) is(are):

how do you deal with this issue? Is there any kind of bug that I am missing?
this issue can be solved installing some Oracle or Baan Service pack?
Or maybe there are a parammeter on Baan or Oracle that can I set to avoid this problem?

Thank you in advance.

Hello gurus,
Any advice? It is hard to belive that I am the only dumb, who is having this issue...

...It is hard to belive that I am the only dumb...

I am surprised that you have "deadly embrace" issues in this day and age. Oracle is the "rolls royce" of rdbm's and BaanIV is very good at transaction-handling.

Could you provide some specifics on the actual problem you experience ? Baan session names and tables involved and whether the tables are accessed by other than baan programs, whether access to the database(s) is remote to the application box.

Terry

Hi

May be the session has long runing transactions and Oracle at your side is not properly configured for that, or Oracle is not configured to identify the dead locks at table or row level and terminate the dead lock transactions automatically. May be you can check with the DBAs at your side. I dont think that it could be the Baan application side problems, or it may be because of batch jobs having long transactions.

MRF

Hello All,
I have indentified a strange situation where the blocking blocks happens. In two situations:
1.- In the process of finalice the batches.
2.- When runing a TRMS transactions and a user are invoicing. (customized session).

The point two I have already reported to SSA in order to avoid this situation. This situation apppear due to a long select to my sales offers. So I requested that after a certain number of records readed, the system commits the transaction and follow the next block.

But in the point 1 it seems more complex. It seems like a user put a exclusive block for a certain group of records and it "cycles". Due to others users have to access to the same group of records the system "hangs" waiting for the release of the exclusive lock. I agree with mostrightfuture.... the system can´t determine the deadlocks and release the resources....

Seems to me that is a DB problem. I searched the internet and found this presentation. What is your oppinion about that?

Regards.

Hi mig28mx,

The presentation is quite useful and explains the locking strategy in Oracle for the data consistency in different situations. This presentation is more useful for the DBAs not for the application developer or users.

Problem is quite clear that the process initiates long transactions. I will check and tell you more possibilities from DB side to overcome this problem. Meanwhile you can try following:

Finalize one batch at a time and check if problem comes, and if not then increase the batches count and try again.

MRF

Dear mig28mx,

as far as i know oracle (and i understand your question right):

The feature that on session blocks the other is only possible within a oracle db, other dbms will give you an error.

Only oracle waits until you e.g. commit on transaction, so the other can proceed.

A basic sample from oracle training:

update on row

(without commit)

update the same row within a seperate process

commit -> the commit will wait until the first update will be commited.

Regards,

Kozure

Yes! you are rigth!
Thats the ideal transaction for lock a record and release. But in my environment that seems it hangs in some specific situations.

I belive that Oracle will handle all these deadlocks and releases the resources but It seems like in some circumstances this mechanism not work.

I have identified these two situations and one corresponds to a customized session. The other is more strange due to is a standard operation in baan and I belive that Oracle have to have the capacity to deal with this issue...


Regards.

can you post your db_resource and ora_storage?

Dave

Hello Dave,
Here is the files.
Thank you in advance.

Assuming you're on unix your db_resource should look closer to

dbsinit:021
ora_init:0101000
lock_retry:0
ora_max_array_fetch:5
ora_max_array_insert:5
ora_timeout:{9000,900,900,900,900}
rds_full:5
sst_set_rows:5
oracle_client_home:/apps/baan/bse/lib/ora/oracle_home
nls_lang:american_america.we8iso8859p1
ora_column_format:8

and that's it.
depending on your porting set version you may or may not need ora_column_format and oracle_client_home...

Dave

Thinking aloud so don't try this directly but first think about the consequences...

When setting ora_timeout, the database will wait up to a certain maximum amount of time to do the transaction. When ora_timeout is not setting, the DML operation will be activated with a NOWAIT option and thus if the lock can not be set directly, the query will go back to the driver and retry according the settings of lock_retry.

You have set both parameters lock_retry and ora_timeout. To find the locking on Oracle level, I recommend removing the lock_retry parameter. On the other hand, maybe the end users that suffer from this locking issue are helped by disabling the ora_timeout parameter. This can work in theory, so if it does not work in your situation, feel free to share, but don't blame me. It is al user, session and usage dependent.

As you already did see in the presentation on locks: measuring the locks is very important. Start with the Oracle tools and use e.g. the Baan Call Graph Profiler or the BLAT tool to find the locking point in the application. See your tech manuals or http://www.baanboard.com/baanboard/attachment.php?attachmentid=1534&d=1086353671 for more details on how to use these tools.

I hope you can share your results with us to see if we are able to find a cure.

Hope this helps,
Dick

Thank you very much Dave & Dikkie,
I will take your suggestions and I will share with the board the resuts.

Thank you for all your support.

Can I take your comments as a confirmation that this problem should be resolved from the Database side? I mean, in the process of batch finalization not matter how many users I have concurrent, I have enough hardware resources, the lock issue is not caused directly by the application.

Regards.

The problem should be solved in the application, enduser, data or database: applications are setting the locks, but if every body is not using the same data that is no problem. So, on what you wind with the traces (database and Baan) you can determe the exact problem.

Hope this helps,
Dick

Dear mig28mx,

maybe the following link is helpful for you to identify the reason.

http://orafaq.com/node/854

Is all done with oracle views, no need for external software.

Regards,

Kozure