Are Ecto Transactions concurrency safe

If wrap an Repo.get, and a Repo.insert in a transaction, can another process run a Repo command in the middle of my transaction? For example, I want to add a sequential sequence number to rows in a table. Can I put the get and in insert into a transaction without hitting the unique constraint of the sequence column?

3 Likes

Ecto transactions ultimately rely on the transaction modes of the underlying database. For Postgres that encompasses a broad array of possibilities: https://www.postgresql.org/docs/current/static/transaction-iso.html. Additionally there’s row level locks that can occur via SELECT FOR UPDATE.

Using the appropriate type of transaction will depend a lot on what you’re trying to achieve.

3 Likes

If you want to ensure unique sequential numbers, you should really be doing that at the database level, where it can actually be guaranteed, whether with a Postgres sequence or a MySQL autoincrement field.

I hit a related problem where I wanted to do a multi-stage, multi-process data import in a transaction, here’s a discussion and the solution I came up with: Using Ecto to run a long-running multi-process transaction

The TL;DR is that in Ecto, each process, when calling Repo commands, takes a connection from the pool and so any Repo calls within that process are serialised, including transactional ones. Other processes don’t know some other connection has a transaction open. As @benwilson512 said, the resultant semantics are dictated by the database itself.

According to this SO post (didn’t do more research), a Postgres sequence will simply increment each time it needs to, even if its value is not used (e.g. a transaction is rolled back). This can cause “gaps” in the numbering in that case but seems like the right solution.

If that’s unsatisfactory, perhaps some more information on your usecase would be good.

1 Like

Ah yeah somehow I missed the incrementing number thing. Using an autoincrementing column would definitely be best. If you wanted to roll it yourself by getting the last row, adding 1, and then writing a new row you can totally do that, but you’d need to specify the Serializable isolation level I believe.

I believe this question arose because of this topic on using Ecto.Multi vs prepare_changes for an incrementing order column.

Important to note is in that case, the order should not be incremented for each new column, but rather, the order only makes sense for siblings within a belongs_to relationship (i.e. the order of photos in album #3, or the order of songs in playlist #500, etc.).

Got it. Depends on how the database handles transactions. Thanks for the answers.

You need a higher transaction isolation level than the standard one, for most RDBMSs. It is often called ‘repeatable read’ and potentially locks the whole table for update when only reading. This has consequences for throughput, for obvious reasons.

Here is a more thorough discussion of this topic for Postgres and Ecto: https://keathley.io/elixir/ecto/2018/04/23/setting-isolation-levels-in-ecto-transactions.html