楽したい

常にWhat's upの精神

insert ignore intoでdeadlockしていたのを解決したので偉い

Database

最近あるシステムで大量にdeadlockが発生していたのを修正しました。
修正方法が初めてやるタイプの修正だったのと、解決するまでにかなり時間がかかったので記事書いてみました。

具体的な事例

下記の1~3の実装がされている箇所でdeadlockが発生していました。

  1. バッチ処理の一部に、ループ内でinsert ignore into ~している処理が含まれている。バッチ処理なので複数回呼ばれる。
  2. show engine innodb statusを叩いて、「LATEST DETECTED DEADLOCK」セクションの「WAITING FOR THIS LOCK TO BE GRANTED」に「lock_mode X insert intention waiting」と表示されている。
  3. 特定のカラムでUNIQUE KEYが設定されている。

それぞれのイメージは下記です。

1番の実装イメージ

// トランザクション開始
start_transanction();

// 保存するデータの配列
$data_list = [
  [1, 11],
  [2, 22],
  // こんな感じのデータが大量にある
];

// 過去の経緯的にデータを分割して入れないとエラーになってしまうらしく、50件ずつデータをinsertするためarray_chunk()で50件毎に分割する。
 $chunks = array_chunk($data_list, 50);

// chunk毎(=50件毎)にデータをinsert ignore intoするためのループ
foreach($chunks as $i => $chunk) {
  // 下記のようなinsert ignore into文がDBに投げられて処理される。ここでdeadlockが多発していた。
  insert ignore into test_table values(1, 11), (2, 22)・・・(50, 5050)
}

// 他の関係あるテーブルの更新をする処理が続く

// 処理終わったらコミット
commit;

2番のイメージ例。(関係なさそうなところは省略。supremumと「lock_mode X insert intention waiting」が出力されてることが大事)。

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
省略
INSERT IGNORE INTO 省略
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
省略 lock_mode X insert intention waiting
省略
0: len 8; hex 73757072656d4c23; asc supremum;;

*** (2) TRANSACTION:
省略
INSERT IGNORE INTO 省略
*** (2) HOLDS THE LOCK(S):
省略
0: len 8; hex 73757072656d4c23; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
省略 lock_mode X insert intention waiting
省略
0: len 8; hex 73757072656d4c23; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

3番のテーブルイメージ

