MySQLのトランザクション分離レベルを変更したときの動作を確認してみました。 ただの動作確認しかしていないので、性能がアップしたとかそういう役に立つような内容は一切書いていない記事です。
確認したことは以下の4つです。
- MySQLのデフォルトのトランザクション分離レベルであるREAD COMMITTEDでは、未コミットのレコードだけが読み取られない
- トランザクション分離レベルであるREAD UNCOMMITTEDでは、未コミットのレコードだけが読み取られない
- トランザクションアクセスレベルをデフォルトのREAD WRITEからREAD ONLYに変更することでテーブルへの変更（INSERT、UPDATE、DELETE）が拒否される
- セッションでのトランザクション分離レベル・トランザクションアクセスレベルの変更は次のSQL文（トランザクション）だけに反映される
参考文献
この記事は本当に役立つ内容が少ないので、一番最初にトランザクション分離レベルを理解するにあたって役立つ記事のリンクを紹介しておきます。
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.3.7 SET TRANSACTION ステートメント
- MySQLのトランザクション分離レベルについてまとめる - Zenn
- トランザクション分離レベルについてのまとめ - Qiita
- MySQL Performance Implications: InnoDB Isolation Modes
MySQLの公式ドキュメント
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.3.7 SET TRANSACTION ステートメント
トランザクション分離レベルを変更するSQL構文は以下のとおりです。
SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ... transaction_characteristic: { ISOLATION LEVEL level | access_mode } level: { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE } access_mode: { READ WRITE | READ ONLY }
たとえば、トランザクション分離レベルをREAD UNCOMMITTEDに変更する場合には以下のようにSQLを発行します。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
動作確認
Dockerを使ってMySQLを実行しました。 動作確認で利用したMySQLは8.0.34でした。
$ docker run -it --name test-mysql -e MYSQL_ROOT_PASSWORD=mysql -d mysql:latest
$ docker exec -it test-mysql bash -p
インストールが完了すれば以下のコマンドでログインできます（パスワードはmysql）。
# mysql -u root -h 127.0.0.1 -p
これから使うデータベースとテーブルを作成しておきます。
mysql> create database testdb; Query OK, 1 row affected (0.02 sec) mysql> create table testdb.user (id int, name varchar(10)); Query OK, 0 rows affected (0.10 sec)
デフォルトのトランザクション分離レベルREAD COMMITTED
1つ目のターミナルで以下のSQLを発行します。 コミット済のレコード2行と、未コミットのレコード1行が存在している状態にします。
mysql> insert into testdb.user values (1, 'alice'); Query OK, 1 row affected (0.02 sec) mysql> insert into testdb.user values (2, 'bob'); Query OK, 1 row affected (0.02 sec) mysql> select * from testdb.user; +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ 2 rows in set (0.00 sec) mysql> begin; mysql> insert into testdb.user values (3, 'cordie');
別で2つ目のターミナルを起動して、以下のSQLを発行します。 MySQLのデフォルトのトランザクション分離レベルであるREAD COMMITTEDでは、 未コミットのレコードだけが読み取られないことが確認できました。
mysql> select * from testdb.user; +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ 2 rows in set (0.00 sec) mysql> select @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only; +--------------------------------+--------------------------------+ | @@GLOBAL.transaction_isolation | @@GLOBAL.transaction_read_only | +--------------------------------+--------------------------------+ | REPEATABLE-READ | 0 | +--------------------------------+--------------------------------+ 1 row in set (0.01 sec) mysql> select @@SESSION.transaction_isolation, @@SESSION.transaction_read_only; +---------------------------------+---------------------------------+ | @@SESSION.transaction_isolation | @@SESSION.transaction_read_only | +---------------------------------+---------------------------------+ | REPEATABLE-READ | 0 | +---------------------------------+---------------------------------+ 1 row in set (0.01 sec)
トランザクション分離レベルをREAD UNCOMMITTEDに変更
さらに続けて2つ目のターミナルで以下のSQLを発行します。 トランザクション分離レベルをREAD UNCOMMITTEDに変更してみると、 未コミットのレコードを読み取ることができました。
mysql> set transaction isolation level read uncommitted; mysql> select @@SESSION.transaction_isolation, @@SESSION.transaction_read_only; +---------------------------------+---------------------------------+ | @@SESSION.transaction_isolation | @@SESSION.transaction_read_only | +---------------------------------+---------------------------------+ | REPEATABLE-READ | 0 | +---------------------------------+---------------------------------+ 1 row in set (0.01 sec) mysql> select @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only; +--------------------------------+--------------------------------+ | @@GLOBAL.transaction_isolation | @@GLOBAL.transaction_read_only | +--------------------------------+--------------------------------+ | REPEATABLE-READ | 0 | +--------------------------------+--------------------------------+ 1 row in set (0.01 sec) mysql> select * from testdb.user; +------+--------+ | id | name | +------+--------+ | 1 | alice | | 2 | bob | | 3 | cordie | +------+--------+ 3 rows in set (0.01 sec)
さらにさらに続けて2つ目のターミナルで以下のSQLを発行します。 セッションでのトランザクション分離レベルの変更は、 次のトランザクションにしか作用しないので2つ目のSQL文には反映されません。
mysql> select * from testdb.user; +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ 2 rows in set (0.00 sec)
トランザクションアクセスレベルの変更
トランザクションアクセスレベルはおそらくMySQLの独自の構文だと思います。
デフォルトのトランザクションアクセスレベルは読み取りも書き込みもできるモードになっています。 しかし、READ ONLYを指定することで読み取りだけを許可し、テーブルへの変更を禁止することができます。 パフォーマンス向上の可能性があるそうです。
2つ目のターミナルで以下のSQLを発行します。 トランザクション分離レベルをREAD UNCOMMITTEDに、トランザクションアクセスレベルをREAD ONLYに変更します。 トランザクションアクセスレベルがREAD ONLYであることでDELETE文が拒否されることが確認できました。
また、エラーが発生した場合にはトランザクション分離レベル・トランザクションアクセスレベルの変更は次のSQL文にも適用されます。
mysql> set transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql> set transaction read only; Query OK, 0 rows affected (0.00 sec) mysql> delete from testdb.user where id = 1; ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction. mysql> select * from testdb.user; +------+--------+ | id | name | +------+--------+ | 1 | alice | | 2 | bob | | 3 | cordie | +------+--------+ 3 rows in set (0.01 sec) mysql> select * from testdb.user; +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ 2 rows in set (0.00 sec)
