Lock Not Available - could not obtain lock on row in relation

Phoenix Forum Questions / Help
, ,
1

I am frequently getting the below error in the production.

(Postgrex.Error) ERROR 55P03 (lock_not_available) could not obtain lock on row in relation "snapshots"
    (ecto_sql) lib/ecto/adapters/sql.ex:618: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:551: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:153: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (ecto) lib/ecto/repo/queryable.ex:67: Ecto.Repo.Queryable.one/3
    (afterglow) web/jobs/snapshots_tasks.ex:13: anonymous fn/1 in AfterGlow.SnapshotsTasks.save/1
    (ecto_sql) lib/ecto/adapters/sql.ex:887: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4

I am not sure how to resolve this.

Can you please help?

Thanks.

2

This is an error message being returned from Postgrex, after it was returned by the database. This error can happen when you do a SELECT ... FOR UPDATE or similar that interacts with the DB’s locking systems.

You’ll need to provide more information about what your application is doing when this error occurs (and/or source code) to get specific help.

1 Like
3

Hi @al2o3cr

We have jobs running in the background that queries the data create a snapshot and send it via email.

Below is the code for the same

def save(snapshot) do
    Repo.transaction(fn ->
      try do
        snapshot =
          from(s in Snapshot, where: s.id == ^snapshot.id, lock: "FOR UPDATE NOWAIT")
          |> Repo.one()

        try do
          unless snapshot.status == "pending" do
            update_status(snapshot, "in_process")

            snapshot =
              cond do
                snapshot.should_save_data_to_db and snapshot.should_create_csv ->
                  Snapshots.save_data(snapshot)
                  |> Snapshots.create_and_send_csv(nil)

                  snapshot

                snapshot.should_save_data_to_db ->
                  Snapshots.save_data(snapshot)
                  snapshot

                snapshot.should_create_csv ->
                  Snapshots.create_and_send_csv_from_remote_db(snapshot)
                  snapshot
              end

            update_status(snapshot, "success")

            if snapshot.scheduled do
              create_new_snapshot(snapshot)
            end
          end
        catch
          _ ->
            update_status(snapshot, "failed")

            if snapshot.scheduled do
              create_new_snapshot(snapshot)
            end
        end
      catch
        _ ->
          nil
      end
    end)
  end

  def run do
    from(s in Snapshot)
    |> where(
      [s],
      s.status in ["pending"] and s.starting_at <= ^DateTime.utc_now()
    )
    |> Repo.all()
    |> Enum.each(fn s ->
      Async.perform(&__MODULE__.save/1, [s])
    end)
  end

  def cancel_all_in_process_snapshots do
    from(s in Snapshot)
    |> where(
      [s],
      s.status == "in_process" and s.starting_at <= ^DateTime.utc_now()
    )
    |> Repo.all()
    |> Enum.map(fn s -> update_status(s, "pending") end)
  end

  defp change_attributes(snapshot) do
    parent =
      if snapshot.parent do
        snapshot.parent
      else
        snapshot
      end

    children_count =
      Repo.one(from(s in Snapshot, where: s.parent_id == ^parent.id, select: count("*")))

    name = "#{parent.name}-#{children_count |> Kernel.+(1)}"

    %{
      snapshot
      | parent_id: parent.id,
        name: name,
        status: "pending",
        snapshot_data: [],
        children: [],
        starting_at:
          snapshot.starting_at
          |> convert_ecto_datetime_to_epoc
          |> Kernel.+(snapshot.interval)
          |> DateTime.from_unix!(:second)
    }
  end

  defp create_new_snapshot(snapshot) do
    # clone
    {:ok, snapshot} =
      snapshot
      |> Repo.preload(:question)
      |> Repo.preload(:parent)
      |> Repo.preload(:children)
      |> Repo.preload(:snapshot_data)
      |> change_attributes
      |> Map.put(:id, nil)
      |> Repo.insert_with_cache()

    # schedule
    # snapshot
    # |> schedule(
    #   snapshot.starting_at
    #   |> convert_ecto_datetime_to_epoc
    #   |> Kernel.-(
    #     DateTime.utc_now()
    #     |> DateTime.to_unix(:seconds)
    #   )
    #   |> Kernel.*(1000)
    # )
  end

  defp update_status(snapshot, status) do
    snapshot = Ecto.Changeset.change(snapshot, status: status)
    Repo.update!(snapshot)
  end

  defp convert_ecto_datetime_to_epoc(datetime) do
    datetime
    |> NaiveDateTime.to_erl()
    |> :calendar.datetime_to_gregorian_seconds()
    |> Kernel.-(62_167_219_200)
  end
end
4

You’re getting the 55P03 because you are requesting it - the NOWAIT tells Postgres to return an error if it can’t immediately acquire the lock.

This is not going to handle a raised exception; catch with a single value on the left of the -> will only capture values emitted with throw. You’d need the two value shape instead.

BUT what you likely want instead is rescue, with a specific exception:

try do
  # ... some SQL bits ...
rescue
  Postgrex.Error ->
    # error handling
end

Other general notes:

  • children_count |> Kernel.+(1) pipes are not magic, this is just children_count + 1 with extra steps
  • the code around convert_ecto_datetime_to_epoc looks like it could be shortened to DateTime.add(snapshot.started_at, snapshot.interval, :second)
5 Likes