Idiomatic way of checking if a row is locked using Ecto?

I’m trying to handle race conditions, and am locking the row in question to prevent issues.

What I’m not sure about is how to check for this… is it just a case of trying to update the row in question and handling the SQL error directly? Or is there a better way of checking if the record is locked before trying to update it?

Thanks in advance

Without knowing your exact scenario, here are some ideas from a Postgresql point of view:

  1. If you need to obtain the lock for that exact row and can’t skip it, you either let the query timeout or use the NOWAIT option to return an error immediately
  2. If you can skip the row and try other ones, use the SKIP LOCKED option

More info on SKIP LOCKED/NOWAIT:

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT , the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED , any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.

2 Likes

Could you use optimistic locking? I haven’t used it with Ecto but I think this it Ecto.Changeset.optimistic_lock — Ecto v3.6.2
If version number doesn’t match Ecto.StaleEntryError is raised and you need to retry the operation.