Deadlock Question

[lesstjm]

In my database I have a transaction table and a balance table and whenever a transaction comes in for an account I have to sum all of the customer's transactions to update their balance. I am having a deadlock problem. Transactions are coming in so fast that while I am summing up the transaction amounts to figure out the account balance I am getting deadlock errors because other transactions are lining up. Is there any way I could check to see if the transaction records for that account are locked and then make my program wait a specified amount of time? Or is there a way I could increase the time Oracle will wait before it blows up with a deadlock error?

[pando]

shouldnt you do that asyncronously? for example instead of updating every transaction update transactions every 1 ~ 5 minutes

just curious since I have never dealed with banking applications

[lesstjm]

I mentioned that to my boss but they don't like that idea. The customers have to see the changes as they happen. I guess my only solution would be if I could check the account for a lock and if there is one make my program wait. Does anyone kno how to do that?

[raghud]

try this sql if you know the program you are expecting the lock or userid or sql try customize this sql and run it .. if you are getting the rows ...you can put your sql on wait .. If you got 0 rows means there are no locks ...

select a.username, a.program, a.sid, a.serial#
from v$session a, dba_blockers b
where a.sid = b.holding_session;

[chrisrlong]

Another thought is to just adjust the customer's total with the transaction in-hand rather than re-calculating the total after every transaction.

- Chris