© 2024 NTT DATA Corporation Oracle Databaseの トランザクションと一貫性 JPOUG Tech Talk Night#9 2024年6月21日 株式会社NTTデータ 高橋 一裕

© 2024 NTT DATA Corporation 2 自己紹介 • DBスペシャリストとしてSIプロジェクトのDB担当 • 技術領域はOracle、Exadata、OCI • 現場の経験を共有してDBAの生活の質を向上したい • OracleACEとしてコミュニティ活動 MOSC (My Oracle Support Community) コミュニティ活動

© 2024 NTT DATA Corporation 3 本日お伝えしたいこと • 読み取り一貫性 • トランザクションとは何か（トランザクション表とUNDOの構造） • 読み取り一貫性の実現方式（ITLとUNDOの構造） • 参考： • Transactions and Consistency, pp.25-43, “Oracle Core, Essential Internals for DBAs and Developers”, Jonathan Lewis, apress

© 2024 NTT DATA Corporation 4 • 2人のユーザしかいないシステムで、データの参照、更新を繰り返すDBを想像する • あなたがDBへトランザクションを行い、私が問い合わせをするとき、私はあなたのトランザクションが完了（コミット）するまで はいかなる変更も見えてはいけない。また、私が見えるタイミングは、トランザクション分離レベルに依存する • 変更する側が提供する、２つの注目点(focal point) • トランザクションの開始を特定する点～トランザクション表 • 変更する表や索引ブロックに存在するエントリ～ITL コンフリクトの解消 ～読み取り一貫性 私 あなた 更新トランザクション COMMIT 参照 トランザクション分離レベル： read committed コミットされていない変更、コミットされたとして も（分離レベルに応じて）見るべきでない変 更を認識（無視）する効率的なメカニズム コミット時に、全てのユーザにトランザクションを コミットしたことを知らせる効率的なメカニズム （コミットしたタイミングで全てのブロックをマー クするような非効率な方法ではなく）

© 2024 NTT DATA Corporation 5 トランザクションとUNDO • DBを作成するときにUNDO表領域を作成する。Oracleはここに自動的にいくつかのUNDOセグメントを作成し、ワーク ロードに応じて自動的にUNDOセグメントを追加、増加、縮小、削除する • トランザクション管理はUNDOセグメントから始まる • トランザクション表（TRN TBL：直近のトランザクションを記録する小さな表） • トランザクション制御表（TRN CTL１：トランザクション表の状態と内容の詳細を記録する集合） • トランザクション表には直近のいくつかのトランザクションが記録される • Oracleのバージョンにより格納できる数は異なる（下記では11gで34行/セグメント） • 再利用する度にwrap#をカウントアップする Extent Control Header Extent Map Retention Table Transaction Control Free Block Pool Transaction Table Undoセグメントヘッダ Undo Tablespace Undoセグメント index state cflags wrap# uel scn dba nub cmt 0x00 9 0x00 0x2013 0x001b 0x0000.016f1fc1 0x0180083e 0x00000001 1302762364 0x01 9 0x00 0x2014 0x001a 0x0000.016f1f54 0x0180083e 0x00000001 1302762364 0x02 10 0x80 0x2013 0x0002 0x0000.016f20fc 0x0180083e 0x00000001 0 0x03 9 0x00 0x200c 0x001c 0x0000.016f20d8 0x0180083e 0x00000001 1302762364 … 0x20 9 0x00 0x200f 0x001f 0x0000.016f1c75 0x0180083e 0x00000001 1302762364 0x21 9 0x00 0x2013 0x0010 0x0000.016f1e0c 0x0180083e 0x00000001 1302762364 トランザクション表 トランザクショ状態 10:アクティブ 9:インアクティブ スロット 番号 再利用 回数 次のスロット 番号 コミットSCN 最後に利用したUNDO ブロックアドレス 利用したUNDO ブロック数 コミット時刻 （絶対値） 未コミット コミットSCN UNDOブロック 34 個 コミット済み

© 2024 NTT DATA Corporation 6 トランザクションの開始と終了 • トランザクションの開始 • UNDOセグメントを1つ選択する • トランザクション表からエントリを１つ選択し、wrap#を1増加、状態をactive(10)にする • REDOチェンジベクターを生成し、REDOログファイルに書き込む • トランザクションの終了（＝～コミット） • トランザクション表のエントリを、状態をinactive(9)にし、scnカラムに現在のSCNを入れる • REDOチェンジベクターを生成し、REDOログファイルに書き込む • トランザクションはトランザクション表のエントリで定義される • Undoセグメント番号.トランザクション表のエントリ番号.wrap#で一意に決まる • トランザクションIDはv$transactionやv$lockに現れる 0x0009.002.00002013 トランザクションID: Undoセグメント番号 トランザクション表 のエントリ番号 wrap#(8211)