CREATE TABLE `test_table` (
  `test_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` bigint(20) NOT NULL,
  `b_id` bigint(20) NOT NULL,
  PRIMARY KEY (`test_id`),
  UNIQUE KEY `a_b_id` (`a_id`,`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

前提知識

対応方法

結論的には、TX分離レベルをREPEATABLE READからREAD COMMITTEDに変更したらdeadlockが発生しなくなりました。
修正方法のイメージは下記です。

修正イメージ

// 下記のSQL文で、TX分離レベルをREAD COMMITTEDに変更しておく。
$this->db->query('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;');

// トランザクション開始
start_transanction();

// 以下変更なし

原因と対応方法解説

今回のdeadlock発生原因はsupremumレコードがIXロックされてしまい発生している感じでした。
具体的には下記のような流れでdeadlockが発生していると思われました(多分)。

  • TX2: insert文で、supremumレコードに対してXロックを取得する。
  • TX1: insert文で、supremumレコードに対してIXロックを取得待ちになる。
  • TX2: insert文で、supremumレコードに対してIXロックを取得する。(既にTX1がIXロック取得待ちなため、TX2にIXロックを付与できずdeadlockすると思われる)

上記のdeadlockはIXロックを取得してしまうことが原因なので、TX分離レベルをREAD COMMITTEDに変更することによってIXロックを取得しないようにしました。
今回の場合、insert ignore文だったため、同一レコードがinsertされる時にエラーが出ても特に問題なく処理が進むので上記で対応できました。
また、TX分離レベルをREAD COMMITTEDに変更するとファントムリードや反復不能読み取りが発生する可能性が発生しますが、今回はトランザクション内でSELECTした結果を使って何か別の処理をしたりなどはしていなかったため無視できました。

感想

解決するにはしたが、めちゃくちゃ時間かかったし今でも本当に理解があっているのかちょっと謎い。
MySQLは難C。

SQL50本ノックの環境構築をしようとしてサポートページからダウンロードしたシェルスクリプトを実行するとエラーになってしまう問題

UNIX 技術書

SoftwareDesign2017年11月号の特集にあるSQL50本ノックをやろうとして公式サイトから環境構築のためのシェルスクリプトをダウンロードして、下記コマンドで実行しようとするとエラーになる。

実行コマンド

sh postgres_initialize.sh

エラー抜粋

Step 2/5 : RUN apt-get update &&   apt-get install -y wget unzip &&   wget https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/pagila/pagila/pagila-0.10.1.zip -O /tmp/pagila-0.10.1.zip &&   unzip /tmp/pagila-0.10.1.zip -d /tmp &&   cat /tmp/pagila-0.10.1/pagila-schema.sql |   grep -v "CREATE PROCEDURAL LANGUAGE plpgsql" > /docker-entrypoint-initdb.d/pagila_init.sql &&   cat /tmp/pagila-0.10.1/pagila-data.sql >> /docker-entrypoint-initdb.d/pagila_init.sql
 ---> Running in 02819260eb91
Get:1 http://security.debian.org jessie/updates InRelease [44.9 kB]
Ign http://deb.debian.org jessie InRelease
Get:2 http://apt.postgresql.org jessie-pgdg InRelease [56.5 kB]
Get:3 http://deb.debian.org jessie-updates InRelease [7,340 B]
Get:4 http://deb.debian.org jessie Release.gpg [2,420 B]
Get:5 http://deb.debian.org jessie Release [148 kB]
Get:6 http://security.debian.org jessie/updates/main amd64 Packages [825 kB]
Get:7 http://deb.debian.org jessie/main amd64 Packages [9,098 kB]
Get:8 http://apt.postgresql.org jessie-pgdg/main amd64 Packages [204 kB]
Get:9 http://apt.postgresql.org jessie-pgdg/9.6 amd64 Packages [1,690 B]
Fetched 10.4 MB in 6s (1,594 kB/s)
W: Failed to fetch http://deb.debian.org/debian/dists/jessie-updates/InRelease  Unable to find expected entry 'main/binary-amd64/Packages' in Release file (Wrong sources.list entry or malformed file)

E: Some index files failed to download. They have been ignored, or old ones used instead.
The command '/bin/sh -c apt-get update &&   apt-get install -y wget unzip &&   wget https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/pagila/pagila/pagila-0.10.1.zip -O /tmp/pagila-0.10.1.zip &&   unzip /tmp/pagila-0.10.1.zip -d /tmp &&   cat /tmp/pagila-0.10.1/pagila-schema.sql |   grep -v "CREATE PROCEDURAL LANGUAGE plpgsql" > /docker-entrypoint-initdb.d/pagila_init.sql &&   cat /tmp/pagila-0.10.1/pagila-data.sql >> /docker-entrypoint-initdb.d/pagila_init.sql' returned a non-zero code: 100

解決方法

ググったらそれっぽいのがあった。 https://superuser.com/questions/1423486/issue-with-fetching-http-deb-debian-org-debian-dists-jessie-updates-inrelease

上記を参考にして、公式サイトから落としてきたDockerfileにコマンドを追記してあげる。 追記する箇所は、Dockerfileの「FROM postgres:9.6.5」と「RUN apt-get update && \ ・・・」の間。

Dockerfile追記例

FROM postgres:9.6.5

# 追記するコマンドここから
RUN printf "deb http://archive.debian.org/debian/ jessie main\ndeb-src http://archive.debian.org/debian/ jessie main\ndeb http://security.debian.org jessie/updates main\ndeb-src http://security.debian.org jessie/updates main" > /etc/apt/sources.list
# ここまで

RUN apt-get update && \
  apt-get install -y wget unzip && \
  ・・・
  ・・・

追記したコマンドは、apt-getでパッケージを取得する際にダウンロード先を別の場所から取得するようにするためのコマンドらしい。 /etc/apt/sources.listがダウンロード先を指定できるファイルで、編集したらapt-get updateでアップデートをする必要があるので上記の位置にコマンドを追記したらうまいこと動いてくれるっていう感じ。

追記したらシェルスクリプトを実行してあげて、postgresにloginできたらOK。

参考 /etc/apt/sources.listについての説明 https://www.garunimo.com/program/linux/_etc_apt_sources_list.php