Can somebody help me with a threading/fiber issue.

[timuckun]

I am working on fixing the broken rack tests on the techempower benchmarks. I decided to use sequel instead of the raw pg driver for the tests because I want to be able to run the tests with jruby and jdbc as well as mri. The tests say that when you do the updates portion of the test you must select N number records separately (not in the same transaction), put those in an array and then change the value to a random number and then persist the data back into the database. When you do the updates you don't have to update each record separately you can batch the update calls into the same call.

I do a simple string concatenation for the updates like this.

def update_worlds(count)
    results = select_worlds(count)
    update_statements = String.new
    update_statements << "Begin Transaction;\n"
    results.each do |result|
      result[:randomnumber] = random_id
      update_statements << "UPDATE World SET randomNumber = #{result[:randomnumber]} WHERE id = #{result[:id]};\n"
    end
    update_statements << 'commit transaction;'
    # puts update_statements
    @connection.synchronize do
      @connection.run(update_statements)
    end
    results
  end

This sometimes causes a deadlock error in postgres

ack: 2023-05-16 10:30:57 +0000 Rack app ("GET /updatesqueries=20" - (172.19.0.2)): #
<Sequel::SerializationFailure:"PG::TRDeadlockDetected: ERROR:  deadlock detected\n
DETAIL:  Process 236 waits for ShareLock on transaction 1039; blocked by process 268.\n
Process 268 waits for ShareLock on transaction 1116; blocked by process 169.\n
Process 169 waits for ShareLock on transaction 1094; blocked by process 261.\n
Process 261 waits for ShareLock on transaction 1115; blocked by process 239.\n
Process 239 waits for ShareLock on transaction 1076; blocked by process 236.\n
HINT:  See server log for query details.\n
CONTEXT:  while updating tuple (19,153) in relation \"world\"\n">

This makes no sense to me as the selects are done already and each update statement updates a unique id.

I am using puma in this case puma does have

before_fork do
  Sequel::DATABASES.each(&:disconnect)
end

The problem is worse when I am using falcon and I use the :fiber_concurrency

Any ideas on how I can prevent these deadlocks?

[janko]

I've experienced similar deadlocks in Postgres, and the solution was to do the updates in a deterministic order (e.g. by ID). Because you're essentially doing multiple updates in a single (implicit) transaction, so this could deadlock if simultaneous but in different order.

[timuckun]

I have changed the query so that it's a single statement. This seems to help a lot. I'll run a more intensive test and see what happens.