PingCAP Education： TiDBでのロック競合の トラブルシューティング
 本多康夫 Technical support engineer at PingCAP Japan

本日の位置づけ https://pingcap.co.jp/event/ 
 
 2022年7月28日(木) 14:00-15:00 PingCAP Education：TiDBでの表設計 2022年9月9日(金) 14:00-15:00 PingCAP Education：TiKV-トランザクション＆MVCC 2022年9月30日(金) 14:00-15:00 PingCAP Education：TiDBでのSQL実行の仕組み 2022年10月21日(金) 14:00-15:00 PingCAP Education：TiKV-データの整合性 2022年10月28日(金) 14:00-15:00 PingCAP Education：TiDB でのロック競合のトラブルシューティング 2022年11月25日(金) 14:00-15:00 PingCAP Education：TiDBでのスロークエリの特定と対応方法 2022年12月9日(金) 14:00-15:00 PingCAP Education：TiDB Data Migrationのご紹介 ※過去開催アーカイブ　：　https://pingcap.co.jp/event-video/

アジェンダ ● TiDBクラスタ概要 ● TiDBの悲観的ロックと楽観的ロックの違い ● TiDBのロック情報を確認するためのビュー ● TiDBでのロック待ちを確認、解除する方法 ● Q&A, アンケートのお願い ● お知らせ

● TiDBアーキテクチャ ● TiDBとTiKV, PDが分離 ● 複数TiDBサーバーが存在 TiDBクラスタ概要 TiDB TiDB TiKV Cluster (Storage) Metadata TiKV TiKV TiKV MySQL Clients TiKV TiDB TSO/Data Location TiDB TiDB ... ... DistSQL API PD PD PD Cluster TiKV TiKV TiDB PD MySQL Clients ...

おさらい: TiKVのLock CF(Column Family) ● DML実行時のロック情報は TiKVのLock CFに保存されています ● “PingCAP Education：TiKV-トランザクション＆MVCC” より ● https://speakerdeck.com/pingcap0315/tikv-toranzakusiyon-and-mvcc?slide=15

TiDBの悲観的ロックと楽観的ロックの違い

● 楽観的ロック ○ トランザクションのcommit時に行ロックを取得する ○ commit時に競合が発生することがある ● 悲観的ロック ○ トランザクション中の DML実行時に行ロックを取得する ○ 行ロックはcommitまたはrollbackで解放される TiDBの悲観的ロックと楽観的ロックの違い

● TiDBではシステム変数`tidb_txn_mode`によって、トランザクションを悲観的ロック (pessimistic)または楽 観的ロック(optimistic)のいずれかが選択可能 ● TiDB 3.0.8(2019年12月31日リリース)からは、悲観的ロック (pessimistic)がデフォルト値 ○ 特段の理由がない限り、デフォルト値 (pessimistic)をご利用ください TiDBの悲観的ロックと楽観的ロックの違い mysql> select @@session.tidb_txn_mode, @@global.tidb_txn_mode; +-------------------------+------------------------+ | @@session.tidb_txn_mode | @@global.tidb_txn_mode | +-------------------------+------------------------+ | pessimistic | pessimistic | +-------------------------+------------------------+ 1 row in set (0.00 sec)

TiDBのロック情報を確認するためのビュー

● TiDBサーバーで実行中のオペレーションを表示 ● information_schema.processlist ○ 接続しているTiDBサーバーのみのプロセス情報を表示 ● information_schema.cluster_processlist ○ すべてのTiDBサーバーでのプロセス情報を表示 ○ どのTiDBサーバーかはINSTANCE列で区別可能 ● “show processlist”コマンドとinformation_schema.processlistの差異 ○ DIGEST列、MEM(現在使用中のメモリ 単位はByte)、DISK(Disk spillしたサイズ 単位はByte) ○ TxnStart : 開始トランザクション TSO PROCESSLIST / CLUSTER_PROCESSLIST

TiDBサーバー2台構成 - 10.0.129.125: - 10.0.132.163: ポートの10080はサービスポート PROCESSLIST / CLUSTER_PROCESSLIST

● TiDBサーバーで実行中のトランザクションを表示 ● `information_schema.tidb_trx` ○ 接続しているTiDBサーバーの実行中のトランザクションを表示 ● `information_schema.cluster_tidb_trx` ○ すべてのTiDBサーバーでの実行中のトランザクションを表示 ○ どのTiDBサーバーかはINSTANCE列で区別可能 ● 主な列の意味 ○ ID ■ トランザクションID ○ SESSION_ID ■ トランザクションを実行しているセッション ID( processlistのIDと同一) ○ STATE ■ Idle(なにもしていない), Running(SQL実行中), LockWaiting(ロック獲得待ち), Committing(コミット中), Rollingback(ロールバック中) ● WAITING_START_TIME ○ STATEがLockWaitingの時の開始時刻(start_tsのTSOに対応する物理時刻 ) TIDB_TRX / CLUSTER_TIDB_TRX

TIDB_TRX / CLUSTER_TIDB_TRX TiDBサーバー2台構成 - 10.0.129.125: - 10.0.132.163: ポートの10080はサービスポート

