Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 4th September 2006, 18:42
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
Blocking blocks on oracle.
Baan: Baan IVc
DB: Oracle 9.0.1.x
OS: HP-UX 11.00
C/S: Both

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.
__________________
We talk Baan!
Reply With Quote
  #2  
Old 6th September 2006, 23:40
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
Any advice?

Hello gurus,
Any advice? It is hard to belive that I am the only dumb, who is having this issue...
__________________
We talk Baan!
Reply With Quote
  #3  
Old 7th September 2006, 02:35
tjbyfield's Avatar
tjbyfield tjbyfield is offline
Guru
 
Join Date: Jun 2002
Location: Sydney Australia
Posts: 356
tjbyfield is on a distinguished road
Baan: baan4c4 - DB: oracle 805 - OS: AIX 4.3.3
RDMS lock or Baan Lock ?

Quote:
Originally Posted by mig28mx
...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
Reply With Quote
  #4  
Old 18th September 2006, 10:01
mostrightfuture's Avatar
mostrightfuture mostrightfuture is offline
Guru
 
Join Date: Apr 2004
Location: Switzerland
Posts: 341
mostrightfuture is on a distinguished road
Baan: Baan Vc - DB: SQL Sever 7.0 - OS: Windows 2000
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

Last edited by mostrightfuture : 18th September 2006 at 10:15.
Reply With Quote
  #5  
Old 18th September 2006, 16:19
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
Blocking blocks.

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.
Attached Files
File Type: ppt Blocking_Locks.ppt (508.0 KB, 76 views)
__________________
We talk Baan!
Reply With Quote
  #6  
Old 18th September 2006, 16:46
mostrightfuture's Avatar
mostrightfuture mostrightfuture is offline
Guru
 
Join Date: Apr 2004
Location: Switzerland
Posts: 341
mostrightfuture is on a distinguished road
Baan: Baan Vc - DB: SQL Sever 7.0 - OS: Windows 2000
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

Last edited by mostrightfuture : 18th September 2006 at 16:51.
Reply With Quote
  #7  
Old 19th September 2006, 12:08
Kozure Ohashi's Avatar
Kozure Ohashi Kozure Ohashi is offline
Guru
 
Join Date: Dec 2003
Location: Tokyo-Otsuka
Posts: 324
Kozure Ohashi is on a distinguished road
Baan: Baan IV c4 SP 19, ERP LN 6.1 FP3 - DB: 10g R2 - OS: Linux SLES 10 64 Bit
Oracle feature

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
Reply With Quote
  #8  
Old 19th September 2006, 16:22
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
Hi 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.
__________________
We talk Baan!
Reply With Quote
  #9  
Old 19th September 2006, 18:06
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
can you post your db_resource and ora_storage?

Dave
Reply With Quote
Sponsored Links
  #10  
Old 20th September 2006, 01:28
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
files.

Hello Dave,
Here is the files.
Thank you in advance.
Attached Files
File Type: txt db_resource.txt (390 Bytes, 54 views)
File Type: txt ora_storage.txt (25.5 KB, 38 views)
__________________
We talk Baan!
Reply With Quote
  #11  
Old 20th September 2006, 03:51
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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
Reply With Quote
  #12  
Old 20th September 2006, 09:24
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
My 2 cents

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/a...4&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
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #13  
Old 20th September 2006, 16:21
mig28mx's Avatar
mig28mx mig28mx is offline
Guru
 
Join Date: Nov 2003
Location: Mexico
Posts: 427
mig28mx is on a distinguished road
Baan: IVc4 - DB: Oracle 9i, Oracle10g - OS: HP Unix 11, Intel RedHat ES
Thanks Dave, Dikkie.

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.
__________________
We talk Baan!
Reply With Quote
  #14  
Old 20th September 2006, 16:37
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
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
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #15  
Old 20th September 2006, 21:36
Kozure Ohashi's Avatar
Kozure Ohashi Kozure Ohashi is offline
Guru
 
Join Date: Dec 2003
Location: Tokyo-Otsuka
Posts: 324
Kozure Ohashi is on a distinguished road
Baan: Baan IV c4 SP 19, ERP LN 6.1 FP3 - DB: 10g R2 - OS: Linux SLES 10 64 Bit
Reason for locking

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
The latest from the Oracle front Francesco General Discussion & Chat 0 14th February 2005 23:37
Oracle, PeopleSoft agree merger terms Flip_J General Discussion & Chat 1 13th December 2004 14:39
Payment of blocked supplier pmahto Finance, Invoicing and Integration 1 11th September 2003 20:36
Anyone have any BAAN/Oracle security links petefinnigan Operating Systems & Databases 3 9th September 2003 22:45
Baan on Oracle Guru for Hire Will travel OracleBaanGuru Jobs and Resumes 1 19th June 2002 15:57


All times are GMT +2. The time now is 20:46.


©2001-2017 - Baanboard.com - Baanforums.com