# 2021/04/25 メンター相談

###### tags: `メンター相談`,`4月`

## 「ISOLATION LEVEL」と「行/テーブルロック」「共有/占有ロック」の関係はどのようになっているのでしょうか？

- ISOLATION LEVELによって方針が変わるので、これは基本方針と思っていただければ
- 共有と占有ロックは併用できる
- 松原さんがやったことあるのはテーブルロックの占有ロックのみ
- 組み合わせることはできる理解
- オプティマイザがレベルやクエリに合わせて、どう判断するかが変わるが、どう変わるかはわからなかった
- 一貫性読み取り
- スナップショットから読み取る機能のこと
- 更新する時に他の人が同時更新しないようにするときなどは、占有ロックを取る必要がある
- 実務で意識すること
- MySQLでいうインテンションロックくらい
- ロックの種類をさらに分類すると
- 開発者が意図的にできるロック
- SELECT FOR SHARE
- 共有ロック
- 松原さんはMySQLで書いたことはあまりない
- SELECT FOR UPDATE
- 占有ロック
- こちらの方が利用頻度は高い
- 毎回これを使うかどうかは、機能次第。
- 例えばユーザーのプロフィール編集とかでは、上書きされてもいいか、ユーザーのプロフィール更新は本人しかできないので、使わない判断をする
- 為替では、レート変動が関係するので、ロック書ける
- 楽観ロックを使う場合は、これは使わない
- バージョン更新noのみ控えておくとかになる
- 開発者が意図的に設定できないロック
- ギャップロック
- ネクストキーロック
- isolation レベルを今まで松原さんが変えた経験はない
- 全部serializableにしておいて、徐々に下げていったことはあるが、結構アクセスがきている時点でそれをやるのは無謀だったかも。。
- PostgresSQLでも変えたことはない
- ここを変えるのは正直怖い
- アプリケーション側で楽観ロックの方で制御していることが多い
- 詳しくないと変えるのは結構怖いので、変えてない
- セッション単位で決めることの方が多いと思われる
- 存在しないレコードのロックを取得しようとすると大きなギャップロックが発生する
- リクルートの時に起きたことがある
- 範囲選択をしてデータ更新する時に、他のデータが更新しないように、それいこうのデータが修正できないようにロックするが、その時の存在しないレコードのロックを撮ろうとすると発生する
- デッドロックの原因になる
- 発生してそうであれば、更新する時にはあるかレコードがあるかどうかを確認した方が良い
- もしくは、存在しないレコードに対してupdate文を書ける場合は、その時だけギャップロックをとらないようにisolation levelを下げるとかはできるかも
- バッチによる大量のinsertをしている時は、デッドロックが置きがちなイメージ
- バッチは実行時間をずらして実行するもの。idが低いものから取っていくクエリと、idが高いものから取っていくクエリのバッチが並行して走っていると中間でデッドロックが発生する可能性がある
- idは同じ順番で取るようにして、ロック発生しないように注意する
- prismaは楽観ロックに寄せようとしている
- トランザクションやデッドロックは、余程大きなサービスでなければ発生しないので、楽観ロックの方がパフォーマンス的には有利
- 悲観ロックは、待ち時間が発生するため
- 大体のサービス（ローンチ直後のサービスなど）は楽観ロックで十分に思う
- ロックを意識して改善した事例
- ユーザーからの問い合わせで、処理が失敗した時があった
- スロークエリにはログがなかったが、innoDBを見たらロックが原因だった
- ロック待ちはスロークエリログには出力されないので、スロークエリログでは気づけなかった
- PostgreSQLとMySQLはデフォルトのisolatiton levelが異なるので注意
- prahaでは楽観ロックがメイン
- DDD的な理由もあり、ロジックが実行している間にずっと待たせるよりは楽観ロックの方が良いかと思われる
- prismaは開発者がカラムにバージョンとかを付け加えておく必要がある
- MySQLでデッドロック発生すると、自動ロールバックするらしいが、実際はどうか？
- アプリケーション側で、ロールバックするように制御しているので、そのログを出力して検知している
- index貼られてないカラムに対してfor updateで占有ロックすると、テーブルロックになってしまっていて、デッドロックが発生してしまっていた。こういう時はどうするのか（中野さん）
- 経験はない。。
- MySQLは全部ろっくするのは、indexレコードに対してロックする仕組みしかない
- PKは、付属情報を持っていないので、テーブルロックをかけにいくしかないのではないかと思われる
- 困ったらテーブルロックになっているかも

## インデックスでOptimizerが統計情報をどのように使用しているのか知りたい

- 結論確実なことはわからなかった
- 多分プロファイラを実行したところ、インデックスが多ければ多いほど、実行計画を作る時に考慮するものが増えるからではないかと思われる
- オプティマイザトレースをオン（information_schema）にして、クエリを実行して、オプティマイザトレースを実行すると、どういう経緯でクエリが決定されたのかがわかる
- potential_range_indexes
- considered_execution_plans
- 使えるインデックス を作れば作るほど、ここが時間がかかるので、これのせいで遅くなるのではないかと予想している
- 選択肢が増えれば増えるほど、時間も増えるんではないか
- statisticsは結構ブレるので、確実なことはわからないが。。

