Yes! Either by pessimistic locking (which you can do by wrapping you code in a Repo.transaction, assuming that you use Postgres or another database that supports them), which locks the whole table,
or by optimistic locking, which requires an extra field in your DB table, but is much faster in cases where conflicting writes are uncommon, since it only ‘locks’ one record in the table.

There also is Ecto.Query.lock but it is more low-level and DB-specific.

Do transaction in postgres actually lock the whole tables? I think it might depend on isolation levels, which is read committed, by default (I think), which I don’t think locks the tables but uses mvcc.

Depending on the problem, you can also use upserts. If you do insert(data, on_conflict: :nothing, returning: true), it will do nothing if the data exists and return all fields from the database.

We also talk about it on the What’s New in Ecto 2.0 ebook (95% of it still applies to Ecto 3). http://pages.plataformatec.com.br/ebook-whats-new-in-ecto-2-0

No. Doing an insert does not prevent other transactions doing inserts. You have to use an explicit lock command (in Postgres) to lock the whole table. Doing an update will block other updates on those rows, and may block reads depending on the isolation level of the reader (but not by default in Ecto).

You are correct; it depends on the isolation level, which, as long as it isn’t SERIALIZE will not lock the whole table.

This is not correct. Isolation level will not ever cause a table to be locked by an insert nor other DML command. Locks and isolation level are separate concepts. According to the Postgres documentation, these are the only commands that lock a full table: ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM.

DML commands like insert/update obtain ROW EXCLUSIVE locks.

Isolation level is concerned with isolation between transactions, and preventing dirty reads/phantom reads. SERIALIZABLE simply means the transactions must have the same semantics when running in parallel, that they would have running one after the other. Yes this requires read locks, and not reading new rows from another transaction but that does not lock the whole table against inserts.

@jeremyjh Thank you for clarifying !

To make sure I understand this correctly (because TFM seems ambiguous on this - actually more than the above quote):

When there’s conflict (like a UNIQUE constraint being violated), does the insert/2 function return the conflicting record from the database, discarding the struct or changeset passed to it? What if multiple records conflicted with the passed data? Like when violating constraints on several fields?

Update:

Did some tests (using Postgres), and it seems that given a changeset, inserting which would violate unique constraint, Repo.insert(changeset, returning: true, on_conflict: :nothing) returns the data passed to it rather than the record from the database. Is this the expected behaviour? If yes then what’d be the way to obtain the existing record without performing a separate query for this purpose?

As clarified in a separate thread this is not actually true. If the function only translates syntax between Elixir and SQL then only the equivalent SQL behaviour remains valid. PostgreSQL documentation says:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used)

This is consistent with results of my quick tests above: on_conflict: :nothing prevents returning fields from the database as no rows are “actually inserted (or updated)”. Shall see if I can improve this section of the docs

Still not without race condition, but I really needed a basic find_or_create functionality found in Rails and I liked this neat trick initially shared on SO by defining such function in the MyApp.Repo module in lib/my_app/repo.ex:

def get_or_insert(schema, params) do
  case get_by(schema, params) do
    nil -> insert(schema.changeset(schema.__struct__, params))
    struct -> {:ok, struct}
  end
end

This allows you to do MyApp.Repo.get_or_insert(MyItem, %{var1: "some value"}) for any model in your app then.

Think lots of people searching for this functionality will also land here, so re-sharing.

Upset doesn’t cut it for my use case, as I often need to search against multiple columns including jsonb, and storing huge unique indexes on these columns doesn’t make sense.