refirio.org

ロックによる排他制御

MySQLのプロセス確認と強制終了（デッドロック解除）の方法は、サーバメモの Command.txt の「MySQLでプロセスを確認＆強制終了」を参照 ロックについては、このファイル内の「トランザクション」についても参照（トランザクション分離レベルについては注意が必要） 以下の記事は以前に試したときのメモ FOR UPDATE による行ロックの具体例がある （この内容以外にも、トランザクション分離レベルについては注意が必要。またボタンを連打されなように非活性化するなどの処理も入れておくべき） Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 CREATE TABLE や TRUNCATE TABLE を実行した場合は、暗黙的にコミットされてロックが外れる（テンポラリテーブルの場合は外れない） …など例外的な挙動もあるようなので注意する。要勉強 デッドロックが発生した場合、「SHOW ENGINE INNODB STATUS;」で詳細を確認できる （このコマンドを使用する場合、「SHOW ENGINE INNODB STATUS\G;」とすると結果を読みやすくなる） なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2017/12/20/030013 以下、ロックの挙動を検証したときのメモ 主に以下のページを参考にしている DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000 デッドロックについては、後述の「デッドロックの具体例」も参照 またこの項目の最後に記載した「本番環境で実際にログを確認したときの内容も」も参照 ■前提 以下のテーブルを作成し、データを登録しているものとする （トランザクション分離レベルは、MySQLデフォルト設定の REPEATABLE READ としている） 以下で値の編集を確認できる 複数端末から実行しても編集できる この前提で、トランザクション・共有ロック・排他ロックについて確認する 2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する ■トランザクションの確認 ■共有ロックの確認（LOCK IN SHARE MODE） トランザクションが終了されるまで共有ロックを継続して取得し続ける 別のトランザクションからの読み取りを許可するが、書き込みは許可しない状態になる ■排他ロックの確認（FOR UPDATE） トランザクションが終了されるまで排他ロックを継続して取得し続ける 別のトランザクションからの読み取り＆書き込みの両方を許可しない状態になる ※ただしInnoDBでトランザクション分離レベルが REPEATABLE READ の場合、操作もとでコミットされると読み取りできる REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値なので、実質変更途中のデータを取得されることが無いためだと思われる ■排他ロックの確認（READ COMMITTED + FOR UPDATE） トランザクション分離レベルを READ COMMITTED にし、更新の前には FOR UPDATE で排他ロックをかける方法 予約処理の定員チェックなど、厳密性を求められる場面では基本的にこの処理が良さそう ■引き続きの勉強中メモ ・トランザクション内でロックしたら、コミットしてから次の処理が進む ロックは、定員オーバーチェックの最初だけ行うといい。同じ処理の中にfor updateが2回あると問題。1回だとデッドロックは理論的に起こらない。はず 予約者をINSERTしていくような行が増えるテーブルはロックできない ・ダミーテーブルをロック、という手法は無いか 「予約者をINSERTしていくような行が増えるテーブルはロックできない」があるので、適当なテーブルで行うべきでは無いか データが1行で「ロックしているか否か」の値を持つだけのテーブルをロックするのなら有効か ・「ダミーテーブルをロック」が有効なら、ダミーファイルを配置しておいてファイルロックをかけ、それによって排他制御を行うのは有効か サーバが複数台構成の場合は使えないので、かえってややこしくなるだけか ・ミドルウェアの設定で「デッドロックを検知した瞬間にエラーで落ちる」とかできないか 以下などでは「スレッドを特定して強制終了」となっているので、そのような設定は無さそうだが MySQLでロックを特定し、強制終了する - Qiita https://qiita.com/RyutaKojima/items/a76f4cd4c94d1989a4a5 【障害対応】MySQLでデットロックが発生した時の対応方法 - 気ままに https://unot13.hatenablog.com/entry/2018/08/07/174951 MySQLでロックを特定、強制終了してみた｜SHIFT Group 技術ブログ｜note https://note.com/shift_tech/n/n808984951f92 以下は参考になりそうなサイト 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 https://blog.tokumaru.org/2015/05/blog-post.html MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance http://d.hatena.ne.jp/LukeSilvia/20110123/p1 MySQL - InnoDBのロック関連まとめ - Qiita https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8 MySQL テーブルのロック - とみぞーノート http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E... デッドロックを回避するために - 技術ブログ | 株式会社クラウディア https://cloudear.jp/blog/?p=1335 MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary https://ichirin2501.hatenablog.com/entry/2015/12/24/164916 doc/innodb.md at master - ichirin2501/doc https://github.com/ichirin2501/doc/blob/master/innodb.md MySQLでSELECT FOR UPDATEと行ロックの挙動を検証してみた - JUST FOR FUN http://taiga.hatenadiary.com/entry/2018/02/12/170109 MySQLのトランザクション処理中にALTERするとコミットされる - ペチパーノート http://butterbull.hatenablog.com/entry/2014/06/12/150756 ソシャゲエンジニアの自分が開発に必須だなと思った知識（MySQL編） - Qiita https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb 第75回　MySQLのさまざまなタイムアウトオプションについて：MySQL道普請便り｜gihyo.jp … 技術評論社 https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075 InnoDB の行レベルロックについて解説してみる - あらびき日記 https://abicky.net/2016/11/30/082130/ MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ https://tech.mti.co.jp/entry/2017/12/27/190733 MySQL(InnoDB)の行ロック - フリエン生活 https://free-engineer.life/mysql-innodb-record-locks/ MySQL １つのテーブルでデッドロックさせる│システムガーディアン株式会社 https://sys-guard.com/post-15568/ データベースのロックの基礎からデッドロックまで https://zenn.dev/gibjapan/articles/1d8dfb7520dabc MySQL/Aurora/TiDBロック入門 - 第１回トランザクション分離レベル｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/05/mysql-lntro-locking-1-transaction-isolation/ MySQL/Aurora/TiDBロック入門 - 第２回ロックモニターの読み方【動画解説付】｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/06/mysql-intro-locking-2-lock-monitor/ MySQL/Aurora/TiDBロック入門 - 第３回ロック読取りも SELECT は止められない【解説動画付】｜技術ブログ｜北海道札幌市・宮城県仙台市のVR・ゲーム・システム開発 インフィニットループ https://www.infiniteloop.co.jp/tech-blog/2024/06/mysql-intro-locking-3-locking-read/ ■本番環境で実際にログを確認したときの内容 Laravelのログに以下が現れた 本番環境で以下の内容を確認できる - - - - - - - - - - - - - - - - - - - - > SHOW ENGINE INNODB STATUS\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2024-08-26 08:27:54 0x1532ed702700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 573526 srv_idle srv_master_thread log flush and writes: 573462 ---------- SEMAPHORES ---------- ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-08-26 08:00:27 0x153300128700 *** (1) TRANSACTION: TRANSACTION 134818679, ACTIVE 193 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 7551 lock struct(s), heap size 1122424, 3044103 row lock(s) MariaDB thread id 453694, OS thread handle 23308788729600, query id 17183524 10.0.0.176 webmaster Sending data select count(*) as aggregate from `user_property_views` where `user_accounts_id` is null and `user_identification_id` is null and `properties_property_cd` = '52-56599' and `user_property_views`.`deleted_at` is null for update *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 570 page no 16942 n bits 112 index PRIMARY of table `test`.`user_property_views` trx id 134818679 lock_mode X locks rec but not gap waiting Record lock, heap no 39 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176363; asc cc;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f800b1; asc d ;; 8: len 4; hex 64f803b7; asc d ;; 9: len 4; hex 64f803b7; asc d ;; *** CONFLICTING WITH: RECORD LOCKS space id 570 page no 16942 n bits 112 index PRIMARY of table `test`.`user_property_views` trx id 134818634 lock_mode X locks rec but not gap Record lock, heap no 39 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176363; asc cc;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f800b1; asc d ;; 8: len 4; hex 64f803b7; asc d ;; 9: len 4; hex 64f803b7; asc d ;; Record lock, heap no 53 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 00176372; asc cr;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: SQL NULL; 4: SQL NULL; 5: len 8; hex 35322d3536333530; asc 52-56350;; 6: SQL NULL; 7: len 4; hex 64f803b7; asc d ;; 8: len 4; hex 64f81e1f; asc d ;; 9: len 4; hex 64f81e1f; asc d ;; *** (2) TRANSACTION: TRANSACTION 134818634, ACTIVE 189 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 681 lock struct(s), heap size 90232, 2045 row lock(s), undo log entries 2 MariaDB thread id 453707, OS thread handle 23308626749184, query id 17197392 10.0.1.223 webmaster Update insert into `user_property_views` (`user_accounts_id`, `properties_property_cd`, `updated_at`, `created_at`) values (NULL, '52-56350', '2024-08-26 17:00:23', '2024-08-26 17:00:23') *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 570 page no 24944 n bits 16 index properties_property_cd of table `test`.`user_property_views` trx id 134818634 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 35322d3536353939; asc 52-56599;; 1: len 4; hex 00188da5; asc ;; *** CONFLICTING WITH: RECORD LOCKS space id 570 page no 24944 n bits 16 index properties_property_cd of table `test`.`user_property_views` trx id 134818679 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 35322d3536353939; asc 52-56599;; 1: len 4; hex 00188da5; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 134823210 Purge done for trx's n:o < 134823210 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x15344fd648c0), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x15344fd63dc0), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 42708 OS file reads, 920068 OS file writes, 812525 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.33 writes/s, 0.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 774097754010 Log flushed up to 774097754010 Pages flushed up to 773558503655 Last checkpoint at 773558503655 0 pending log flushes, 0 pending chkp writes 807780 log i/o's done, 0.33 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 5385486336 Dictionary memory allocated 32056824 Buffer pool size 324480 Free buffers 158700 Database pages 165780 Old database pages 61176 Modified db pages 30299 Percent of dirty pages(LRU & free pages): 9.338 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 113153, not young 2015954 0.00 youngs/s, 0.00 non-youngs/s Pages read 42603, created 355298, written 112134 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 165780, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 9312843, updated 305539, deleted 100852, read 5982990655 0.11 inserts/s, 0.00 updates/s, 0.00 deletes/s, 85.55 reads/s Number of system rows inserted 7, updated 1863, deleted 7, read 304252 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.06 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) ERROR: No query specified - - - - - - - - - - - - - - - - - - - - 以下、ChatGPTによる回答 このSHOW ENGINE INNODB STATUSコマンドの出力から、現在のデッドロックの状況と、その原因についていくつかのことが分かります。 1. デッドロックの概要 ・LATEST DETECTED DEADLOCKセクションに、最近検出されたデッドロックの詳細が記録されています。 ・具体的には、2つのトランザクション (TRANSACTION 134818679 と TRANSACTION 134818634) が競合して、デッドロックが発生しています。 2. トランザクションの内容 ・トランザクション1 (134818679) は、user_property_viewsテーブルで SELECT クエリを実行しており、これによりレコードに X ロックがかかっています。 これに対して、もう一つのトランザクションが挿入操作を行おうとして、ロックがかかっているために待機している状態です。 ・トランザクション2 (134818634) は、同じテーブルに対して INSERT 操作を行おうとしていますが、同じレコードに対して他のトランザクションがロックを持っているため、ロック待ちが発生しています。 3. デッドロックの解消 ・この場合、InnoDBはトランザクション1をロールバックすることでデッドロックを解消しました (WE ROLL BACK TRANSACTION (1)と記載されています)。 4. 継続的な問題かどうかの判断 ・現在のところ、エラーログにデッドロックが頻発しているか、またはこのSHOW ENGINE INNODB STATUSでデッドロックが再度報告されているかを確認することで、問題が継続的か一時的かを判断できます。 ・LATEST DETECTED DEADLOCKの時間が最近のものであり、頻繁に発生している場合は、デッドロックが継続的な問題となっている可能性があります。 5. 対策 デッドロックを防ぐためには、可能であれば、以下のような改善を検討することが推奨されます： ・クエリの順序を統一して、異なるトランザクションが同じ順序でロックを取得するようにする。 ・大量のレコードに対してロックを取得するクエリを、可能な限り短いトランザクション内で実行する。 ・LOCK IN SHARE MODE や FOR UPDATE といったクエリを見直し、必要最小限のレコードに対してのみロックをかけるようにする。 エラーログとInnoDBステータスを引き続き監視し、問題が継続するかどうかを確認することをお勧めします。 また、デッドロックが頻発する場合は、クエリの最適化やデータベース設計の見直しが必要かもしれません。