Maxximiliann

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

Function:

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

  {num_of_deleted_records, _} = Repo.delete_all(selected_record)

  case num_of_deleted_records do
    0 -> Repo.delete_all(selected_record)
    _ -> {:ok, num_of_deleted_records}
  end
end)
end

Exception:
2021-08-27T13:10:16.977283+00:00 error: ** Task 'Elixir.Foo' terminating, ** Started from <0.2203.1>, ** When function == fun erlang:apply/2, ** arguments == [#Fun<Elixir.SomeLambda.0.42097265>,[#{error => some_message}]], ** Reason for termination == , ** {#{'__exception__' => true,'__struct__' => 'Elixir.Postgrex.Error',connection_id => nil,message => nil,postgres => #{code => serialization_failure,detail => <<"Reason code: Canceled on identification as a pivot, during commit attempt.">>,file => <<"predicate.c">>,hint => <<"The transaction might succeed if retried.">>,line => <<"4853">>,message => <<"could not serialize access due to read/write dependencies among transactions">>,pg_code => <<"40001">>,routine => <<"PreCommit_CheckForSerializationFailure">>,severity => <<"ERROR">>,unknown => <<"ERROR">>},query => nil},[{'Elixir.DBConnection',run_transaction,4,[{file,"lib/db_connection.ex"},{line,1547}]},{'Elixir.Task.Supervised',invoke_mfa,2,[{file,"lib/task/supervised.ex"},{line,90}]},{'Elixir.Task.Supervised',reply,5,[{file,"lib/task/supervised.ex"},{line,35}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,226}]}]}

How can this exception be handled in such a way that the deletion is simply retried rather than the system crashing?

6 comments

#troubleshooting #error-handling

4 2669 6

2021-08-27 15:20:15 UTC

Marked As Solved

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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?

Post #5

Also Liked

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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.

Post #7

Maxximiliann

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!