MySQL道普請便り

第47回トランザクション分離レベルを変更する

一括りにデータベースのトランザクションと言っても、実はデータベースや設定によってトランザクションを複数走らせた時に、微妙に挙動が異なる場合があります。この違いは、データベースのACID特性のうちのIsolation(独立性)に関わる設定が違うため発生します。この設定はトランザクション分離レベルと言って、並列性やデータの整合性をどこまで求めるかによって適切に選ぶ必要があります。そこで今回は、MySQLでトランザクション分離レベルを変更する方法を紹介したいと思います。

検証環境

第10回 yum, rpmインストールにおけるMySQL 5.6とMySQL 5.7の違いで紹介されたyumリポジトリを使用したインストールを利用しています。また、MySQLのバージョンは5.7.18を使用しています。OSは、今回はCentOS 7.3上で確認を行っています。

現在のトランザクション分離レベルを確認する

変更を行う前に、現在のトランザクション分離レベルが一体どのような設定になるかを確認してみましょう。確認するには、以下のコマンドを実行します。txはTransactionの略称です。

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

最初は設定を変更していないため、globalとsessionで比較しても、デフォルトのREPEATABLE READが設定されていることがわかります。

トランザクション分離レベルを変更する

トランザクション分離レベルを変更する際には、以下の3つの方法があります。

  • SET TRANSACTION構文を使って変更する
  • mysqldから起動する際にオプションを指定する
  • my.cnfに設定する

続いて、それぞれの設定方法に関して説明をしていきます。

SET TRANSACTION構文で変更する

まずは、SET TRANSACTION構文を使って変更します。この構文は以下のような構造をしています。また、この構文でGLOBALの変数を変更するためにはSUPER権限が必要なので注意してください。SET TRANSACTION構文は、トランザクション分離レベル以外にもREAD ONLYやREAD WRITEなども指定できますが、今回は説明を行いません。

mysql> SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 指定したいトランザクション分離レベル

指定できるトランザクション分離レベルとしてSERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTEDの4種類が選択できます。デフォルトではREPEATABLE READが設定されています。GLOBALやSESSIONオプションは、その設定がグローバルに設定するものなのか、そのセッション限りなのかを指定します。指定がされなかった場合は、指定を行ったセッションで次回に発生するトランザクションに、指定したトランザクション分離レベルが設定されます。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | SERIALIZABLE   |
+-----------------------+----------------+
1 row in set (0.00 sec)

SESSIONで設定した場合はコネクションを再接続した際に、GLOBALで設定した場合は再起動した時に、設定が戻ってしまうことに注意が必要です。設定の永続化を行いたい場合は、後述するオプションを指定して起動するようにするか、もしくはmy.cnfで設定をするようにしましょう。うっかり忘れてしまうと思わぬタイミングで足をすくわれる事があります。

余談ではありますが、MySQL8.0ではSET PERSISTというコマンドが追加されています。こちらを利用すると設定をコマンドラインから永続化することができます。このコマンドで設定した場合、mysqld-auto.cnfにJSONファイルとして書き出され、読み込む際に一番最後に読み込みが行われます。

mysql> SET PERSIST TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

# pwd
/var/lib/mysql
# cat mysqld-auto.cnf
{ "mysql_server": {"tx_isolation": "SERIALIZABLE" } }

起動時にオプションを指定をする

CentOS7以降だとsystemctlコマンドを使って起動します。起動時に指定するOPTIONは、まとめてMYSQLD_OPTSというオプションに登録することで使えます。set-environmentオプションで設定を行います。一部キーワードがSET TRANSACTION構文と異なることに注意が必要です。オプションに使える値はSERIALIZABLE, REPEATABLE-READ, READ-COMMITTED, READ-UNCOMMITEDの4つです。

# systemctl set-environment MYSQLD_OPTS="--transaction-isolation=SERIALIZABLE"
# systemctl restart mysqld.service

再起動を行った後に起動後確認をすると、SERIALIZABLEになっています。

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| SERIALIZABLE          | SERIALIZABLE   |
+-----------------------+----------------+
1 row in set (0.00 sec)

またMYSQLD_OPSに設定したオプションを解除したい場合は、以下のようにsystemctl unset-environmentコマンドを実行して再起動すると元の設定に戻ります。

# systemctl unset-environment MYSQLD_OPTS
# systemctl restart mysqld.service

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

以上からデフォルトのREPEATABLE-READに戻っていることがわかります。もし、systemctlから設定が行われているかどうかを確認したい場合は、systemctl show-environmentコマンドで設定した環境変数が確認できます。

# systemctl show-environment
LANG=en_US.UTF-8
MYSQLD_OPTS=--transaction-isolation=SERIALIZABLE
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

このように起動時にオプションとして設定することもできます。ただし後述するように、my.cnfでも設定をする場合は混在させると見落としがちなので、設定する場合はどの様に運用するかをきっちりと決めて明文化してから設定を行ったほうが良いでしょう。

my.cnfファイルで設定をする

my.cnfファイルの読み込み順やオプションファイルの書式に関しては、第31回 MySQLのオプションファイル my.cnfの豆知識[その1]第33回 MySQLのオプションファイル my.cnfの豆知識[その2]に詳細な説明があるので、気になる方はそちらも合わせて参照してください。

my.cnfで指定する場合は、以下のように[mysqld]セクションで設定を行います。オプションに使える値は起動時にオプションで指定する時と同じSERIALIZABLE, REPEATABLE-READ, READ-COMMITTED, READ-UNCOMMITEDの4つです。今回は/etc/my.cnf内に以下の設定を行いました。

[mysqld]
transaction-isolation = READ-COMMITTED

その後再起動を行い、確認した結果が以下のようになります。

# systemctl restart mysqld.service

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

設定がREAD-COMMITEDに変更されていることがわかります。

まとめ

今回はトランザクション分離レベルを変更する方法について紹介しました。トランザクション分離レベルの各設定の違いに関しては、次回以降で説明させていただきたいと思います。

おすすめ記事

記事・ニュース一覧