Handling Elixir.Postgrex.Error - "could not serialize access due to read/write dependencies among transactions"

@maxximiliann you can catch the exception and try again, although you’ll likely want to place a limit on how many times you do this.

Can you elaborate a bit on what this code is supposed to do? Why does it try to do the delete again if no records were deleted?

That was my poor attempt at handling the exception, he h hehe he

New attempt:

def delete_single_record(table_name, key, identifier) do
	Repo.transaction(fn ->
	  selected_record =
	    from(record in table_name,
	      where: field(record, ^key) == ^identifier
	    )

	  try do
	    Repo.delete_all(selected_record)
	  catch
	    Postgrex.Error -> Repo.delete_all(selected_record)
	  end
	end)
end

Result:

** (Mix) Could not start application MyApp: MyApp.Application.start(:normal, []) returned an error: shutdown: failed to start child: Supervisor.Main
    ** (EXIT) shutdown: failed to start child: MyGenServer
        ** (EXIT) an exception was raised:
            ** (Postgrex.Error) ERROR 40001 (serialization_failure) could not serialize access due to read/write dependencies among transactions

    hint: The transaction might succeed if retried.

Reason code: Canceled on identification as a pivot, during commit attempt.
                (db_connection 2.4.0) lib/db_connection.ex:1547: DBConnection.run_transaction/4
                (elixir 1.12.2) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
                (elixir 1.12.2) lib/task/supervised.ex:35: Task.Supervised.reply/5
                (stdlib 3.15.1) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

What am I missing?

I believe you need to wrap the whole Repo.transaction in a try, since the exception is occurring when the transaction commits.

I am sort of surprised you are getting this error though, since this transaction isn’t (as far as I can see) using the serializable isolation level, and it’s also just a single query. Are you eliding any parts of this function?

I ran ALTER DATABASE db_name SET DEFAULT_TRANSACTION_ISOLATION TO SERIALIZABLE; from Postgres to serialize all transactions. I wasn’t aware this could also be done straight from Elixir. Where can I read up on this?

Thanks for all your help!

Ah. You can opt into serialized transactions on a transaction by transaction basis by doing:

Repo.transaction(fn ->
  Repo.query!("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
  # other stuff here
end)

For serializing all transactions setting the default as you’ve done is probably easiest.

Back to your original issue, calling delete_all twice in the same transaction isn’t going to help, you need to retry the whole transaction. Merely attempting it one more time may not be enough either, it may take several tries. I would reconsider whether you want this default.

Gotcha. This did the trick:

  def delete_single_record(table_name, key, identifier) do
    try do
      Repo.transaction(fn ->
        selected_record =
          from(record in table_name,
            where: field(record, ^key) == ^identifier
          )

        Repo.delete_all(selected_record)
      end)
    catch
      Postgrex.Error -> delete_single_record(table_name, key, identifier)
    end
  end

Thanks again!