Transactions- what are the basics?

Questions / Help
,
1

I have a situation similar to create and insert 2 associated models.

In my case User has_many Others and Others belongs_to User.
I want to insert a user and an Other in one transaction.

I’m doing a put_assoc from User and it works fine. It generates 2 insert statements of course.

My question is: Do I need to wrap the insert in a Repo.transaction? I have been unable to find any documentation that talks about how the inserts are handled. If I did not wrap the insert function in a transaction, would the 2 sql statements be batched in a single transaction by default or would they be done as 2 separate transactions?

  MyModule.Repo.transaction fn ->
    MyModule.Repo.insert(changeset)
  end
1 Like
2

You probably want an Ecto.Multi rather than using transactions directly as you get a bunch of extra stuff that you don’t get with Repo.transaction

4 Likes
3

Well, since I don’t know what I get with transactions, I’d really like to understand more about them first, so I can understand what advantages I’m getting with Ecto.Multi.

2 Likes
4

In its most simple form you can think of a transaction as a ‘serialized set of operations’ on the database.

I.E. All the operations in your transaction will either all complete, or all fail, and nothing will interfere or run between them that can change your state unexpectedly.

In reality it is far more complex with the database allowing multiple operations on non-conflicting rows and all such, but overall you can think of it that way. :slight_smile:

1 Like
5

A transaction is a sequence of operations performed as a single unit of work. Or more simply, if a statement in the transaction fails the previous statements will get automatically rolled back (in Ecto), and no further operations are carried out. If you’re using transactions manually you can perform special behaviours on rollback, if simply rolling back isn’t enough.

Ecto.Multi works hand in hand with Repo.transaction and provides a bunch of useful stuff on top of transactions. i.e. easy inspection and returns, that using Repo.transaction directly you’d probably end up implementing anyway.

2 Likes
6

I think I’ve done a bad job of asking my question. I understand what a db transaction is.

What I don’t know is when Ecto applies them automatically and when I need to explicitly declare them.

If I do a single line Repo.insert(changeset) and that change set generates multiple SQL insert statements, will Ecto automatically wrap all those SQL statements in a single db transaction?

7

Yes, you can observe this with debug logging:

iex(5)> %Menu.Item{name: "Bar", price: 4.50, category: %Menu.Category{name: "Foo"}} |> Repo.insert!
[debug] QUERY OK db=0.3ms
begin []
[debug] QUERY OK db=6.6ms
INSERT INTO "categories" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Foo", {{2017, 11, 3}, {20, 21, 34, 814485}}, {{2017, 11, 3}, {20, 21, 34, 817077}}]
[debug] QUERY OK db=5.0ms
INSERT INTO "items" ("category_id","name","price","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [4, "Bar", #Decimal<4.5>, {{2017, 11, 3}, {20, 21, 34, 834005}}, {{2017, 11, 3}, {20, 21, 34, 834009}}]
[debug] QUERY OK db=0.8ms
commit []

Note the begin and commit lines. Those are the beginning and end of the transaction respectively.

5 Likes
8

Ah, yes! Thanks! I totally missed that.

9

If anyone coming to this topic needs more tips on Ecto.Multi and transactions in general, we’ve written a post about it: How database transactions work in Ecto and why Elixir makes it awesome? :wink:

5 Likes