● ロック待ちをしているトランザクションと待ちの原因のトランザクション IDを表示 ● information_schema.data_lock_waits ○ cluster_data_lock_waitsのようなビューは存在しない ○ MySQLでは同名のテーブルは performance_schemaにあるが、TiDBではperformance_schema がないため、information_schema内に存在します ● 主な列の意味 ○ CURRENT_HOLDING_TRX_ID ■ ロックを保持しているトランザクション ID(TRX_ID)を表示 ■ TIDB_TRXのIDと同じ値 ○ TRX_ID ■ ロックを取得しようとしている (待たされている)トランザクションID(TRX_ID)を表示 ■ TIDB_TRXのIDと同じ値 ○ SQL_DIGEST_TEXT ■ ロックを取得しようとしている正規化された SQL DATA_LOCK_WAITS

DATA_LOCK_WAITS ● 元々実行したSQLとの違い ○ テーブル名、列名がバッククォート ○ 1というリテラルが ? に置き換わる

● デッドロックとなったトランザクションを表示 ● information_schema.deadlocks ○ 接続しているTiDBサーバーのデッドロックを表示 ● information_schema.cluster_tidb_trx ○ すべてのTiDBサーバーのデッドロックを表示 ○ どのTiDBサーバーかはINSTANCE列で区別可能 ● 主な列の意味 ○ OCCUR_TIME ■ デッドロックエラーが発生した時刻 ○ TRY_LOCK_TRX_ID ■ ロックを取得しようとしているトランザクション ID ■ TIDB_TRXのIDと同じ値 ○ TRX_HOLDING_LOCK ■ 現在そのロックを取得しているトランザクション ID ■ TRY_LOCK_TRX_IDとTRY_HOLIDING_LOCKの値が同じエントリーがある DEADLOCKS / CLUSTER_DEADLOCKS

