Dear Gurus,

I wish you can help me out on a problem.
I am just new to OLTP systems and now, have just built a system that can accept and throw ISO8583 financial messages. The design of the system was basically divided into two:

1. Communications
2. Authorization

The team and I used VB6 as our main tool for creating communication modules. We also used MSMQ and COM+ but am not really sure if the later really works with Oracle 10gR2 (our backend). Our Authorization modules are composed of nested oracle procedures that accepts, parses and processes messages.

Everything was working well until we found out that there were instances that oracle wasn't able to provide output procedure values. We partially solved the problem by checking on the locks and table indexes. This only covered part of the problem but under stressful condition, oracle starts to act weird and returns NULL output values. So what we did was to include rollbacks into our system so as to recover from traffic but to our dismay it still didn't worked out.

Here is the design of the system:
Every time a transaction comes in, it checks the card information from the mother database(masterfile), gets the card's status, balance and substracts it with the total transaction amount (which can be queried on a separate table - lets name this as table B) the card incurred throughout the day to get the latest balance of the card (since the masterfile can only be updated at night to avoid data integrity problems)

My problem right now is focused on the second table (table B) mentioned above. Some transactions like sale transaction updates this table as other transactions like void and reversal transactions concurrently thus creating heavy traffic (or deadlocks).

We've used PL/SQL commands like LOCK TABLE <table> FOR ROW EXCLUSIVE... sort of commands but still, traffic is heavy.

Can anyone help me out on this problem? Thanks