Question: How can I avoid deadlocks on my database when using ActiveJob in Rails?

Question

How can I avoid deadlocks on my database when using ActiveJob in Rails?

Answers 1
Added at 2016-09-28 16:09
Tags
Question

I haven't had a lot of experience with deadlocking issues in the past, but the more I try to work with ActiveJob and concurrently processing those jobs, I'm running into this problem. An example of one Job that is creating it is shown below. The way it operates is I start ImportGameParticipationsJob and it queues up a bunch of CreateOrUpdateGameParticipationJobs.

When attempting to prevent my SQL Server from alerting me to a ton of deadlock errors, where is the cause likely happening below? Can I get a deadlock from simply selecting records to populate an object? Or can it really only happen when I'm attempting to save/update the record within my process_records method below when saving?

ImportGameParticipationsJob

class ImportGameParticipationsJob < ActiveJob::Base
  queue_as :default

  def perform(*args)
    import_participations(args.first.presence)
  end

  def import_participations(*args)
    games = Game.where(season: 2016)
    games.each do |extract_record|
      CreateOrUpdateGameParticipationJob.perform_later(extract_record.game_key)
    end
  end   
end

CreateOrUpdateGameParticipationJob

class CreateOrUpdateGameParticipationJob < ActiveJob::Base
  queue_as :import_queue

   def perform(*args)
     if args.first.present?
       game_key = args.first

       # get all particpations for a given game
       game_participations = GameRoster.where(game_key: game_key)
       process_records(game_participations)
     end
   end

   def process_records(participations)
     # Loop through participations and build record for saving...
     participations.each do |participation|
       if participation.try(:player_id)
         record = create_or_find(participation)
         record = update_record(record, participation)
       end

       begin
         if record.valid?
           record.save
         else

         end
       rescue Exception => e

       end
     end
   end

   def create_or_find(participation)
     participation_record = GameParticipation.where(
       game_id: participation.game.try(:id),
       player_id: participation.player.try(:id))
       .first_or_initialize do |record|
         record.game = Game.find_by(game_key: participation.game_key)
         record.player   = Player.find_by(id: participation.player_id)
         record.club     = Club.find_by(club_id: participation.club_id)
         record.status   = parse_status(participation.player_status)
     end
     return participation_record
   end

   def update_record(record, record)
     old_status = record.status
     new_status = parse_status(record.player_status)
     if old_status != new_status
       record.new_status = record.player_status
       record.comment    = "status was updated via participations import job"
     end
     return record
   end

end
Answers
nr: #1 dodano: 2016-09-28 17:09

They recently updated and added an additional option you can set that should help with the deadlocking. I had the same issue and was on 4.1, moving to 4.1.1 fixed this issue for me.

https://github.com/collectiveidea/delayed_job_active_record

https://rubygems.org/gems/delayed_job_active_record

Problems locking jobs

You can try using the legacy locking code. It is usually slower but works better for certain people.

Delayed::Backend::ActiveRecord.configuration.reserve_sql_strategy = :default_sql

Source Show
◀ Wstecz