MySQLのオンラインDDLでDeadlockエラーになるケース

これは、MySQL Advent Calendar 2022 の7日目の記事です。

昨日は taka_yuki_04 さんでした。 next4us-ti.hatenablog.com

MySQL 5.6からオンラインDDLがサポートされ、DDL実行中でもデータの読み書きが可能ですが、DDL実行中にトランザクションの状況によってはDeadlockエラーが発生するケースがありますので、それを紹介したいと思います。

データの準備

検証に使った環境はMySQL 8.0.30です。検証用のテーブルとデータを作成します。

> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)
>SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

(MySQL5.6やisolation levelをREAD-COMMITTEDにしても結果は同じです)

CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  name varchar(10),
  PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO user (name) VALUES('a');
SELECT * FROM user;

+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.01 sec)

Deadlockエラーの検証

-- tx1
BEGIN;

SELECT id FROM user WHERE id = 1;

一つのセッション(tx1)でトランザクションを開始し検索を実行します。 検索が終わったことを確認したあとに異なるセッション(tx2)で下記を実行します。

-- tx2
ALTER TABLE user ADD INDEX index_name (name);

このインデックス作成のSQLWaiting for table metadata lockという状態で1つ目のセッション(tx1)が終わるまでDDLが実行されずに待たされます。 コレも嫌な動作の一つですが、この記事の本題ではありません。(参考 )

次にトランザクション実行中だった1つ目のセッションで更新処理を行うとDeadlockエラーが発生します。

-- tx1
UPDATE user SET name = 'b' WHERE id = 1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

エラーになった直後にWaiting for table metadata lockが解除されてインデックス作成が成功します。

-- tx2
ALTER TABLE user ADD INDEX index_name (name);

Query OK, 0 rows affected (46.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

userテーブルのレコードは1つ目のセッションでしか操作していないにも関わらずDeadlockが発生しました。

DDL実行時間が長い場合にエラーになるタイミング

次は先にDDLを実行していて、DDL実行に時間がかかる場合はどのタイミングでDeadlockが起きるでしょうか?

例えば下記のDDLが時間のかかるDDLだった場合です。

-- tx1
ALTER TABLE user ADD INDEX index_name (name);

オンラインDDLですのでDDL実行中でも下記のようなSQLは成功します。

-- tx2
BEGIN;
SELECT id FROM user WHERE id = 1;
UPDATE user SET name = 'b' WHERE id = 1;
COMMIT;

DDL実行中に下記のSELECTが終わったあとにDDLの実行が完了したとします。

-- tx3
BEGIN;
SELECT id FROM user WHERE id = 1;

DDL完了後にこのトランザクションで更新処理をするとDeadlockが発生します。

-- tx3
UPDATE user SET name = 'b' WHERE id = 1;

DDLの実行が完了するタイミングでDeadlockが発生します。

アプリケーションで試す

アプリケーションでこの問題が発生するかを試すために下記のような簡単なスクリプトを作りました。 インデックスの作成・削除を繰り返すプログラムです。

require "bundler/inline"

gemfile(true) do
  gem "activerecord", require: "active_record"
  gem "mysql2"
end

ActiveRecord::Base.establish_connection
ActiveRecord::Base.logger = Logger.new(STDOUT)

while true
  ActiveRecord::Base.connection.execute("ALTER TABLE user ADD INDEX index_name (name)")
  sleep 0.5
  ActiveRecord::Base.connection.execute("ALTER TABLE user DROP INDEX index_name")
  sleep 0.5
end

ddl.rbという名前で保存して下記のように実行します。

DATABASE_URL="mysql2://your_user:your_password@127.0.0.1:3306/your_db" ruby ddl.rb

上記を実行しながらアプリケーションに負荷をかけてDeadlockが発生するか試すことができます。

まとめ

  • オンラインDDLなので安全だと思っていたインデックス作成でもDeadlockが発生するケースがありました。
  • 今回はインデックス作成のDDLを使いましたが、カラム追加などのオンラインDDLでも同じようにエラーが発生します。
  • MySQLのDeadlockというエラーメッセージですが、デッドロックエラーで思い浮かぶ「2つ以上のトランザクションがお互いにロックを取り合ってエラーになる」のとは異なるケースでもDeadlockが発生するケースがいくつかあり、これもその一つです。
  • 今回はすぐに試せるように少ないデータ量で発生することを説明しました。
  • データ量が少なくDDLの実行時間が短くてもエラーが発生しますので、一概にすぐ終わるインデックス作成は安全とは言えないことがわかります。
  • 実行時間の長いDDLの場合はDDLが終わるタイミングでDeadlockエラーが起きることがあります。
  • アプリケーションでDeadlockエラーが発生したときにリトライするなどの対応が必要になります。
  • 自分のアプリケーションで発生するのか是非スクリプトを実行して試してみてください。