Are transactions batched?

Questions / Help
, ,
1

Hello,

I realize this is a DB question, but it involves Ecto and Postgrex so I thought I’d post here.

Suppose I have a multi with many inserts/updates using both Multi.run and Multi.insert/update. I commit the changes to this multi using Repo.transaction() (on a PostgreSQL DB using the Postgrex driver.)

I have a couple of questions. First, are the insert/updates batched? As in, if I have three inserts and two updates, will there be one round trip to the remote DB server or five round trips? Second, if indeed these statements are batched, how is it possible to get the updated DB state inside the multi before the multi is committed?

Thanks!

2

5, they are not batched.

2 Likes
3

Gotcha. Then I wonder what are the best practices for doing atomic updates in general if one want to minimize round trips?

4
  • You can use Multi.run with your function passed as a parameter and you get the state so far in its parameters (Ecto calls it during the transaction, at the time of your choosing. Check the docs.).
  • Not sure your original problem has any other answers except for using insert_all and update_all.
2 Likes
5

It may be possible to send the data to the DB to temporary “staging” table(s) using insert_all or copy from csv or stdin then issuing inserts/updates/upserts atomically in a transaction once it is uploaded. Depends on what you are doing but this could be faster than creating a massive list of operations inside a Multi.