Ecto transaction and the operations are not atomic.

[vasumur]

Hi @vstavskyi

Appreciate your help with this. I have to do a bunch of SQL activities together and only if all of the DML activities success it should commit, otherwise the whole transaction should rollback.

      Repo.transaction(fn ->
        add_effort(activity)
        add_account_effort(activity)
        add_rel(activity)
      end)

While doing the above, when there are errors with the add_rel function the other two DML statements that is add_effort and add_account_efforts are committed.

Sholuld there be any specific settings to enable this transaction (like autocommit explicitly set to false etc)?

[vstavskyi]

With [autocommit: 0] it seems it works.

  DBConnection.transaction(conn, fn conn ->
    query = %Jamdb.Oracle.Query{statement: "insert into peop (id)  values (:1)"}
    DBConnection.execute!(conn, query, [1])  |> IO.inspect
    DBConnection.execute!(conn, query, [1])  |> IO.inspect
    end)
  end

** (DBConnection.ConnectionError) 'ORA-00001: unique constraint (JAM.PEOP_PKEY) violated\n' (db_connection 2.4.2) lib/db_connection.ex:674: DBConnection.execute!/4

[vasumur]

@vstavskyi

Assuming autocommit is 1 by default that means every DML activity (insert/delete/update) automatically commits.

If I turn the autocommit as 0, then I should run these DMLs only within a transaction. Otherwise the insert/delete/updates are not committed?

Is there a way to run commit statement using the REPO.

[vstavskyi]

  sql = "insert into peop (id)  values (:1)"

  YourApp.Repo.transaction(fn ->

    case YourApp.Repo.query(sql,[1]) do
      {:ok, result} -> {:ok, result}
      {:error, err} -> YourApp.Repo.rollback(err)
    end  

    case YourApp.Repo.query(sql,[1]) do
      {:ok, result} -> {:ok, result}
      {:error, err} -> YourApp.Repo.rollback(err)
    end  

  end)

23:08:09.556 [debug] QUERY OK db=0.0ms idle=640.0ms begin []

23:08:09.636 [debug] QUERY OK db=15.0ms insert into peop (id) values (:1) [1]

23:08:09.639 [debug] QUERY ERROR db=16.0ms insert into peop (id) values (:1) [1]

23:08:09.646 [debug] QUERY OK db=0.0ms rollback [] {:error, %DBConnection.ConnectionError{ message: "'ORA-00001: unique constraint (JAM.PEOP_PKEY) violated\n'", reason: :error, severity: :error }}

[vasumur]

Used ecto transaction and set auto commit off by default for connections to accomplish this. Thanks @vstavskyi