DEADLOCKS / CLUSTER_DEADLOCKS - `bar`から`bar2`の順序でupdate - `bar2`から`bar1の順序でupdate ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

DEADLOCKS / CLUSTER_DEADLOCKS

● KILL ○ TiDB 6.0以前 : KILLコマンドを実行するTiDBサーバーへの接続のみ切断可能 ○ TiDB 6.1.0 以降 KILL TIDB と同様の振る舞いとなりました ● KILL TIDB ○ どのTiDBサーバーに接続されている接続化にかかわらず、 TiDBサーバーへの接続を切断可能 ● KILLの引数にとるIDは processlist のID KILL / KILL TIDB

KILL / KILL TIDB KILLされたコネクション: ERROR 2006 (HY000): MySQL server has gone away

● ADMIN CANCEL DDL ○ DDLジョブをキャンセル ○ ADMIN CANCEL DDLの引数のJob IDが必要 ● ADMIN SHOW DDLで現在実行中のDDL jobを確認し、RUNNING_JOBS列の ID を確認する ● TiDB 6.2からは KILL 文によってDDL jobをキャンセル可能になりました ○ *: support canceling DDL statements with KILL ADMIN CANCEL DDL

おさらい: TiDBでのDDL実行 ● TiDBでのDDLは、DDL jobとしてカプセル化され、バックグラウンドワーカーが実行します ● https://speakerdeck.com/pingcap0315/newsql-wu-liao-toreningu-tidbdenosqlshi-xing-noshi-zu-mi ?slide=6

ADMIN CANCEL DDL KILLされたJob: ERROR 8214 (HY000): Cancelled DDL job

TiDBでのロック関連のパラメータ

● TiDBでの innodb_lock_wait_timeoutのデフォルト値は 50秒、最大値は3600秒です ● innodb_lock_wait_timeoutの最大値は、max-txn-ttlの最大値である3600秒に制限されており、これを 超えることはできません ● 3600より大きい innodb_lock_wait_timeout を設定した場合、警告が出て値は 3600に丸められます innodb_lock_wait_timeoutとmax-txn-ttl

TiDBでのロック待ちを 確認、解除する方法

● TiDB 6.1.2 ○ PD x 3 ○ TiDB x 2 ○ TiKV x 3 環境

● データベース : test ● テーブル : foo,foo2テーブル ● レコードは1行ずつ ○ insert into foo(bar) values ('test'); ○ insert into foo2(bar2) values ('test2'); 環境 : Database

● 同じ行に2つのトランザクションから select for updateを行う ● Terminal 1 : ロックを保持する ○ mysql -h -P 4000 -u root test ○ set autocommit = 0; ○ begin; ○ select * from foo where id = 1 for update; ● Terminal 2 : ロック取得待ちをする ○ mysql -h -P 4000 -u root test ○ set autocommit = 0; ○ begin; ○ select * from foo where id = 1 for update; 行ロック待ちを確認する方法

行ロック待ちを確認する方法 ● ロックが取得できている ● ロック待ちしている

● 行ロック待ちを確認する方法 : cluster_processlist

● 行ロック待ちを確認する方法 : cluster_tidb_trx

● 行ロック待ちを確認する方法 : data_lock_waits

● 待っている接続情報 select trx.session_id as waiting_session_id, trx.id as waiting_transaction_id from information_schema.data_lock_waits as l left join information_schema.cluster_tidb_trx as trx on l.trx_id = trx.id; ● ロックを保持している接続情報 select trx.session_id as holding_session_id, trx.id as holding_transaction_id from information_schema.data_lock_waits as l left join information_schema.cluster_tidb_trx as trx on l.CURRENT_HOLDING_TRX_ID = trx.id; 行ロック待ちを確認する方法 : joinする(SQL)

● ロックを保持している接続を kill tidb 行ロック待ちを強制的に解除する

Q&A、アンケートのお願い

PingCAP定期ウェビナー https://pingcap.co.jp/event/ ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists 


次回以降のアジェンダ https://pingcap.co.jp/event/ 
 
 2022年7月28日(木) 14:00-15:00 PingCAP Education：TiDBでの表設計 2022年9月9日(金) 14:00-15:00 PingCAP Education：TiKV-トランザクション＆MVCC 2022年9月30日(金) 14:00-15:00 PingCAP Education：TiDBでのSQL実行の仕組み 2022年10月21日(金) 14:00-15:00 PingCAP Education：TiKV-データの整合性 2022年10月28日(金) 14:00-15:00 PingCAP Education：TiDB でのロック競合のトラブルシューティング 2022年11月25日(金) 14:00-15:00 PingCAP Education：TiDBでのスロークエリの特定と対応方法 2022年12月9日(金) 14:00-15:00 PingCAP Education：TiDB Data Migrationのご紹介 ※過去開催アーカイブ　：　https://pingcap.co.jp/event-video/

PingCAP Education トレーニング トレーニング全体リンク 　　https://en.pingcap.com/education/ 無償コース（推奨） 　　TiDB Technical Essentials 101: 内容 　　- TiDB全体のアーキテクチャ 　　- TiDBの各コンポーネントのアーキテクチャ 　　- HTAP Overview & TiFlashのアーキテクチャ 　　- TiDB Cloudの始め方 　　- TiDB 6.0情報 Course: TiDB Essentials 101 トレーニングを通じ、TiDBの理解を深めることができます。

クラウドアプリのスタートをご支援！ TiDB Cloud 1年無料キャンペーン 日本進出から１周年を記念し、TiDB Cloudをご利用いただいたことがないお客様を対象にTiDB Cloud1年間相当の値引きキャンペーンを実施します。 キャンペーン期間 2022年5月18日～2023年3月31日　申込み分まで キャンペーン内容 最大700万円相当（TiDB Cloud典型構成で1年分相当）の値引きを提供します。 対象・適用条件 下記条件をみたすお客様 ※1（最大10社※2） 　- TiDB Cloudを使用したことがないお客様 　- 事例・プロモーションにご協力いただけるお客様 　- 設立から1年以上経過しているお客様 適用方法 フォームにご入力の上、キャンペーンにお申し込みください。審査結果およびキャンペーン の詳細について担当者より連絡いたします。 URL https://pingcap.co.jp/start-dash-202205/ ※1 別途所定の審査あり
 ※2 予定数に達し次第終了する可能性があります。


TiDB Cloud : 無償トライアルのご案内 https://tidbcloud.com/signup Serverless TierではTiDB Cloudを無償で1年間ご利用頂けます。 ※容量制限はありますが 本番と同等の機能を 提供しているため、 アプリとの接続試験 などが容易にできます。

OSS Insight : 活発度が分かる分析サービス 46億を超えるGitHub上のイベント分析するデータベースとしてTiDB (TiFlash)を活用 https://ossinsight.io/ ①各イベント発生の推移 ①GitHubイベント データを1分おきに同期 +TiFlash https://ossinsight.io/ ②Pull requests地域の表示 ②分析クエリを TiFlash(カラムストア ) で高速処理 ③ジャンル内での比較（人気言語等） etc…

①DMMプラットフォームにおける TiDB CloudとCloud Spannerの比較検証 (DMM.com社) ②金融トランザクションに NewSQLが使えるか検証してみ た (TIS社) ③TiDBの導入背景、3年間運用して感じたこと (U-NEXT 社) ④@cosmeがレガシーシステムをクラウドネイティブ DBに 載せ替える理由 (アイスタイル社) ⑤【DBaaS】TiDB Cloudのアーキテクチャと新機能 (PingCAP) ⑥リアルタイム分析の現実解。今話題の HTAPの実力と は？ (PingCAP) ⑦「最高のコンテンツ」を支える、 Cygamesのデータベー ス技術の今までとこれから〜次世代データベース「 TiDB」 の検証を開始したCygamesの取り組み〜 (Cygames社) PingCAP/TiDB セッション紹介

No content

TiDB / TiDB Cloud 日本語ドキュメント https://docs.pingcap.com/ja/tidb/stable 日本語訳問題の報告は こちらからお願いします。 https://github.com/pingcap/docs/issues issue作成時のLabel： i18n-ja type/bug-fix

@PingCAP_Japan Twitterのフォローをお願いします。

Thank You!
 https://www.pingcap.com/
 [email protected]