© 2024 NTT DATA Corporation 7 ここまでのまとめ • トランザクション表のスロットに記録される情報 • データベースの特定の物理的な場所 • トランザクションがコミットされたか、またアクティブなのか • コミットされたトランザクションのSCN • トランザクションにより生成された最も直近のUNDOレコードの場所 • トランザクションにより生成されたUNDO量 • インスタンスクラッシュ等トランザクションをロールバックするとき • アクティブトランザクション(state=10)をトランザクション表から特定 • UNDOブロックを特定(dba)し、UNDOをさかのぼり適用

© 2024 NTT DATA Corporation 8 UNDOブロックの構造 • 表ブロックとUNDOブロックの類似 • ヘッダセクションにメタデータを格納 • ブロックのスタックされたアイテムの場所のリスクを示す行ディレクトリ • アイテムのヒープ（表なら行、UNDOならレコード） • 異なる点は、UNDOは（一つの例外を除き）更新されないので、きれいに並ぶ • UNDOブロックにはオーナートランザクションがある • 1つのUNDOブロックに別トランザクションのUNDOレコードが入ることもある Header and Summary ITL list Space Summary Row Directory Free Space Row Heap 表ブロック Control section Record Directory Free Space Record Heap UNDOブロック Free Space Free Space **************************************************************** UNDO BLK: xid: 0x008.029.00002068 seq: 0x97a cnt: 0xc icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset ----------- ----------- ----------- ----------- ----------- 0x01 0x1f9c 0x02 0x1f4c 0x03 0x1ebc 0x04 0x1e88 0x05 0x1e2c 0x06 0x1db8 0x07 0x1d40 0x08 0x1cec 0x09 0x1c84 0x0a 0x1c28 0x0b 0x1bbc 0x0c 0x1b60 *-------------------------------- * Rec #0x1 slt: 0x17 objn: 2(0x00000002) objd: 4294967295 tblspc: 12(0x0000000c) * Layer: 22(Tablespace Bitmapped file) opc: 3 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No ... *-------------------------------- * Rec #0xc slt: 0x29 objn: 45756(0x000002bc) objd: 45756 tblspc: 12(0x0000000c) * Layer: 11(Row) opc: 1 rci 0x0b Undo type: Regular undo Last buffer split: No Temp Object: No 別のトランザクション オーナートランザクション UNDOブロックダンプ

© 2024 NTT DATA Corporation 9 データブロックとUNDO • データブロックを参照するとき、そのデータが適切なバージョンであることを保証しなければならない。これをデータの読み取り 一貫性（のバージョン）という Block header dump: 0x00c0070a Object id on Block? Y seg/obj: 0x18317 csc: 0x00.1731c44 itc: 2 flg: 0 typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0001.001.00001de4 0x01802ec8.0543.05 --U- 3 fsc 0x0000.01731c46 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x00c0070a data_block_dump,data hearder at 0xc90225c =============== tsiz: 0x1fa0 hsiz: 0x18 pbl: 0x0c90225c 76543210 flag=-------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x1f85 avsp=0x1f6d tosp=0x1f6d 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x1f97 0x14:pri[1] offs=0x1f8e 0x16:pri[2] offs=0x1f85 block_row_dump: tab 0, row 0, @0x1f97 tl: 9 fb: --H-FL-- lb: 0x1 cc:2 col 0: [ 2] c1 02 col 1: [ 2] c1 02 tab 0, row 1, @0x1f8e tl: 9 fb: --H-FL-- lb: 0x1 cc:2 col 0: [ 2] c1 03 col 1: [ 2] c1 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x1 cc:2 col 0: [ 2] c1 04 col 1: [ 2] c1 04 end_of_block_dump • ここで１つの表を作成し、そのブロックダンプを取得し、 ITL(Interested Transaction List)の状態を確認する • ITLは最近このブロックを更新したトランザクションを特定す るためにある • エントリ0x01のトランザクション0x1.1.1de4が SCN1731c46でこのブロックを更新した • ブロックはまだクリーンアウトされていない(Flag:U) • ロックされた行数は3(Lck) • トランザクションのUNDOブロック(Uba) • Ubaからトランザクションにより更新されたUNDOを特定し ロールバックすることができる • 0x01のUba先のUNDOブロックには、3行目のINSERT を取り消し、Ubaを2行目のUNDOブロックに更新 create table t1(id number, n1 number); insert into t1 values(1,1); insert into t1 values(2,2); insert into t1 values(3,3); commit; ID N1 1 1 2 2 3 3 表ブロックダンプ

