[Feature Proposal] Atomic Update and Select

rubyonrails-core
,
1 
class Ticket
  enum :status, [:unassigned, :assigned, :resolved, :punt], prefix: true
  belongs_to :assigned_to, classname: "User"

  def self.get_me_a_ticket(user)
    t = Ticket.unassigned.first
    # race condition here
    t.update!(status: :assigned, assigned_to: user)
    t
  end
end

If this is active enough, you get conflicts. So you have to bring back a bunch of records and mark them as assigned and hope you don’t get a double hit.

Does anyone do something like this and know how to do this without introducing a new method to ActiveRecord?

My proposal is to use Postgres and sqlite’s update and RETURNING

  # UPDATE tickets
  #   SET status = 1,
  #       assigned_to = 55
  # WHERE status = 0
  # LIMIT 1
  # RETURNING *
  def self.get_me_a_ticket(user)
    Ticket.unassigned
          .limit(1)
          .update_all_returning(status: :assigned, assigned_to: user)
  end

I thought of a couple of design tradeoffs, and chose what I thought worked best:

  • I did not want to introduce a new method, but update_all currently returns an integer, and changing that return type isn’t ideal. Alternative is a new update_all_returning method or another name.
  • Postgres and SqlLite3 supportRETURNING with INSERT, DELETE and UPDATE, and MySql does not, as reflected by supports_insert_returning. MiriaDB supports all except for UPDATE RETURNING, so I introduced supports_update_returning with the expected default value.
  • MySql does not support RETURNING at all. Calling update_all throws an exception rather than implementing a shim. I did the same.
  • update_all_returning and update_all have most of the same code. I could have modified update_all, refactored update_all and update_all_returning to use a common method, or move it out to a query helper method. I chose to keep the code local.
  • Since the upsert case is implemented with different SQL on different databases, this is implemented in the connection layer. For update_all_returning I opted to introduce Arel and an update_returning method.
  • Will circle back to convert our current SQL munging to use Arel in another PR since that seemed out of scope.

I’d appreciate any feedback or testimonials on how this will change your life.

EDIT: oops, Add update_all_returning for atomic update-and-fetch by kbrock · Pull Request #57073 · rails/rails · GitHub

[Feature Proposal] ActiveRecord update_all/delete_all support use sql's returning clause statements
2

Ok, looks like this was discussed in [Feature Proposal] ActiveRecord update_all/delete_all support use sql's returning clause statements as a posibility