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