複数行 UPDATE で発生するデッドロックを回避する

こんにちは、masm11 です。

今回は、RDBMS で複数行の UPDATE すると割と簡単に起きるデッドロックを回避する方法を探ります。 Rails を用い、RDBMS としては PostgreSQL を使います。

準備

まずは、Rails のプロジェクトを作ります。

rails new proj --skip-bundle
cd proj
sed -i -e 's/sqlite3/pg/g' Gemfile    # sqlite3 を pg に書き換え
bundle install --path=vendor/bundle

PostgreSQL にも DB とユーザを作成します。

createdb -U postgres proj
createuser -U postgres proj

config/database.yml は以下のように設定しました。

default: &default
  adapter: postgresql
  user: proj
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: proj

では、モデルを作ります。

bin/rails g model SampleTable col1

マイグレーション時にテスト用データも作成してしまいます。

class CreateSampleTables < ActiveRecord::Migration[5.2]
  def change
    create_table :sample_tables do |t|
      t.string :col1

      t.timestamps
    end
    SampleTable.create!([ { col1: '123' }, { col1: '456' } ])
  end
end

マイグレーションします。

bin/rails db:migrate

DB 上のデータを確認してみましょう。

luna:proj % psql -U proj -c 'select * from sample_tables' proj
 id | col1 |         created_at         |         updated_at         
----+------+----------------------------+----------------------------
  1 | 123  | 2020-03-11 07:31:53.594273 | 2020-03-11 07:31:53.594273
  2 | 456  | 2020-03-11 07:31:53.596021 | 2020-03-11 07:31:53.596021
(2 行)

luna:proj %

ちゃんとできていますね。

デッドロックさせる

さて、次にタスクを作成します。

lib/tasks/sample_task.rb を以下のように作成しました。

class Tasks::SampleTask

  def self.execute

    loop do
      SampleTable.update_all({col1: 'foo'})
      $stdout.print '.'
    end

  end

end

これを読み込ませるため、config/application.rb に以下の設定を追加しました。

    config.autoload_paths += ["#{config.root}/lib"]

実行します。

luna:proj % bin/rails runner Tasks::SampleTask.execute
..............................................................

ここまでは動いていますね。

ではこれを3枚の端末で同時実行します。

/home/masm/t/proj/vendor/bundle/ruby/2.7.0/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:611:in `exec_params': PG::TRDeadlockDetected: ERROR:  deadlock detected (ActiveRecord::Deadlocked)
DETAIL:  Process 875364 waits for ShareLock on transaction 178082; blocked by process 875263.
Process 875263 waits for ShareLock on transaction 178081; blocked by process 875364.
HINT:  See server log for query details.
CONTEXT:  while rechecking updated tuple (8,3) in relation "sample_tables"
: UPDATE "sample_tables" SET "col1" = 'foo'

起きました。デッドロックです。

SQL としては、上記にあるように、

UPDATE "sample_tables" SET "col1" = 'foo'

を実行しているだけです。

これは、id が 1の行と2の行を、順不同で書き換えているためです。

1 をロックして書き換えた後に 2を書き換えるためにロックしようとしているプロセス
2 をロックして書き換えた後に 1を書き換えるためにロックしようとしているプロセス

両者が互いに相手を待ってしまうのです。

解決策

これを回避するには、書き換え順序を保証してやる必要があります。例えば id 順にするのが良いでしょう。

しかし、

      SampleTable.order(:id).update_all({col1: 'foo'})

これでは、

UPDATE "sample_tables" SET "col1" = 'foo' WHERE "sample_tables"."id" IN (SELECT "sample_tables"."id" FROM "sample_tables" ORDER BY "sample_tables"."id" ASC)

となり、SELECT は id 順になるものの、結局 UPDATE 自体は順不同になってしまいます。

SQL としては、

UPDATE "sample_tables" SET "col1" = 'foo' ORDER BY "id" ASC

のようになって欲しいところです。ですが、MySQL では UPDATE に ORDER BY が指定できるそうですが、 PostgreSQL では使えません。

      SampleTable.transaction do
        a = SampleTable.lock.order(:id).update_all({col1: 'foo'})
      end

これだと

BEGIN
UPDATE "sample_tables" SET "col1" = 'foo' WHERE "sample_tables"."id" IN (SELECT "sample_tables"."id" FROM "sample_tables" ORDER BY "sample_tables"."id" ASC)
COMMIT

となってしまいます。.lock が効いていません。サブクエリに FOR UPDATE が付くかと思ったのですが。

      SampleTable.transaction do
        a = SampleTable.lock.order(:id)
        a.pluck(:id)
        a.update_all({col1: 'foo'})
      end

こうすると、SQL は以下のようになります。

BEGIN
SELECT "sample_tables"."id" FROM "sample_tables" ORDER BY "sample_tables"."id" ASC FOR UPDATE
UPDATE "sample_tables" SET "col1" = 'foo' WHERE "sample_tables"."id" IN (SELECT "sample_tables"."id" FROM "sample_tables" ORDER BY "sample_tables"."id" ASC)
COMMIT