Whenever there’s a constraint, you’re presented with two options:

1. Try to break it and recover if it fails
2. Verify it and be sensible to race conditions (or use locks to avoid these)

Most often, programmers chose option 1, because it is usually easier to manage. But it causes a common problem in larger transactions: as soon as a constraint is broken, the transaction is aborted. Very often, this is not the intention of the programmer. Take the following pseudocode example:

transaction(fn ->
  a_result = perform_a()

  # doing operation that may break a constraint
  case perform_b(a_result) do
    # Success, no action
    {:ok, _} -> :ok

    # Constraint broken, perform something
    {:error, _} ->
      perform_c(a_result)
  end
end)

In the above code, within a single transaction we:

1. Perform operations, some of which may break a constraint
2. Depending on whether the constraint was broken or not, we do something else

This code doesn’t work because of the problem mentioned above: the transaction is aborted from the very moment that the constraint is broken. Everything will be rolled back and no more operations are allowed.

We can solve this easily using savepoints. Savepoints allow you to create special marks in the transaction. You can then rollback to these marks, reverting all commands done after it and restoring the transaction state. As simple as:

transaction(fn ->
  a_result = perform_a()
  savepoint("before_b")

  # doing operation that may break a constraint
  case perform_b(a_result) do
    # Success, no action
    {:ok, _} -> :ok

    # Constraint broken, rollback to before the constraint was broken
    # and then perform our action
    {:error, _} ->
      rollback_to_savepoint("before_b")
      perform_c(a_result)
  end
end)

You can read more about savepoints in Postgres documentation, and in MariaDB docs. If you intend to use savepoints from Elixir with Ecto, you can try my library Ecto Savepoint