I have a procedure where a create loans for clients. Clients can get only 10000 max. I first read the amount requested by the client on old loans, if the client has requested more than 10000 I raise an error else I insert a new loan.
create or replace NONEDITIONABLE PROCEDURE CREATE_LOAN
(
p_client_id INT,
p_requested_loan_amount DECIMAL
)
IS
v_available_amount DECIMAL DEFAULT 0;
BEGIN
--Get available amount for the client( Clients cant require more thant 10000)
SELECT 10000 - COALESCE(SUM(l.amount), 0) INTO v_available_amount
FROM loans l
WHERE l.client_id = p_client_id
AND (l.state_id = 1 OR l.state_id = 2);
--If the cliente requested more than 10000 in older loans raise error
IF p_requested_loan_amount > v_available_amount
THEN
raise_application_error( -20001, 'Not enough available amount.' );
END IF;
--Else insert new loan
INSERT INTO loans (amount, state_id, client_id)
VALUES(p_requested_loan_amount, 1, p_client_id);
END;
How can I prevent two concurrent transaction from reading the old loans at the same time and both beliving that they have available amount before they insert. If I were using Sql Server I could increase the isolation level and that would prevent the problem, but it does not work the same way on Oracle.
