SQL & PL/SQL

Dear All,

I have a stored procedure for a banking application which does the following;


(a) balance check of the customer
(b) if balance verification is ok then call another stores procedure to update customer balance and insert the transaction
(c) for step (b) , i have savepoint and rollback on any issues in the insert transaction part.

Here issue is , this stored procedure is called by multiple sessions , and in both the sessions step (a) passed and hangs at step (b).

Here both the balance check will be approved in both the cases ;

Consider a scenario here.A customer having balance of USD 100 in his account.Customer is doing 2 transactions simultaneously and in trans1 he uses 100 usd and in transaction 2 also 100 usd gets approved even if the balance in not available.

Hope my explanation is clear here.

Do you have any solution for such scenario.

Thank you