Commit a nested transaction

Question

Let's say I have a method that provides access to an API client in the scope of a user and the API client will automatically update the users OAuth tokens when they expire.

class User < ActiveRecord::Base

  def api
    ApiClient.new access_token: oauth_access_token,
                  refresh_token: oauth_refresh_token,
                  on_oauth_refresh: -> (tokens) {
                    # This proc will be called by the API client when an 
                    # OAuth refresh occurs
                    update_attributes({
                      oauth_access_token: tokens[:access_token],
                      oauth_refresh_token: tokens[:refresh_token]
                     })
                   }
  end

end

If I consume this API within a Rails transaction and a refresh occurs and then an error occurs - I can't persist the new OAuth tokens (because the proc above is also treated as part of the transaction):

u = User.first

User.transaction { 
  local_info = Info.create!

  # My tokens are expired so the client automatically
  # refreshes them and calls the proc that updates them locally.
  external_info = u.api.get_external_info(local_info.id)

  # Now when I try to locally save the info returned by the API an exception
  # occurs (for example due to validation). This rolls back the entire 
  # transaction (including the update of the user's new tokens.)
  local_info.info = external_info 
  local_info.save!
}

I'm simplifying the example but basically the consuming of the API and the persistence of data returned by the API need to happen within a transaction. How can I ensure the update to the user's tokens gets committed even if the parent transaction fails.

Unfortunately, no. The "nested transaction" is called by an API client that I don't have control over. Imagine that inside my transaction, I need to consume an API and my OAuth token expires. The API client refreshes my tokens but can't persist them because the transaction fails downstream. — user1032752
– user1032752, Commented Jun 16, 2017 at 20:16
Perhaps you need Autonomous Transactions. This article may help you grasp the concept and apply it to your ruby scenario. — cachique
– cachique, Commented Jun 16, 2017 at 20:33
Thanks for the link. Do you know of any Ruby/Rails abstraction layers that can help with this? I'd rather avoid writing stored procedures. — user1032752
– user1032752, Commented Jun 17, 2017 at 17:17

Jon Sully · Accepted Answer · 2023-04-16 15:47:19Z

4

+50

Have you tried opening a new db connection inside new thread, and in this thread execute the update

    u = User.first

    User.transaction { 
       local_info = Info.create!
   
       # My tokens are expired so the client automatically
       # refreshes them and calls the proc that updates them locally.
       external_info = u.api.get_external_info(local_info.id)

       # Now when I try to locally save the info returned by the API an exception
       # occurs (for example due to validation). This rolls back the entire 
       # transaction (including the update of the user's new tokens.)
       local_info.info = external_info 
       local_info.save!

       # Now open new thread
       # In the new thread open new db connection, separate from the one already opened
       # In the new connection execute update only for the tokens
       # Close new connection and new thread
       Thread.new do
          ActiveRecord::Base.connection_pool.with_connection do |connection|
             connection.execute("Your SQL statement that will update the user tokens")        
          end
       end.join
    }

I hope this helps

edited Apr 16, 2023 at 15:47

Jon Sully

3633 silver badges6 bronze badges

answered Jun 23, 2017 at 22:36

Nermin

6,11016 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1032752 Over a year ago

Works great - thanks a lot! In fact, you can just wrap the transaction in the new Thread and it works.

pduey · Accepted Answer · 2019-09-26 16:13:09Z

2

Nermin's (the accepted) answer is correct. Here's an update for Rails >= 5.0

Thread.new do
  Rails.application.executor.wrap do
    record.save
  end
  # Note: record probably won't be updated here yet since it's async
end

Documented here: Rails guides threading and concurrency

answered Sep 26, 2019 at 16:13

pduey

3,7962 gold badges26 silver badges34 bronze badges

Comments

Mimi Schatz · Accepted Answer · 2017-06-16 22:30:48Z

0

This discussion from a previous question might help you. It looks like you can set a requires_new: true flag and essentially mark the child transaction as a sub transaction.

User.transaction { 
  User.transaction(requires_new: true) { 
    u.update_attribute(:name, 'test') 
  }; 

  u.update_attribute(:name, 'test2'); 

  raise 'boom' 
}

answered Jun 16, 2017 at 22:30

Mimi Schatz

1

1 Comment

user1032752 Over a year ago

Thanks for your comment. I did try this but it looks like it offers the opposite of what I need. From what I understand, this will commit the second update if the nested transaction experiences an exception not the other way around.

Collectives™ on Stack Overflow

Commit a nested transaction

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related