CODEWITHSUNDEEP
sqlruby-on-railsmariadbdatabase-deadlocksgalera
MrS
MrS

Reputation: 1

Deadlock while Pessimistic Locking in Ruby on Rails

I get a deadlock error while pessimistic locking in Ruby on Rails.

Versions:
MariaDB: Docker mariadb:10.11
Ruby: ruby-3.3.0 via rvm
Rails: 8.0.1
MySql2: 0.5

I have a three instances of an RoR application that each connects to the same database in a Galera MariaDB cluster (with Master-Master)(different nodes). I use pessimistic locking for one of my endpoints and use a delay (i used two seconds) in the transaction to test for problems like this:

Car.transaction do
      car.lock!
      last_bid = car.bids.order(bid_price: :desc).first
      sleep ENV['SIMULATED_DELAY'].to_i if ENV['SIMULATED_DELAY'].present?
      if last_bid.nil? || params[:bid_price].to_f > last_bid.bid_price
        Bid.create!(
          car: car,
          user: User.find(params[:user_id]),
          bid_price: params[:bid_price]
        )
        render json: { message: ' successfully' }, status: :created
      else
        render json: { error: 'must be higher than the current highest bid' }, status: :unprocessable_entity
      end
    end

When I send requests to each of my RoR instances the first succeded and the two others fail with following error:

{"status":500,"error":"Internal Server Error","exception":"#\u003cActiveRecord::Deadlocked: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction\u003e","traces":{"Application Trace":[],"Framework Trace":[],"Full Trace":[]}}

Started POST "/users/1/cars/1/bids" for 172.18.0.1 at 2025-01-17 08:44:12 +0000
2025-01-17 09:44:12   ActiveRecord::SchemaMigration Load (1.4ms)  SELECT `schema_migrations`.`version` FROM `schema_migrations` ORDER BY `schema_migrations`.`version` ASC /*application='PoC'*/
2025-01-17 09:44:13 Processing by BidsController#create as */*
2025-01-17 09:44:13   Parameters: {"bid_price"=>5000, "user_id"=>"1", "car_id"=>"1", "bid"=>{"bid_price"=>5000}}
2025-01-17 09:44:13   Car Load (0.8ms)  SELECT `cars`.* FROM `cars` WHERE `cars`.`id` = 1 LIMIT 1 /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:13   ↳ app/controllers/bids_controller.rb:3:in `create'
2025-01-17 09:44:13   TRANSACTION (0.6ms)  BEGIN /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:13   ↳ app/controllers/bids_controller.rb:6:in `block in create'
2025-01-17 09:44:13   Car Load (4.0ms)  SELECT `cars`.* FROM `cars` WHERE `cars`.`id` = 1 LIMIT 1 FOR UPDATE /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:13   ↳ app/controllers/bids_controller.rb:6:in `block in create'
2025-01-17 09:44:13   Bid Load (0.4ms)  SELECT `bids`.* FROM `bids` WHERE `bids`.`car_id` = 1 ORDER BY `bids`.`bid_price` DESC LIMIT 1 /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:13   ↳ app/controllers/bids_controller.rb:7:in `block in create'
2025-01-17 09:44:13   User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1 /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:13   ↳ app/controllers/bids_controller.rb:8:in `block in create'
2025-01-17 09:44:15   Bid Create (3.8ms)  INSERT INTO `bids` (`user_id`, `car_id`, `bid_price`, `timestamp`, `created_at`, `updated_at`) VALUES (1, 1, 5000, '2025-01-17 08:44:15.199472', '2025-01-17 08:44:15.235149', '2025-01-17 08:44:15.235149') RETURNING `id` /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:15   ↳ app/controllers/bids_controller.rb:11:in `block in create'
2025-01-17 09:44:15   TRANSACTION (0.3ms)  ROLLBACK /*action='create',application='PoC',controller='bids'*/
2025-01-17 09:44:15   ↳ app/controllers/bids_controller.rb:5:in `create'
2025-01-17 09:44:15 Completed 500 Internal Server Error in 2203ms (ActiveRecord: 20.9ms (5 queries, 0 cached) | GC: 41.7ms)
2025-01-17 09:44:15 
2025-01-17 09:44:15 
2025-01-17 09:44:15   
2025-01-17 09:44:15 ActiveRecord::Deadlocked (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction)
2025-01-17 09:44:15 Caused by: Mysql2::Error (Deadlock found when trying to get lock; try restarting transaction)
2025-01-17 09:44:15 
2025-01-17 09:44:15 Information for: ActiveRecord::Deadlocked (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction):
2025-01-17 09:44:15   
2025-01-17 09:44:15 
2025-01-17 09:44:15 Information for cause: Mysql2::Error (Deadlock found when trying to get lock; try restarting transaction):
2025-01-17 09:44:15   
2025-01-17 09:44:15 app/controllers/bids_controller.rb:11:in `block in create'
2025-01-17 09:44:15 app/controllers/bids_controller.rb:5:in `create'

I get nothing usefull (only pluses and dashes) when i try to show engine innodb status;.

I have tried to use ActiveRecord::TouchLater with no success.

class Bid < ApplicationRecord
  belongs_to :user, touch: true
  belongs_to :car, touch: true

  validates :bid_price, presence: true
  validates :timestamp, presence: true
end

I suspect that the different instances of MariaDB don't communicate there execution queues, but i don't know how to prove it.

Upvotes: 0

Views: 50

Answers (1)

danblack
danblack

Reputation: 14736

The optimistic locking of Galera isn't suited to scenarios likely to have contention like bidding.

Recommend for this transaction a configuration that routes all application instances the updates to the same galera node.

A ruby equalivant of:

UPDATE carhighestbid
SET user = {:userid}, bid= {:bid}
WHERE carid = {:carid} AND bid < {:bid}

And examine the rows updated to determine if successful.

The database has no easy way to say the highest bid is always successful in a deadlock.

Upvotes: 0

Related Questions