How to execute a transaction with the model?

[Nicolab]

Hello,

I am trying to execute a transaction with the model.

It works:

User.adapter.database.transaction do |tx|
  # here the lang is "en"
  ret = tx.connection.exec "UPDATE users SET lang=$1 WHERE id=$2", "fr", 17
  tx.rollback
  puts "rollback"

  # Ok, here the lang is "en" again
   pp User.find 17
end

It does not work:

User.adapter.database.transaction do |tx|
  user = User.find 17
  break unless user

  # here the lang is "en"
  user.update lang: "fr"

  tx.rollback
  puts "rollback"

  # here the lang is "fr" (updated)
  pp User.find 17
end

Where am i wrong? Maybe I need to pass the tx.connection to the User model?

[msa7]

any updates here?

[kalinon]

There does not seem to be any support for transactions via Granite models.

Nor does there seem to be a way to have the model use the transaction connection for its changes. It defaults to using the models adapter connection, which is why the transaction cannot be rolled back.

[crimson-knight]

Thank you @kalinon

I think that supporting transactions is important, so I've labeled this as part of bringing Granite up to feature parity with ActiveRecord and added the label for this being an enhancement.

[kalinon]

Just some further comments on how this example is working:

# TX comes from the DB shard not granite
User.adapter.database.transaction do |tx| 
  user = User.find 17
  break unless user

  # here the lang is "en"
  user.update lang: "fr"  # This update uses a different connection which is not having a `transaction` happening

  tx.rollback           # This performs a rollback but on the `tx` connection
  puts "rollback"

  # here the lang is "fr" (updated)
  pp User.find 17
end

when you get to User.adapter.database its no longer in Granite

So if we wanted this behavior we would need to build a whole transaction feature, where any DB action within the block, would use that connection. Allowing a rollback to occur.

he was able to roll back in his first example because hes calling the tx directly:

  ret = tx.connection.exec "UPDATE users SET lang=$1 WHERE id=$2", "fr", 17
  tx.rollback

This feature would probably require some sort of mechanism that checks what thread you are in, and gives you the same connection. However, it would need to probably be implemented on a Granite call (i.e User.transaction) and not via the DB lib.