- 投稿日:2020-01-13T16:38:29+09:00
トランザクションのネストの使い方まとめた(初心者向け)
トランザクションのネストについてまとめてみました
どう記述したらネストができるの?
ロールバックした時の挙動は?
などなどまとめてみました
自分がよく使うMySQLとRails(ActiveRecord)について記載します。他のDBやフレームワークでは多分話が変わりますのでご注意ください前提
ネストしたトランザクションの挙動
ネストしたトランザクションって、正確な挙動がこうあるべきという決まりがあるのかどうかは筆者はよく知りません
ここでは、以下のような挙動を満たすことを目的にします
- トランザクションの内部に、もう一つトランザクションを貼る
- 内側のトランザクションがロールバックした場合、外側のトランザクションには影響を与えない
- 外側のトランザクションがロールバックした場合、内側のトランザクションもロールバックする
- 内側だけコミットされてしまうと、外側のトランザクションから見ると一貫性が破綻することになるため
- つまり「内側だけコミットする」はナシ。「内側だけロールバックする」はアリ
テストデータ
この記事では、以下のような
users
テーブルを使用して実験していますmysql> show create table users; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+MySQL におけるトランザクションのネスト
BEGINを2回書く(ダメな方法)
トランザクションの開始は
BEGIN
です。
じゃあBEGIN
の中でもう一回BEGIN
を書いてみたらどうなる?正常系
まずは、ネストした上で正常にコミットさせてみます
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); BEGIN; INSERT INTO users VALUES(2, 'in-nest'); COMMIT; INSERT INTO users VALUES(3, 'after-nest'); COMMIT; select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+(みやすいようにインデントしてますが本来は不要です)
問題ないようです
(※が、実は問題があります。後述)ネストの中でロールバックする
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); BEGIN; INSERT INTO users VALUES(2, 'in-nest'); ROLLBACK; # <===ココ INSERT INTO users VALUES(3, 'after-nest'); COMMIT; select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 3 | after-nest | +----+-------------+ネスト内の INSERT 文だけロールバックされています
どうやら正しく動いているようです
(※が、 これも実は問題あり なのです。後述)ネストの後でロールバックする
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); BEGIN; INSERT INTO users VALUES(2, 'in-nest'); COMMIT; INSERT INTO users VALUES(3, 'after-nest'); ROLLBACK; # <=== ココ select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+おかしい!ロールバックされてない
外側のトランザクションでロールバックが起きたわけですから、内側(in-nest
)も含めて全部ロールバックされていないとおかしいわけですBEGIN はネストできない
MySQL では、トランザクションはネストすることができないのです
実は、2度目のBEGIN
が実行された時、現在のトランザクションがコミットされ、新しいトランザクションが開始しているんです。このことは公式ドキュメントにも書いてあります。
https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.htmlトランザクションをネストすることはできません。これは、START TRANSACTION ステートメントまたはそのシノニムのいずれかを発行するときに、現在のすべてのトランザクションに対して実行される暗黙的なコミットの結果です。
つまり、2度目の
BEGIN
の時点で、before-nest
のインサート文がコミットされます。ネストのCOMMIT;
の時点でin-nest
もコミットされますよね。そしてafter-nest
は、トランザクション外で実行されているわけです。だからその後ROLLBACK;
が実行されようがロールバックされません。
COMMIT;
ROLLBACK;
って、トランザクション貼ってない時に実行しても特にエラーとか起こさないんですねえ。ということから、トランザクションネストしたい時に
BEGIN
は使っちゃダメということがわかりましたSAVEPOINT を使う(正しい方法)
というわけで、こういう時は
SAVEPOINT
命令を使います。
外側のトランザクションはBEGIN
のままでよく、内側のトランザクションはSAVEPOINT
と書きます。書き方は以下のように対応しています
BEGIN文 SAVEPOINT文 BEGIN SAVEPOINT hoge COMMIT RELEASE SAVEPOINT hoge ROLLBACK ROLLBACK TO SAVEPOINT hoge SAVEPOINT はいくつも同時に貼ることができるため、必ず命令文の後に名前を指定する形になります
正常系
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); SAVEPOINT nest; # <===ココ INSERT INTO users VALUES(2, 'in-nest'); RELEASE SAVEPOINT nest; # <===ココ INSERT INTO users VALUES(3, 'after-nest'); COMMIT; select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+ちゃんとインサートされていますね
ネストの中でロールバックする
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); SAVEPOINT nest; INSERT INTO users VALUES(2, 'in-nest'); ROLLBACK TO SAVEPOINT nest; # <===ココ INSERT INTO users VALUES(3, 'after-nest'); COMMIT; select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 3 | after-nest | +----+-------------+内側だけロールバックしていますね
ネストのあとでロールバックする
BEGIN; INSERT INTO users VALUES(1, 'before-nest'); SAVEPOINT nest; INSERT INTO users VALUES(2, 'in-nest'); RELEASE SAVEPOINT nest; INSERT INTO users VALUES(3, 'after-nest'); ROLLBACK; # <===ココ select * from users; Empty set (0.00 sec)全部ロールバックしています!
SAVEPOINT
を使うと、ネストトランザクションが正しく動作することが確認できました。ActiveRecord におけるトランザクションのネスト
さて今度は ActiveRecord でトランザクションを記述したらどういうクエリが発行されるかの確認です
DBは引き続きMySQLです何も考えず
.transaction
してみる(ダメな方法)正常系
トランザクションをネストさせたコードを書きます
それによって実行されたクエリとDBの中身を続けて書いています。User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction do User.create!(id: 2, name: 'in-nest') end User.create!(id: 3, name: 'after-nest') endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') COMMIT +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+こうなりました。
クエリを見て分かる通り、 内側のUser.transaction
は何のクエリも発行していないということがわかりましたこれは当然、ネストしたトランザクションを使うという目的は満たしていませんね。
ネストの中でロールバックした場合
ロールバックした時の挙動もチェックしておきましょう
トランザクションを明示的にロールバックさせるためには、 ActiveRecord::Rollback 例外を投げます
どんな例外を投げてもロールバックされるでしょ?と思われる方もいるかもしれませんが、ちょっと違うんです(後述)User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction do User.create!(id: 2, name: 'in-nest') raise ActiveRecord::Rollback # <===ココ end User.create!(id: 3, name: 'after-nest') endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') COMMIT mysql> select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+ロールバックされないという不思議な結果になりました。
理由は後述しますネストの後でロールバックした場合
User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction do User.create!(id: 2, name: 'in-nest') end User.create!(id: 3, name: 'after-nest') raise ActiveRecord::Rollback # <===ココ endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') ROLLBACK mysql> select * from users; Empty set (0.03 sec)全部ロールバックされます。
まあ、これはわかりますよね。トランザクション1つしかないんだし…どうやら、 ActiveRecoed は普通にtransactionメソッドをネストしてもうまくいかないようです
クエリは発行されないし、ロールバックもしてくれなかったりと挙動もヘンです
どうしたらいいのでしょうか(答えはこの後すぐ)オプション
requires_new
transactionメソッドには、
requires_new
というオプションがあります。
これを指定すると、明示的に新しいトランザクションを貼ることができるのです。
やってみましょう正常系
User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction(requires_new: true) do # <===ココ User.create!(id: 2, name: 'in-nest') end User.create!(id: 3, name: 'after-nest') endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') RELEASE SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') COMMIT mysql> select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 2 | in-nest | | 3 | after-nest | +----+-------------+ 3 rows in set (0.00 sec)
requires_new: true
を指定すると、SAVEPOINT
クエリが発行されましたね! (自動でactive_record_1
という名前がつけられています)
トランザクションブロックが終了した時も、ちゃんとRELEASE SAVEPOINT
が発行されています
正しく動いていますねネストの中でロールバックした場合
User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction(requires_new: true) do User.create!(id: 2, name: 'in-nest') raise ActiveRecord::Rollback # <===ココ end User.create!(id: 3, name: 'after-nest') endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') ROLLBACK TO SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') COMMIT mysql> select * from users; +----+-------------+ | id | name | +----+-------------+ | 1 | before-nest | | 3 | after-nest | +----+-------------+ 2 rows in set (0.00 sec)内側のトランザクションだけロールバックしています!
いい感じですねネストの後でロールバックする場合
User.transaction do User.create!(id: 1, name: 'before-nest') User.transaction(requires_new: true) do User.create!(id: 2, name: 'in-nest') end User.create!(id: 3, name: 'after-nest') raise ActiveRecord::Rollback # <=== ココ endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') RELEASE SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (3, 'after-nest') ROLLBACK mysql> select * from users; Empty set (0.01 sec)全部ロールバックされました!
いいですね
ちゃんとネストしたトランザクションとしての挙動をしてくれましたActiveRecord::Rollback 以外のエラーが起きた時
transaction の中で例外がおきた時、トランザクションがロールバックされることはご存知と思いますが
ActiveRecord::Rollback
エラーだけは、ちょっと挙動が違いますUser.transaction do User.create!(id: 1, name: 'before-nest') User.transaction(requires_new: true) do User.create!(id: 2, name: 'in-nest') raise # <=== ActiveRecord::Rollback でない例外が起きた end User.create!(id: 3, name: 'after-nest') endBEGIN INSERT INTO `users` (`id`, `name`) VALUES (1, 'before-nest') SAVEPOINT active_record_1 INSERT INTO `users` (`id`, `name`) VALUES (2, 'in-nest') ROLLBACK TO SAVEPOINT active_record_1 ROLLBACK # <=== ここもロールバックしている mysql> select * from users; Empty set (0.00 sec)外側のトランザクションまでロールバックしています
先ほどActiveRecord::Rollback
例外を投げた時は、内側のトランザクションだけがロールバックし、外側のトランザクションは何事もなかったかのように続行されていました
今回は、例外が内側のトランザクションを突き抜けて、外側のトランザクションまで例外によるロールバックを発生させちゃったんですね。(なんなら外側のトランザクションも突き抜けているので、どこかで捕捉しないとプログラムが止まります)
実は ActiveRecord の transaction メソッドは、ActiveRecord::Rollback
例外だけを静かに飲み込んで何事もなかったかのように振舞っているのです (https://github.com/rails/rails/blob/v6.0.2.1/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb#L283)これはトランザクションがネストしているかどうかとは関係なく、覚えておきたい点ですね。
さっきの答え合わせ
.transaction
を2回貼った時、内側でraise ActiveRecord::Rollback
を投げた時、ロールバックしなかったという不思議現象がありましたね
あれは、例外が内側のtransactionに捕捉され、外側のトランザクションからすると何事も起こっていないように見えていたからでした。
あのコードでは、内側の transaction メソッドは実際にはBEGIN
やSAVEPOINT
クエリを発行していないため、対応するROLLBACK
ROLLBACK TO SAVEPOINT
を発行しなかったということのようです
だったら例外も捕捉しないで上にそのまま raise してよ、という気もしますね(この挙動の方が辻褄があうと言うことなんでしょうか…?)貼ってる?トランザクション
参考までに
ActiveRecord には、今トランザクションを貼っているかどうかを知るメソッドがありますUser.connection.open_transactionsこのように書くと、現在貼っているトランザクション数が返ってきます
User.connection.open_transactions # => 0 User.transaction do User.connection.open_transactions # => 1 end User.transaction do User.transaction(requires_new: true) do User.connection.open_transactions # => 2 end end User.transaction do User.transaction do User.connection.open_transactions # => この場合は1 end endまとめ
- MySQL
- ネストしたいなら
SAVEPOINT
命令を使おうBEGIN
を2度書くと思わぬことが起きるぞ- 厳密にはトランザクションのネストはできないよ
- Rails(ActiveRecord)
- ネストしたトランザクションを貼るときは、オプション
requires_new: true
を指定しよう
- 指定しないと予想外の挙動になるよ
- 内側のトランザクションだけをロールバックする場合は、
raise ActiveRecord::Rollback
を投げよう
- それ以外の例外だと外側も一緒にロールバックしちゃうぞ
まとめのまとめ
可能な限り、トランザクションのネストってしない方がいいよ
- 投稿日:2020-01-13T00:18:45+09:00
VPS/ubuntu上にインストールしたmysqlに外部からアクセス
概要
conohaVPS(ubuntu)上にインストールしたMysqlに外部(Macのターミナル)からアクセスをする
備忘録としてまとめています環境
ローカル
MacBookPro(macOS 10.15.1)
VPS
conoha VPS(ubuntu18.04)
Mysql 5.7.28VPSにmysqlをインストール
ubuntu上にmysqlをインストールします
VPS$ sudo apt update $ sudo apt install mysql-server外部接続用ユーザーの作成
mysqlのインストールが完了したら外部接続用のユーザーを作成します。
まず初めにmysqlにrootユーザーでログイン
パスワードも聞かれますVPS$ mysql -u root -prootログインができたら外部接続用ユーザーを作成します。
今回はユーザー名:outer/ホスト名:XXX.XXX.XXX.XXX/パスワード:passwdという名前にします。適宜変えてください
(ホスト名のIPは接続側、今回ならmac側のIPです。VPSのIPではない)
また、権限は全OKな感じにしているのでこちらも適宜変えてください。VPS/mysql# ユーザーの作成 CREATE USER outer@XXX.XXX.XXX.XXX IDENTIFIED BY 'passwd'; # 作ったユーザーの権限を設定 GRANT ALL PRIVILEGES ON *.* to outer@XXX.XXX.XXX.XXX IDENTIFIED BY 'passwd' WITH GRANT OPTION; # 必要であれば下記コマンドで正しく設定されているか確認 SELECT user, host FROM mysql.user;ここまでできたら一度"exit"と叩いてmysqlからログアウトします。
mysqlの再起動
下記コマンドでMysqlを再起動します。
VPS$ sudo systemctl restart mysqlまた、(必要か分かりませんが)mysqlの下記configを変更する必要があるそうです。
VPS$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf #下記をコメントアウトする。 bind-address = 127.0.0.1ubuntuファイアウォールの設定
mysqlのデフォルトのポートは3306なのでファイアウォールも設定してあげます。
VPS# ポート3306を解放 sudo ufw allow 3306 # ufwの再読み込み ufw reload # 必要であれば下記コマンドで確認 ufw status verbosemacターミナルから接続
最後に外部からアクセスできるか確認します。
ここでのYYY.YYY.YYY.YYYはVPSのIPアドレスです。mac/ターミナル$ mysql -h YYY.YYY.YYY.YYY -u outer -pこれで接続ができました。