InnoDB

ストレージエンジンの一つ
MySQL5.5以降のデフォルトのストレージエンジン

InnoDBの構成

InnoDBのメモリ、ファイル構成の概要

Buffer_pool（バッファプール） :
テーブルとインデックスのキャッシュ領域
log_buffer（ログバッファ）:
Redo logは一旦この領域に書き込まれ、COMMITのタイミングでDiskフラッシュされる
Redo log （InnoDBログ）:
Redo logファイルにシーケンシャルに変更内容を書き込むことで更新速度を向上させる。
table space:
InnoDB テーブルおよび関連付けられたインデックスのデータを保持するデータファイル
innodb_file_per_tableが有効な場合、テーブル毎に作成される
System table space:
InnoDBデータディクショナリ、Undo log、変更バッファー、二重書き込みバッファーの
ストレージ領域のメタデータを含むデータファイル

ACID準拠のトランザクション機能

InnoDBはACID準拠のトランザクション機能をもつ

トランザクション

コミットまたはロールバックされる作業単位

例)

sql

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;

COMMIT; -- ROLLBACK;

ACIDとは

トランザクション処理に求められる4つの特性
以下性質の頭文字をとったもの。

原子性 (Atomicity)
トランザクションに含まれるタスクが全て実行されるか、全く実行されないことを保証する
一貫性 (Consistency)
トランザクション開始と終了時に整合性を満たすことを保証する
分離性 (Isolation)
トランザクション中に行われる操作の過程が他のトランザクションから隠蔽される
持続性 (Durability)
トランザクション操作の完了通知をユーザが受けた時点で、
その操作は永続的となり、結果が失われない

更新処理

ACIDを実現しつつ、現実的なパフォーマンスを得るため、
「WAL」（Write Ahead Log（ログ先行書き込み））で更新される。

doublewrite_buffer（二重書き込みバッファー）:
データをデータファイルに書き込む前にdoublewrite_bufferに書き込むことで信頼性を向上させる。

データ更新時の流れ

更新クエリ発行、更新内容はlog_bufferにも反映される
COMMIT時にredo log(InnoDBログファイル)に更新情報を追記（シーケンシャルライト）
COMMIT完了を通知
定期的にバッファプールのデータをデータファイル（テーブルスペース）にフラッシュ
- データ保護のため、同じ内容を2回書き込む。ダブルライトバッファ、本番ページ

クラッシュリカバリ

クラッシュ時は、起動時に以下プロセスでデータ復旧される

Redoログの適用
未完了のトランザクションのロールバック（Undoログで？）
挿入バッファーのマージ
※変更対象のIndexがディスク上にない場合、Diskデータを読まずメモリ内で更新。更新内容をマージするらしい

InnoDB のリカバリプロセス

ロック制御

トランザクションによって照会または変更されているデータを
他のトランザクションが見たり変更したりすることを防止する
InnoDBは基本行ロックでロックを取得する。

ロックモード

共有(S)ロック :
ロックを保持するトランザクションによる行の読み取りが許可
排他(X)ロック :
ロックを保持するトランザクションによる行の更新、削除が許可
インテンションロック
テーブルロックのみ。
処理を行いたいテーブルに対し「ロックを取ることを宣言する」ロック
- インテンション共有(IS)ロック：
  Sロックの前に対象テーブルにISロックを行う
- インテンション排他(IX)ロック :
  Xロックの前に対象テーブルにIXロックを行う

ロックの競合と互換の関係

	X	IX	S	IS
X	競合	競合	競合	競合
IX	競合	互換	競合	互換
S	競合	競合	互換	互換
IS	競合	互換	互換	互換

T1: トランザクション１
T2: トランザクション２
r : 行
S : 共有ロック
X : 排他ロック

  T1が行rにSロック
   T2が行rにSロック -> OK
   T2が行rにXロック -> Wait...

  T1が行rにXロック
   T2が行rにSロック -> Wait...
   T2が行rにXロック -> Wait...

ロックの範囲

InnoDBではクラスタインデックス構造を採用
ロックはインデックスをもとに行われ、Index上を走査した行が対象となる

レコードロック（行ロック）
同じテーブルの他の行へ他のトランザクションがアクセス可能
ギャップロック
インデックス上のレコードとレコードの間をロック
(ファントムリードを防ぐ)
ネクストキーロック
レコードロックとその手前のギャップロック

レコードロック（行ロック）

     __9__
    /     \
   4       15
  /\      / \
 2  6    ★  17

ギャップロック

     __9__
    /     \
   4       15
  /\      / \
 2  6____12  17
      ↑
　　　この間のこと

ネクストキーロック

     __9__
    /     \
   4       15
  /\      / \
 2  6____★  17
      ↑
　　　この間と★のこと

トランザクション分離レベル

「待ち時間を減らすためどれだけデータの一貫性を犠牲にして良いか」を定めたもの

ANSI/ISO SQL標準で定められている分離レベル

下に行くほど一貫性が保たれる

READ UNCOMMITTED ( 確定していないデータまで読み取る )
READ COMMITTED ( 確定した最新データを常に読み取る )
REPEATABLE READ ( 読み取り対象のデータを常に読み取る )
SERIALIZABLE ( 直列化可能 )

分離レベルによって起こる現象

ダーティーリード :
別のトランザクションによって更新されたが、
まだコミットされていないデータを取得されてしまう
ファジーリード/ノンリピータブル・リード :
同一トランザクション内の同一クエリで、取得タイミングにより、
異なる結果が返る
(その間にコミットしている別のトランザクションによって変更された)
ファントムリード:
同一トランザクション内の同一クエリで、取得タイミングにより、
行が増えた結果が返る
(その間にコミットしている別のトランザクションによって挿入された)

分離レベルと起こる現象

分離レベル	ダーティリード	ファジーリード	ファントムリード
READ UNCOMMITED	発生する	発生する	発生する
READ COMMITTED	発生しない	発生する	発生する
REPEATABLE READ	発生しない	発生しない	発生する (InnoDBでは発生しない)
SERIALIZABLE	発生しない	発生しない	発生しない

MVCC(マルチバージョンコンカレンシーコントロール)

共有(S)ロックを取得せずにSELECT可能な仕組み
他のトランザクションが更新中のデータをロックせず参照することができるため、並列度が向上する。

同じ行データに対し、複数のバージョンをUndoログに保持することで、
更新中でロックされたデータも読み取ることができる。

Undo log （ロールバックセグメント）:
トランザクションが変更中のデータの、変更前のデータを保持する領域。
別トランザクションは変更前のデータを読み込む

参考

ACID
トランザクション分離レベル
 MySQLのロックについて dbstudy.info
InnoDBのREPEATABLE READにおけるLocking Readについての注意点漢のコンピュータ道
 大人のためのInnoDBテーブルとの正しい付き合い方。漢のコンピュータ道

基礎MySQL ~その 4~ InnoDB①