7

I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation and create the records in the database.

I found a bug today when two http requests had been made within the same second which caused this function to be called with identical data twice. There is a clause inside the function which makes a select on a table to see if a value exists, if it does exist then I take the ID and use that on my next operation, if it doesn't then I insert the data, get back the ID and then use that on the next operation. Below is a simple example.

select id into articleId from articles where title = 'my new blog';
if articleId is null then
    insert into articles (title, content) values (_title, _content)
    returning id into articleId;
end if;
-- Continue, using articleId to represent the article for next operations...

As you can probably guess, I got a phantom read on the data where both transactions entered the if articleId is null then block and tried to insert onto the table. One succeeded and the other blew up because of a unique constraint on a field.

I've had a look around at how to defend against this and found a few different options but none seem like they fit our needs for a few reasons and I'm struggling to find any alternatives.

  1. insert ... on conflict do nothing/update... I first looked at the on conflict option which looked good however the only option is to do nothing which then doesn't return the ID of the record that caused the collision, and do update won't work as it will cause triggers to be fired off when in reality the data hasn't changed. In some instances this isn't a problem but in many cases this might invalidate sessions user sessions which isn't something we can do.
  2. set transaction isolation level serializable; this seems like the most attractive answer, however even our test suite can cause read/write dependencies where, like the above, we want to insert if something doesn't exist and return it if it does and carry on with further operations. If we have several transactions pending that run the above code it will cause a read/write dependency error as outlined in the transaction-iso of the Postgres docs.

How should is this sort of concurrent read/write transaction handled?

Neither myself or my team claim to be database experts, let alone Postgres experts but feel like this must be a solved problem, or one people have come across in the past. We're open to any suggestions. If the information provided above is not enough, please comment and I'll add more information as needed.

  • perhaps update only columns that have no update triggers watching them (and don't change the value in those columns), or put if new is not distinct from old then return new; end if; at the top of all your update triggers. – Jasen Jul 31 '18 at 20:50
5

Try the insert first, with on conflict ... do nothing and returning id. If the value already exists, you will get no result from this statement, so you have then to execute a select to get the ID.

If two transactions try to do this at the same time, one of them will block on the insert (because the database does not yet know if the other transaction will commit or rollback), and continue only after the other transaction has finished.

  • Thanks for this, a simple solution but one we overlooked as it would potentially end up being done in many places. That said, it doesn't mean it's bad, just means we've got some work to do! – Elliot Blackburn Jul 20 '18 at 7:27
  • While this is simple and should work in most cases, it can still find no row (though the row is there) or find a row in the SELECT, which a concurrent transaction already deleted (yet uncommitted). Under heavy concurrent load you might have to do more (loop), like the other answers illustrate. – Erwin Brandstetter Sep 21 '18 at 12:59
  • @ErwinBrandstetter I tested this. Please provide a sequence of commands that shows the problem. – CL. Sep 21 '18 at 13:39
  • I discussed possible issues in detail here: stackoverflow.com/a/42217872/939860 – Erwin Brandstetter Sep 21 '18 at 13:46
  • @ErwinBrandstetter In my test, the sentence "The SELECT sees the same snapshot from the start of the query and also cannot return the yet invisible row." appears to be wrong. [In two connections: C1: BEGIN; C2: BEGIN; C1: INSERT; C2: tries same INSERT, blocks; C1: COMMIT; C2: unblocks; C2: SELECT sees the new row.] Again, please provide an example that proves the existence of the problem. – CL. Sep 21 '18 at 14:19
2

I think the best solution is to just do the insert, and catch the error and handle it properly. If you are prepared to handle errors, serializable isolation level is (apparently) unnecessary for your case. If you are not prepared to handle errors, serializable isolation level won't help--it will just create even more errors you aren't prepared to handle.

Another option would be to do the ON CONFLICT DO NOTHING and then if nothing happens, follow up by doing the query you are already doing to get the must-be-there-now value. In other words, move select id into articleId from articles where title = 'my new blog'; from a pre-emptive step to a step only executed if ON CONFLICT DO NOTHING does in fact do nothing. If it is possible for a record to be inserted and then deleted again, then you should do this in a retry loop.

  • Thanks for this, I'm going to take a look at the on conflict do nothing initially and read around more on proper postgres error catching and see which ones make the code most readable. I imagine we'll use some combination of both throughout our code now. – Elliot Blackburn Jul 20 '18 at 7:28
2

The root of the problem is that, with default READ COMMITTED isolation level, each concurrent UPSERT (or any query, for that matter) can only see rows that were visible at the start of the query. The manual:

When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions.

But a UNIQUE index is absolute and still has to consider concurrently entered rows - even yet invisible rows. So you can get an exception for a unique violation, but you still cannot see the conflicting row within the same query. The manual:

INSERT with an ON CONFLICT DO NOTHING clause may have insertion not proceed for a row due to the outcome of another transaction whose effects are not visible to the INSERT snapshot. Again, this is only the case in Read Committed mode.

The brute-force "solution" to this problem is to overwrite conflicting rows with ON CONFLICT ... DO UPDATE. The new row version is then visible within the same query. But there are several side effects and I would advice against it. One of them is that UPDATE triggers get fired - the thing you want to avoid expressly. Closely related answer on SO:

The remaining option is to start a new command (in the same transaction), which then can see these conflicting rows from the previous query. Both existing answers suggest as much. The manual again:

However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

But you want more:

-- Continue, using articleId to represent the article for next operations...

If concurrent write operations might be able to change or delete the row, to be absolutely sure, you also have to lock the selected row. (The inserted row is locked anyway.)

And since you seem to have very competitive transactions, to make sure you succeed, loop until success. Wrapped into a plpgsql function:

CREATE OR REPLACE FUNCTION f_articleid(_title text, _content text, OUT _articleid int) AS
$func$
BEGIN
   LOOP
      SELECT articleid
      FROM   articles
      WHERE  title = _title
      FOR    UPDATE          -- or maybe a weaker lock 
      INTO   _articleid;

      EXIT WHEN FOUND;

      INSERT INTO articles AS a (title, content)
      VALUES (_title, _content)
      ON     CONFLICT (title) DO NOTHING  -- (new?) _content is discarded
      RETURNING a.articleid
      INTO   _articleid;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Detailed explanation:

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.