I have 3 tables,
C. A function
actOnABC(i) which is a read only operation on the tables:
IF actOnABC(i) = 0 THEN RAISE EXCEPTION 'Message'; ELSE INSERT INTO TABLE B VALUES(...); END IF;
I am running this with Spring JDBC, Transaction Isolation level set to SERIALIZABLE.
As the load/number of concurrent transactions increase, I get the Exception with message:
ERROR: could not serialize access due to read/write dependencies among transactions.
Lets say, I have two concurrent Transactions
T1 and T2 both are attempting to insert. If say,
T1 is successful in making the insert first into table
B, then potentially the result of
actOnABC(i) which was already evaluated by
T2 could change. Hence
T2 will throw an exception.
Have I understood the situation correct? Also, is there any way I can combine the
actOnABC(i) call with the
INSERT statement so as to avoid this race condition? I did think of using transaction level locks. But I am afraid that won't work as I have other stored procedures actively updating the Tables
Is there a smarter way to deal with the situation?
EDIT: Some more information on the stored procedure
actOnABC(). This procedure returns an integer which is calculated as below.
SELECT count(*) into tmp_a from (... access tables..); SELECT count(*) into tmp_b from (... access tables...); return tmp_a - tmp_b;