## pt-query-digestはどういったパラメータで使用することが多いですか

- ランキングを見ていた
- 合計実行時間が長いクエリが出てくる
- 頻度が高いからランキングの上に出てくるのか
- maxやaverageが長い場合は直すが、実行頻度と照らし合わせて治すかどうか検討する

## プラハさんではログ監視にDatadog以外にどのようなツールを使用しているのでしょうか

- プロジェクトごとだが、松原さんが使ったのはRDSを使っている場合は、performace insights
- フロントは、SentryやLogRocket
- https://sentry.io/welcome/
- https://logrocket.com/
- リクルートの時は、elasticsearchに入れて、kibanaで可視化していた
- kibanaを使っていたために、クエリパラメータにセンシティブなデータを出力していたのが問題になったことがあった
- クエリパラメータにそういう情報は入れない方が良い
- n+1クエリが発生している時は、クエリ自体は遅くない場合があるので、ニューレリックを使っていた
- https://newrelic.com/jp/products/logs

## MySQLとPostgreSQLのトランザクションの違いで注意すべきことがありましたら教えていただきたいです。

- ファントムリードをどう防ぐかの仕組みが違うらしい
- 松原さんが実務で意識したことはないが

## 実務でトランザクションのレベルが原因で発生した問題や分離レベルを調整して解決した問題がもしありましたら、教えていただきたいです。

- リクルートで直面したことはあるが、詳細はわからん。。

## その他

### インデックスを張っていないカラムに条件指定を行い、レコードロックをかけるとテーブルロックになってしまう

- インデックスが張られていないカラムを条件で指定する
- その場合プライマリーキーのインデックスでレコードロックを取得しようとする
- しかしインデックスからは、ほかのカラムに指定された検索条件で絞り込むことができない
- そこでインデックスのすべての範囲をロックしている可能性はある

## 参考リンク

### （ロック全般の注意点について）
- [ロック順番を統一しないと簡単にデッドロックが発生する仕組み（特にソシャゲ）](https://www.slideshare.net/sairoutine/ss-62485460)
- こちらもソシャゲを設計する上で考慮しなければいけない点
- 楽観ロックを使うケースとして、どうしてもトランザクションを跨いだ整合性を担保したい時が挙げられている
- [そもそもupdateを使う状況をできるだけ減らしたい、という話](https://www.slideshare.net/matsunobu/ss-6584540)
- [以前「Userテーブルを状態ごとに分割した方が良いよ「という話をした気がしますが、そちらの設計について詳細に解説している記事](https://soudai.hatenablog.com/entry/2018/05/01/204442)
- [かつ、そうした際に発生する可能性がある外部キーロックについて解説している記事](https://www.yuulinux.tokyo/10592/)
- [1つのテーブルだったとしてもバッチ処理でレコードに対してascとdesc（逆順）で同時に更新するといつもたやすくデッドロックする話](https://blog.kamipo.net/entry/2020/12/15/213359)
- [こちらはメルカリの事例共有](https://engineering.mercari.com/blog/entry/2017-12-18-deadlock/)
- [こちらはトリガーを使うことが予測しづらいデッドロックを引き起こしてしまう事例](https://tech-blog.rakus.co.jp/entry/20200929/postgresql)
### （MySQLのロックについて）
- [基本的にインデックス単位である話や、ネクストキーロック、ギャップロックの仕組みについて最もわかりやすく解説されていた](https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb#2-mysql-%E3%81%AE%E7%89%B9%E6%AE%8A%E3%81%AA%E3%83%AD%E3%83%83%E3%82%AF)
- [mvcc（一貫性非ロックread）について説明されている記事](http://nippondanji.blogspot.com/2010/09/innodb.html)
- 行のバージョントランザクションごとに作成しているためロストアップデートなどが発生してしまう点を説明
- [Postgreとのトランザクション処理の違いを軽く説明している記事](https://eh-career.com/engineerhub/entry/2017/09/05/110000#%E6%AF%94%E8%BC%83%E3%83%9D%E3%82%A4%E3%83%B3%E3%83%88%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%87%A6%E7%90%86%E3%81%AE%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)
### （その他）
- isolation levelを上げるとトランザクションの失敗確率が上がる
- [リトライすることで解決することが多いので、ORMに自動リトライ機能を設定できるようにして欲しい、というissue](https://github.com/prisma/prisma/issues/1986)
- [prismaが悲観ロックではなく楽観ロックを推奨するような設計に舵を切っている話](https://github.com/prisma/prisma/issues/1918)
- [pt-query-digestのランキングをチューニングに使用している例](https://www.slideshare.net/techblogyahoo/mysql-58540246)

### （監視周りで紹介したツール）
- [sentry](https://sentry.io/welcome/)
- [logrocket](https://logrocket.com/)
- [newrelic](https://newrelic.com/)
- [rdsのperformance insight](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.html)