Ruby on Rails悲观锁使用中出现死锁问题求助

环境版本信息 #

• MariaDB：Docker镜像mariadb:10.11
• Ruby：通过rvm安装的ruby-3.3.0
• Rails：8.0.1
• MySql2：0.5

问题场景 #

部署3个RoR应用实例，分别连接Galera MariaDB主主集群的不同节点。在出价接口中使用悲观锁，并通过添加2秒延迟模拟并发场景，核心代码如下：

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

错误表现 #

向3个RoR实例同时发送请求时，第一个请求成功，另外两个请求触发死锁错误：

错误响应 #

{"status":500,"error":"Internal Server Error","exception":"#<ActiveRecord::Deadlocked: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction>","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)

ActiveRecord::Deadlocked (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction)
Caused by: Mysql2::Error (Deadlock found when trying to get lock; try restarting transaction)

Information for: ActiveRecord::Deadlocked (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction):

Information for cause: Mysql2::Error (Deadlock found when trying to get lock; try restarting transaction):

app/controllers/bids_controller.rb:11:in `block in create'
app/controllers/bids_controller.rb:5:in `create'

已尝试的无效操作 #

• 执行show engine innodb status;仅得到无意义符号，未获取有效死锁详情
• 在Bid模型中添加touch: true关联，试图通过更新父模型避免死锁，代码如下，但无效果：

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

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

疑问与求助 #

怀疑Galera集群各节点的锁执行队列未同步，但不知如何验证该猜想，寻求可行的解决方案。

内容的提问来源于stack exchange，提问作者MrS