© 2024 NTT DATA Corporation 10 並列な処理 • ４つの独立したセッションを開始し、以下のステップを実行する • 自分のセッションはトランザクション分離レベルをread onlyにしたため、以下２つの制約が適用される • 他ユーザによる未コミットな変更は見ることはできない（read committedと同じ） • この瞬間以降にコミットされた変更を見ることはできない セッション＃１ セッション＃２ 自分のセッション セッション#3 update t1 set n1 = 101 where id = 1; update t1 set n1 = 102 where id = 2; commit; update t1 set n1 = 99 where id = 3; commit; select id, n1 from t1; set transaction read only; ID N1 1 1 2 102 3 3 ID N1 1 101 2 102 3 99 ID N1 1 1 2 102 3 99 ID N1 1 1 2 102 3 99

© 2024 NTT DATA Corporation 11 変更後のブロックの状態 • ３つのセッションが変更を行った後のブロックの状態（※） • 各行はすでに更新されている （セッション#1の未コミット含め） • セッションは3つだが、ITLは2つ（再利用されている） • エントリ0x01 • コミットクリーンアウト(U)が発生 • row 2のロックバイトが0x1なので、セッション#3 • エントリ0x02 • コミットされていないように見える(Scn/Fscが0） • ロックバイトが0x2なので、セッション#1 • 未コミットだがcol1(N1)カラムは更新されている Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.00e.00001b93 0x01800518.04f5.34 --U- 1 fsc 0x0000.01731c83 0x02 0x0004.00c.00001964 0x018036ad.05ff.3a ---- 1 fsc 0x0000.00000000 0x12:pri[0] offs=0x1f7b 0x14:pri[1] offs=0x1f71 0x16:pri[2] offs=0x1f85 tab 0, row 0, @0x1f7b tl: 9 fb: --H-FL-- lb: 0x2 cc:2 col 0: [ 2] c1 02 col 1: [ 2] c2 02 02 tab 0, row 1, @0x1f71 tl: 9 fb: --H-FL-- lb: 0x0 cc:2 col 0: [ 2] c1 03 col 1: [ 2] c2 02 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x1 cc:2 col 0: [ 2] c1 04 col 1: [ 2] c1 64 ※alter system checkpointでdump前にバッファキャッシュをクリア ID N1 1 101 2 102 3 99 表ブロックダンプ（変更後のイメージ） col0 col1 row 0 row 1 row 2

© 2024 NTT DATA Corporation 12 【参考】ITLエントリとUNDOセグメントのリンク • ITLには表ブロックを更新した直近のトランザクションへのリンク(XID)と、UNDOブロックへのリンク(UBA)を持っている • 表ブロックの各行はITLへのリンクを持っている(LB) ITL List #1: XID 3.1843.9 UBA 0x008011b2.0aba.16 #2: XID 5.1843.2 UBA 0x00803c75.0a0d.4 Row directory Free Space Rows xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 2 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 2 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 1 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 表ブロック Segment Header … DBA:0x00803c75 … UNDOセグメント5 Rec4 From UBA From XID Slot 2 UNDOセグメント３へ トランザクション表 UNDOブロック Record Directory Record Heap

© 2024 NTT DATA Corporation 13 一貫性を作る • メモリにブロックをクローンして、現在の（カレント）バージョンから以下のバージョンを作り出す • ITL0x01のXidが2100になっている（1b93ではなく） • このトランザクションはコミットされクリーンアウト適用済み(FlagがC---)で、ロックカウントがゼロにリセットされている • ブロックのバージョンから、セッション#1,#3の変更が消えている Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.00d.00002100 0x018030ca.074c.11 C--- 0 fsc 0x0000.01731c7a 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 1 fsc 0x0000.00000000 0x12:pri[0] offs=0x1f68 0x14:pri[1] offs=0x1f71 0x16:pri[2] offs=0x1f85 tab 0, row 0, @0x1f68 tl: 9 fb: --H-FL-- lb: 0x0 cc:2 col 0: [ 2] c1 02 col 1: [ 2] c1 02 tab 0, row 1, @0x1f71 tl: 10 fb: --H-FL-- lb: 0x0 cc:2 col 0: [ 2] c1 03 col 1: [ 2] c2 02 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x0 cc:2 col 0: [ 2] c1 04 col 1: [ 2] c1 04 ID N1 1 1 2 102 3 3 • Oracleのアルゴリズム ①ブロックを別のバッファにクローン ②コミット済みトランザクションにクリーンアウトを適用 ③未コミットトランザクションをリバース ④ターゲットSCNより大きいコミット済みSCNのトランザクショ ンがあればリバース ID N1 1 101 2 102 3 99 ID N1 1 1 2 102 3 3 ①ブロックを クローン ③未コミットなトラン ザクションをリバース ④SCNが大きすぎる トランザクションをリバース ②コミット済みトラン ザクションをクリーンアウト Itl Flg 0x01 C--- … Itl Flg 0x01 --U- … 表ブロックダンプ（読み取り一貫性イメージ）

