Innodbの行レベルロックアルゴリズムの選択

10159 ワード
mysql
多くの人がMysqlの他の記憶エンジンに対して、Innodbは明らかな特徴があります.それは行レベルのロックをサポートしています.次にInnodbの行レベルのロックを調べてみましょう.
行レベルロックは主に3つのアルゴリズムがあります.
  • Record Lock:1行記録上のロック.
  • Gap Lock:ギャップロック.範囲をロックしますが、記録自体は含まれていません.
  • Next-Key Lock:範囲をロックし、記録自体を含む.
    • InooDBでは、行のクエリーに対してNext-Key Lockというロックアルゴリズムを採用しています.このロックアルゴリズムの使用には、3つのケースがあります.
  • ロックされた列が主キーで唯一の場合、Next-Key LockはRecord Lockにダウングレードされる.
  • ロックされた列が補助インデックスである場合、NextKey-lockインデックスは、次のキーの補助インデックスにgap Lockを使用する.
  • ロックされている列がインデックスではない場合は、全テーブルをロックします.
    • 次は三つの状況について分析します.
  • ロックされている列がメインキーで唯一の場合、Next-Key LockはRecord Lock
    • にダウングレードされる.
    まずデータを構築します.
    DROP TABLE IF EXIST t;
CREATE TABLE t(id INT PRIMARY KEY);

INSERT INTO t SELECT 1;
INSERT INTO t SELECT 2;
INSERT INTO t SELECT 5;
    実験を行い、開始前にロック待ちのタイムアウト時間を最小SET GLOBAL innodb_lock_wait_timeout=1;に設定することを提案します.
    スレッドA
    スレッドB
    BEGIN;
    SELECT*FROM t WHERE id=5 FOR UPDATE;/ターゲットステートメントに排他Xロックを追加します.
    BEGIN;
    INSERT INTO SELECT 4//成功
    INSERT INTO SELECT 6//成功
    INSERT INTO SELECT 5//異常Lock wait timeout exceededを投げる;try restarting trnsaction
    COMMIT;
    COMMIT;
    スレッドAクエリの行が存在しない場合、ロック操作は行われません.
    ロックのタイムアウトは現在の事務のロールバックにつながりません.提出後に操作が成功した挿入コマンドはまだ有効です.タイムアウトしたい場合は、ジョブをロールバックして、mysqlのプロファイルにinnodb_rollback_on_timeout=on;を設定することができます.また、Innodbでは、実際にはインデックスにロックをかけるため、ロックがあるときにテーブルインデックスを変更しないようにします.
  • ロックされている列が補助インデックスである場合、NextKey-lockインデックスは、次のキーの補助インデックスにgap Lock
    • を使用する.
    まずデータを構築します.
    DROP TABLE IF EXIST t;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t SELECT 1;
INSERT INTO t SELECT 3;
INSERT INTO t SELECT 6;
    実験を行う:
    スレッドA
    スレッドB
    BEGIN;
    SELECT*FROM t WHERE id=3 FOR UPDATE;
    BEGIN;
    INSERT INTO SELECT 2//異常を投げる
    INSERT INTO SELECT 4//異常を投げる
    INSERT INTO SELECT 1//異常を投げる
    INSERT INTO SELECT 6//成功
    INSERT INTO SELECT 0//成功
    COMMIT;
    COMMIT;
    Next-Key Lockロックアルゴリズムは、Phtom Problem(ファントム問題)を解決するために、このアルゴリズムを用いたロック技術をNext-Key Lockingと呼び、このロック技術は単一の値ではなく、一つの範囲にロックされている.Next-Key Lockロックアルゴリズムがなぜ幻像問題を解決できるのかを説明します.
    上記の例では、ロックの値3に対して、Next-Key Lockがロックしたいのは区間(1,3)であり、同様にPhtom Problemを解決するためには、補助インデックスの次のキーにgap Lockを使用して、ロック区間(4,6)を特定する必要があり、上記の実験では挿入値1も例外を投げた理由は、値1を(1,3)に挿入したものとみなすことができる.
    挿入1の位置
    0
    1
    2
    3
    4
    ユーザーは以下の2つの方法で明示的にロックをオフにすることができます.
  • は、トランザクションの分離レベルをREAD COMMITTEDに設定する.set session transaction isolation level read committed
  • はパラメータinnodbcuulocksを1に設定します.sel session innodb_locks_unsafe_for_binlog = 1
  • ロックする列がインデックスではない場合、全テーブルをロックする:
    • まずデータを構築します.
    DROP TABLE IF EXIST t;
CREATE TABLE `t` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t SELECT 1;
INSERT INTO t SELECT 3;
INSERT INTO t SELECT 6;
    実験を行う:
    スレッドA
    スレッドB
    BEGIN;
    SELECT*FROM t WHERE id=3 FOR UPDATE;
    BEGIN;
    INSERT INTO SELECT 2;/投げ異常
    INSERT INTO SELECT 4;/投げ異常
    INSERT INTO SELECT 1;/投げ異常
    INSERT INTO SELECT 6;/投げ異常
    INSERT INTO SELECT 0//投げ異常
    COMMIT;
    COMMIT;
    以上は、行レベルロックに対しての3つの場合であり、InnoDBが行レベルロックを実現する前提はインデックスを使用していることが分かりますので、事務があり、同時に量が大きいシステムでは、SQL文と索引の設計には、行レベルロックを考慮に入れるべきです.
    前述のように、InnoDBはNext-Key Lockを利用してPhtom Problemを解決するという話がありますが、ファントム問題とは、ある事柄を2回読み取りする間に、他の事務に1行のデータを追加して、2回のクエリデータが一致しない問題を解決するために、ファントム読み取りを解決するためには、一般的に全表ロックを行う必要があります.
    しかし、MysqlはNext-key lockingを通じてこの問題を解決しました.Next-key lockingは、実はgap lockとrecord lockの総称で、幻読問題を解決するのはgap lockで解決します.
    以下の例を挙げます.
    構造データ:
    DROP TABLE IF EXIST t;
CREATE TABLE `t` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t SELECT 1;
INSERT INTO t SELECT 3;
INSERT INTO t SELECT 6;
    実験ステップ:
    スレッドA
    スレッドB
    SET SESSION transaction isolation level read comitted;
    BEGIN;
    SELECT*FROM t WHERE id>3 FOR UPDATE;/出力は5
    BEGIN;
    INSERT INTO SELECT 6//成功
    COMMIT;
    SELECT*FROM t WHERE id>3 FOR UPDATE;/出力は5 6
    RCの隔離レベルでは2回の照会データが一致しない問題がありますが、MysqlのNext-key lockingは[3、+∞]にX列を加えて鍵をかけて幻読み問題を解決しました.
    しかし、ここでは、この例では範囲検索が行われていますが、上の正確な検索はなぜNext-key lockingが採用されているのでしょうか?Next-key lockingは直接に下のB+木に区間をロックすることによって実現されます.また、指定された行をロックして更新されないようにしています.上記の例では、このときにもう一つのid=3の記録を挿入すると、幻読みが発生します.ロックポイントの一番近い両端の値をロックすることによって、幻読みを回避します.
    最後に一つの問題が残っています.なぜメインキーをロックするとレコードロックになりますが、唯一のインデックスはできません.
    Algorithm——配列シャッフルアルゴリズム(7)
    STL sort関数の内部実装