DBのロックについてあまり意識したことがない人に向けた、実は覚えておきたいロックについての知識 / 技育CAMPアカデミア2023

2023年4月5日(水) サポーターズ主催「技育CAMPアカデミア」で開催されたCARTA HOLDINGSセッションの発表資料です。

・登壇者
株式会社CARTA HOLDINGS
fluctエンジニア
なっかー（@konsent_nakka）

Transcript

1. DBのロックについて あまり意識したことがない人に向けた、 実は覚えておきたいロックについての知識 2023/04/05 株式会社CARTA HOLDINGS（旧VOYAGE GROUP, CCI） fluct 開発本部 開発オペレーションチーム なっかー
2. みなさん、ロックって知ってますか？
3. トランザクションは使ったことあるけど どのロックが使われているか 知らない人も多いのではないでしょうか？
4. 今日から ロックを意識して使い分けましょう
5. 自己紹介 @konsent_nakka なっかー 普段は 配信システムを支える会計システム開発 をやってます https://techblog.cartaholdings.co.jp/entry/cto-interview9 株式会社CARTA HOLDINGS (旧VOYAGE GROUP) fluct 開発本部 20卒エンジニア
6. 経営統合してできた会社です。 電通100%子会社の サイバー・コミュニケーションズ（CCI）が 1 東証一部上場企業である VOYAGE GROUP と アドプラットフォーム事業を中心に幅広く事業展開。 テクノロジーや事業開発力が強み。 デジタル広告のメディアレップ事業を中心に事業展開。 強い顧客基盤が特徴。 【 VOYAGE GROUP 】 【 CCI 】 CARTA HOLDINGSとは ❶ 約53% 電 通 （株） VOYAGE GROUP 約 47% 既存株主 CARTA HOLDINGSは、2019年1月に
7. CARTA HOLDINGSとは ❷ 多くの事業・サービスを運営しています（下記は一部を抜粋）
9. https://engineering.cartaholdings.co.jp/ @carta_engineers フォロー登録！ CARTA TECH BLOG エンジニアの活動情報が満載！ @CARTAEngineering チャンネル登録・高評価
10. 流れ 0. 留意事項 1. そもそもロックとは？どういう時に使うの？ 2. ロックのメリット/デメリット 3. 排他ロック、共有ロック 4. トランザクション分離レベル 5. ロック範囲の種類 6. デッドロックの例 7. トランザクションをうまく使う方法
11. 0. 留意事項 - MySQL(InnoDB)5.7での話です - MyISAM(MySQLの別エンジン)だったり他のDBは考慮しません - 厳密な定義、挙動を知りたい場合は自身で再度調べてください - ロック初心者/初級者向けに平易な表現を優先して書いていますのでご了承くださ い
12. 1. そもそもロックとは？どういう時に使うの？ ロックとは、複数のプログラムが同時に同じリソースにアクセスしようとするときに、 競合/不整合を避けるための仕組みです。 DBにおいては、ロックはトランザクションがデータを更新する場合に、 他のトランザクションが同じデータを編集または参照できないようにします。
13. 1. そもそもロックとは？どういう時に使うの？ 例えば Aさんが銀行口座から1000円引き出し、 同時にBさんが同じ口座から1000円入金する場合、 Aさんの引き出し完了までBさんは口座にアクセスできないようにする ことができます。 これをロックと言います。
14. 1. そもそもロックとは？どういう時に使うの？ ①残高確認 1000円 1000円 0円 ③残高1000円 - 引き出す 1000円 ②残高確認 1000円 2000円 ④残高1000円 +入金 1000円 システムが残高を確認したタイミングによって ③が反映されなくなる
15. 1. そもそもロックとは？どういう時に使うの？ ①残高確認 1000円 1000円 0円 ③残高1000円 - 引き出す 1000円 ②残高確認 待機 0円 ④残高0円 +入金 1000円 ロックされている 残高確認 0円 1000円
16. 1. そもそもロックとは？どういう時に使うの？ SQLで表現すると SELECTで取得したデータを元に変更する良くあるパターン
17. 1. そもそもロックとは？どういう時に使うの？ ロック以前にDBの設計が良くないパターンは多い そもそも入出金システムを表現するなら全てINSERTする方が良いかも
18. 2.ロックのメリット/デメリット ロックのメリット - 同時並列での意図しないデータアクセスを防ぐことによって整合性を保てる デメリット - 並列に動くプロセスをロック待ちさせてしまうと実行時間が伸びる - 意図しないロックが発生しデッドロックする
19. ロック待ち ロック待ちは、複数のトランザクションが同じリソースにアクセスしようとする場合、先に ロックを取得したトランザクションがロックを解放するまで待たなければならない状態のこと を言います。 これにより、トランザクションの実行時間が遅くなる可能性があります。 2.ロックのメリット/デメリット
20. デッドロック デッドロックは、2つ以上のトランザクションが、お互いに相手のロックを解放するまで待ち 合わせ状態になってしまう現象です。 この場合、トランザクションは永遠に終了しなくなります。 基本的なDBであればデッドロック状態になった場合は後からロックを取得したトランザク ションをエラーで終了させ、先に動いていたプロセスを先に完了させます。 2.ロックのメリット/デメリット
21. 3. 排他ロック、共有ロック まずロックには排他ロック(X)と共有ロック(S)があります。 データを読み取るとき(SELECT)は共有ロックを、 データを更新するとき(UPDATE/DELETE/INSERT)は排他ロックを取得して他のSQL実行を一部 制限してくれます。
22. 共有ロック 排他ロック 共有ロック OK NG 排他ロック NG NG 排他ロック: 基本的には他のSQL実行をブロックするロック 共有ロック: 排他ロックを取得するようなSQL実行をブロックするが、共有ロックを取得する SQL実行までは許可するロック
23. 3. 排他ロック、共有ロック SELECTのロックレベルを変更する SELECT文にはロック読み取りという追加構文があります。 • DBから特定のデータを取得してアプリケーションで処理をした後にデータを更新する時 に、他のプロセスから邪魔されずに処理を実行したい場合に利用します。 そしてその構文には例によって”共有ロック用”と”排他ロック用”の2種類があります。 これらのロック読み取りはロックを取得するのでロック読み取りSELECT同士でも通常のロッ ク関係どおりブロックが発生します。
24. 3. 排他ロック、共有ロック 共有ロック: LOCK IN SHARE MODE このロック読み取りを使用するとトランザクションが終了されるまで共有ロックを継続して取 得し続けます。 例えば他のプロセスから対象のデータを取得されるのはいいが更新削除はされたくない時に使 用します。
25. 3. 排他ロック、共有ロック 排他ロック: FOR UPDATE このロック読み取りを使用するとトランザクションが終了されるまで排他ロックを継続して取 得し続けます。 例えばDBから対象のデータを取得してアプリケーションで処理をした後にその値を使用して 対象データを更新したい場合などに使用します。
26. 4.トランザクション分離レベル トランザクション分離レベルとは、 データベースが複数のトランザクションを同時に実行するとき、 他トランザクションへの影響度、どの程度の競合を許容するかを決定する設定です。 DB関連用語で頻出するいわゆるACID特性の Isolation = 分離性 に関係する部分です。
27. 4.トランザクション分離レベル InnoDBでは下記の4つがあります。 • READ UNCOMMITTED • READ COMMITTED • REPEATABLE READ • SERIALIZABLE
28. ダーティーリード 非リピータブルリード ファントムリード READ UNCOMMITTED 発生 発生 発生 READ COMMITTED / 発生 発生 REPEATABLE READ / / 発生(限定的に発生 しない) SERIALIZABLE / / / - ダーティーリード: 別トランザクションでコミットしていない内容を読んでしまう - 非リピータブルリード: 別トランザクションで更新された値が即座に反映されてしまう - ファントムリード: 別トランザクションで追加/削除された値が即座に反映されてしまう
29. 4.トランザクション分離レベル READ UNCOMMITTED 分離性が一切ない分離レベルです。 読み取りもせず書き込みしかしないログ系のアプリケーション、データの更新頻度が低く整合 性を捨ててでも高速化する必要のある場合には利用を検討します。 READ COMMITTED コミットをしてない部分の分離性は保たれますが、コミットした場合は別トランザクションに 反映されてしまいます。 そのため整合性を気にする場合は分離レベルを上げた方が良いでしょう。
30. 4.トランザクション分離レベル REPEATABLE READ 更新が別トランザクションでコミットされても分離性を保てる分離レベルです。 InnoDBだとルールが少し特殊で Consistent reads (MVCC) という技術が使われて、ファントム リードが比較的発生しづらいように設計されています。 consistent readsはそれぞれのトランザクションにおいて最初の読み取り(SELECT)が発生した タイミングでスナップショットを作成し、別のプロセスからデータ追加された場合でもスナッ プショットを読み取るために追加されたデータを読むことありません。
31. 4.トランザクション分離レベル REPEATABLE READ consistent readsにおいて気をつけるポイントは２つあります。 まずスナップショットを作成するタイミングは最初の読み取り時点なので、別プロセスから既 にデータ追加されている場合はそれも含めてスナップショットとしてしまいます。 直感的にはトランザクションを開始したタイミングだと思ってしまいそうなので間違えないよ うに気をつける必要があります。
32. 4.トランザクション分離レベル プロセスA プロセスB 時間
33. 4.トランザクション分離レベル REPEATABLE READ consistent readsにおいてのもうひとつ気をつけるポイントは、ロック読み取りを使用すると ファントムリードが発生してしまうことです。 - ファントムリード: 別トランザクションで追加/削除された値が即座に反映されてしまう
34. 4.トランザクション分離レベル プロセスA プロセスB 時間
35. 4.トランザクション分離レベル SERIALIZABLE このレベルはREPEATABLE READと似ていますがSELECTが常にLOCK IN SHARE MODEと同一 になるため厳密性が増します。 ただしautocommitが有効か無効かで少し挙動が違うので詳しくは調べてください。
36. 4.トランザクション分離レベル InnoDBではデフォルトで REPEATABLE READ になっています。 特殊な要件がない限りデフォルトの REPEATABLE READ で問題ありません。 MySQLにおいて個人的に覚えておいた方が良いトランザクション分離レベルは2つです。 • REPEATABLE READ • READ COMMITTED 分離レベルはアプリケーション特性によって検討しましょう。
37. 5.ロック範囲の種類 ここまで排他ロック、共有ロックなどの基本的に意識すべきロックの話は出てきましたがこれ 以上にロックには細かい種類が存在します。 ロックの影響範囲だったり、何をもとにロックするかなどです。 あまり詳細に書きすぎても仕方ないので大雑把に列挙するような形で3つ紹介します。
38. 5.ロック範囲の種類 1. レコードロック 文字通りレコード(行)をロックするものです。 例えばレコードが一意に決まるようなprimary key検索での更新なんかは内部でこれが使われて います。 他にも複合uniqueに対しての検索でもレコードロックが使われたりします。 後述しますが罠もあります。 1 2 3 4 5 6 7
39. 5.ロック範囲の種類 2. ギャップロック ギャップロックとは言葉では分かりづらいですが範囲ロックと言い換えてもいいでしょう。 WHERE id <= 5とかした時にidが 5以上 のものは範囲的にロックするといったところです。 INSERTする時に id が 1 のものしか入ってないとして id が 5 のものを入れる時は 1 ~ 5 の範囲 をギャップロックされたりします。 ギャップが被ることによって想定していないブロックが発生することも多くあるので認識はし ておきましょう。 1 2 3 4 5 6 7
40. 5.ロック範囲の種類 3. ネクストキーロック これはかなり分かりづらく、実際同じSQLでもレコードロックとギャップロックが使われると きは場合によりけりです。 なのでそこまで細かく意識する必要があるタイミングは少ないでしょう。 簡単に言うとギャップロックの範囲 + 境界値部分の次のキーを余分にロック取得するもので す。 この辺りのデータをロックしたいってことはその前後もデータが追加されたりするんじゃない か？といった意図でロックされていると思っていいです。 1 2 3 4 5 6 7
41. 5.ロック範囲の種類 4. 空振りロック(ギャップロック または ネクストキーロック) レコードロックの時に罠があるという話をしました。 例えば以下のように、存在しないデータに対してDELETE SQLを発行します。 こうなるとMySQLはギャップロック (id=100などもロック) を取得してしまいます。 なので基本的にはDELETEやUPDATEを発行する場合は事前に対象の存在確認をしましょう。 1 2 3 98 99 100
42. 6.デッドロックの例 共有ロック同士のデッドロックについて 共有ロックの上から、共有ロックは取得できますが専有ロックを取得することはできません。 一つのプロセスで実行されるだけなら順番に実行するだけなので問題ありませんが、並列にプ ロセスから実行される場合は問題があります。 解決策としては専有ロックを取得するようなSQLを実行するならSELECT ~ FOR UPDATEを使 用することです。
43. DELETE & INSERT ロジックを書いた時に発生するデッドロック (終わってから考えてみてね) summary — id : PK val month details — id : PK summary_id : FK 6.デッドロックの例
44. プロセスA プロセスB 時間 6.デッドロックの例
45. 7.トランザクションをうまく使う方法 デッドロックの対処方法 デッドロックに対処するにはいくつかの方法があるので難易度順に並べてみます。 - そのまま失敗させる - リトライ制御をする - そもそもロックが被りづらい処理にする - トランザクション粒度を小さくする - ロック時間が短くなるようにする - クエリを見直して適切なロックを取得するようにする - トランザクション分離レベルを変更する
46. 7.トランザクションをうまく使う方法 デッドロックの対処方法: リトライ制御 デッドロックの対処法として try~catch を使ってリトライ制御を書く時に全てのエラーを握り つぶすことは絶対にやめましょう。 言語によりますがデッドロックによるエラーかどうかは判断が可能です。 しっかりとデッドロックエラーの場合だけを拾って、それ以外はそのまま例外としてthrowし なおしましょう。
47. 7.トランザクションをうまく使う方法 トランザクションをうまく使う方法 - サービスにあったトランザクション分離レベルを選択する - まとまったDB操作は同一トランザクションで処理する - 直前とデータが変わってて不整合が起きる - ロック粒度をできる範囲で小さくする - それ同じトランザクションに入れる必要ある？ - ロック時間、範囲(レコード/ギャップ/ネクストキー ロック)が小さくなるようにする - 重いクエリでロック取りすぎ！不必要に範囲の広いロック取りすぎ！ - ロックのMAX待機時間を変更する - InnoDBだとロック待ちのタイムアウトがデフォルト50秒……50秒！？
48. 8.まとめ - 専有と共有がある - 分離レベルが4つあってInnoDBのデフォルトはREPEATABLE READ - SELECT ~ FOR UPDATE などのロック読み取りを上手く活用しよう - 空振りDELETE/UPDATEはするな - トランザクション粒度を小さくしよう
49. 8.まとめ ロックは凄いし大事だけど ご利用は計画的に
50. 参考・利用 利用 - SAKURA internetのアイコンセット https://knowledge.sakura.ad.jp/4724/ 参考 - 「失敗から学ぶRDBの正しい歩き方」著者: 曽根 壮大 さん - https://dev.mysql.com/doc/refman/5.6/ja/innodb-lock-modes.html - https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html - https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html - https://blog.tiqwab.com/2018/06/10/innodb-locking.html - https://zudoh.com/mysql/research-next-key-lock-and-insert-intention-gap-lock - https://dbstudy.info/files/20140907/mysql_lock_r2.pdf
51. 告知！ • 日時：8/7(月)〜8/25(金) 10:00〜18:30 • 形式：オンライン＋渋谷オフィス • 待遇：14万円支給 • 定員：30名程度 • 対象：25卒で入社可能な方 • Webの体系的な講義 と チーム開発 を 行う圧倒的成長の3週間💪🔥 ”Go言語を使って学ぶ、 価値のあるもの創りとチーム開発” もの創り実践プログラム 「Treasure」
52. 質問あれば