© 2024 NTT DATA Corporation 14 【参考】一貫性を作るステップ詳細 • バッファへのクローンされた後のITLとレコードの状態遷移 • このケースではコミット済みブロックのクリーンアウトは不要なので③から開始 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.00e.00001b93 0x01800518.04f5.34 --U- 1 fsc 0x0000.01731c83 0x02 0x0004.00c.00001964 0x018036ad.05ff.3a ---- 1 fsc 0x0000.00000000 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.00e.00001b93 0x01800518.04f5.34 --U- 1 fsc 0x0000.01731c83 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02のトランザクション表をチェックし、トランザクションがアクティブか確認（アクティブ） 未コミットトランザクションをリバースするためにUbaからUNDOブロックを参照 UNDOの内容に従い、row0のカラム1をc1 02 (decimal 1)に変更、0x02をリセット し「未使用」にする Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.00d.00002100 0x018030ca.074c.11 C--- 0 fsc 0x0000.01731c7a 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 もう未コミットトランザクションはないので、一番大きなSCNを確認 （ターゲットのSCNに対して0x01731c83は大きすぎる） 0x01のトランザクションをリバースするためにUbaからUNDOブロックを参照 UNDOの内容に従い、row2のカラム1をc1 04 (decimal 3)に変更、ITL(※)を変 更する 一番大きなSCNを確認。ターゲットのSCNに対して0x01731c7aは適切なので、この セッションが使うべき正しい読み取り一貫性のバージョンとなる op: L itl: xid: 0x0009.00d.00002100 uba: 0x018030ca.074c.11 flg: C--- lkc: 0 scn: 0x0000.01731c7a ID N1 1 101→1 2 102 3 99 ID N1 1 1 2 102 3 99→ 3 ID N1 1 1 2 102 3 3 ③未コミットなトラン ザクションをリバース ④SCNが大きすぎる トランザクションをリバース (※)UNDOレコードに含まれるITLの変更イメージ 変更後イメージ 読み取り一貫性 イメージ

© 2024 NTT DATA Corporation 15 まとめ • Oracle Databaseのトランザクションと一貫性についてお話しました • 鍵となる２つの構造はトランザクション表とITL • 読み取り一貫性のバージョンを作るために、紐づくUNDOを利用 • より理解を深めるために原書をぜひご一読いただきたい

© 2024 NTT DATA Corporation 16 【参考】ITLのカラム カラム 説明 Itl 配列のインデックス。この値は行のlock byte(lb:) に、どのトランザクションがその行をロックしていたかを示すのに使われる Xid 最近のこのブロックを更新したトランザクションのトランザクションID。フォーマットは以下の通り： UNDOセグメント.UNDOスロット.UNDOシーケンス番号 Uba 最も最近このブロックを更新したトランザクションにより生成されたUNDOレコードのブロックへのUNDOレコードアドレス（シーケ ンス／インカネーション番号を含む）。フォーマットは以下の通り： 絶対ブロックアドレス.ブロックシーケンス番号.ブロック内のレコード Flag このトランザクションの見かけ上の状態を識別するビットフラグ ----:アクティブ（または、Xidが全てゼロなら「存在していなかった」ことを示す） --U-:アッパーバウンドコミット（「ファストコミット」の時も設定される） C---:コミットされクリーンアウトされた（関連するロックバイトはゼロにリセットされる） -B--:索引ブロック分割の再帰トランザクションに関連しているかもしれない。このフラグはUBAがITLエントリの前の内容を保 持するレコードを参照することを意味するというコメントを見たことがあるが、確認されていない ---T:ブロッククリーンアウトのときトランザクションがアクティブだったことを示すというコメントを見たことがあるが、確認されていない Lck ブロック内において、このトランザクションによりロックされた行数 Scn/Fsc Flagに依存して、コミットSCN、またはこのトランザクションがコミットされたら利用可能になる空き容量のバイト数（Free Space Credit）を示す

© 2024 NTT DATA Corporation