-1

I have 3 tables, A,B and 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. 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 A, B and C.

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;
  • What does actOnABC return and why can you put it into a where clause? – Evan Carroll Mar 18 '17 at 0:29
0

You should be able to put it into a INSERT INTO .. SELECT

INSERT INTO b (a,b,c)
  SELECT a,b,c
  FROM ( VALUES
    (...)
  ) AS t(a,b,c)
  WHERE actOnABC(i) = 0;
  • I have tried this unsuccessfully. Let me update my original post to give more information about the stored procedure actOnABC() – cplusplusrat Mar 18 '17 at 0:31
  • Let me try out your edited version.. – cplusplusrat Mar 18 '17 at 0:35
  • Unfortunately this does not appear to work either. Note that there could be independent transactions modifying tables A,B and C. So its not just this insert statement which happens concurrently, but there are other transactions which could be modifying tables A, B and C concurrently. – cplusplusrat Mar 18 '17 at 0:52
0

Please see PostgreSQL Documentation, Chapter 13. Concurrency Control, 13.2. Transaction Isolation for both Repeatable Read and Serializable isolation level descriptions:

Applications using this level must be prepared to retry transactions due to serialization failures.

It's impossible to do within a Postgres function - the client application needs to do this retrying. And in Serializable isolation level this retrying is required even for read-only transactions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.