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

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.

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

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

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)
4 Likes