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
