PostgreSQL – Can Sequential Queries Result in Concurrent Transactions?

concurrencyjdbcpostgresqlpostgresql-9.4transaction

I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed the result set of the former one is fetched. Below a simplified sketch:

while(true) {
    PreparedStatement stmt = //...
    ResultSet rs = stmt.executeQuery();
    //process result
}

The query that is causing trouble invokes the following stored procedure:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id BIGINT
                                            , _source_client_id BIGINT)
 RETURNS TEXT AS
$$
   DECLARE rv TEXT;
   BEGIN
     DELETE FROM message m
     USING (
        SELECT * FROM message
        WHERE message.queue_id = _queue_id
        AND (message.receiver_client_id = _source_client_id OR
             message.receiver_client_id = -1)
        ORDER BY insertion_time ASC LIMIT 1) AS tmp
     WHERE m.id = tmp.id
     RETURNING m.content INTO rv;

     IF (SELECT rv IS NULL) THEN
       RAISE 'No message available.';
     END IF;
     RETURN rv;
   END;
$$ LANGUAGE plpgsql;

I am observing behavior as it is described here http://www.postgresql.org/docs/9.1/static/transaction-iso.html in the section 13.2.1. Read Committed Isolation Level.

What happens is that the exception in the procedure is triggered although when I abort everything when the exception happens and invoke the procedure manually the exception is not raised. I noticed further that if a transaction is already holding a row exclusive lock on the message table that then the exception is raised.

Also if I add 

     LOCK TABLE message IN ROW EXCLUSIVE MODE;

at the beginning of the procedure the problem disappears.

I thus suspect that in the above loop that a query is returning a result before its transaction is actually committed such that the next transaction is conflicting with it. Can that happen? N.B. I did not find something that explains this in the PostgreSQL documentation. Also I don't have Async commit enabled.

Best Answer

To answer your question in the title: No.

Consecutive queries in the same session (same transaction or not) are never concurrent. And there can only be one transaction at a time in the same session. Concurrency issues are impossible that way, those only happen between transactions in multiple sessions.

One possible reason for your error message might be a glitch in your function, which can be simplified:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id bigint
                                            , _source_client_id bigint)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   DELETE FROM message m
   USING (
      SELECT id
      FROM   message
      WHERE  queue_id = _queue_id
      AND    receiver_client_id IN (_source_client_id, -1)
      ORDER  BY insertion_time
      LIMIT  1
      ) m1
   WHERE  m.id = m1.id
   RETURNING m.content;

   IF NOT FOUND THEN
      RAISE 'No message available.';
   END IF;
 END
$func$ LANGUAGE plpgsql;

Your original check:

IF (SELECT rv IS NULL) THEN

could be simplified to:

IF rv IS NULL THEN

But that's still no good. It fires when message.content is NULL, not only when no row has been found. The table definition is missing in the question, so we don't know if that's possible.

Either way, the special variable FOUND is the better tool for the purpose. My audited version is also simpler and faster. Note that plpgsql functions do not return results until the end of the function or RETURN is reached. We can just return rows and still cancel the result later if need be. There is an example in the manual, which is a spin-off from this related question on SO:

Related Question