RR および RC 分離レベルでのインデックスとロックのテスト スクリプトのサンプル コード

次のテーブルから * を選択します。

select * from table where ? lock in share mode;
更新のために、? のテーブルから * を選択します。
テーブルの値に挿入します（）;
テーブルセットを更新しますか? どこに?
テーブルから削除?

-- 基本操作 --
--クエリトランザクション分離レベル。デフォルトはRRです。
'%isolation%' のような変数を表示します。

-- トランザクション分離レベルを RC に設定する
セッショントランザクション分離レベルをコミット読み取りに設定します。


-- データの初期化 --
始める;
ユーザーが存在する場合はテーブルを削除します。
テーブル `user` を作成します (
 `id` bigint(20) 符号なし NOT NULL AUTO_INCREMENT,
 `email` varchar(64) NOT NULL,
 `age` int(11) NOT NULL,
 `address` varchar(64) NOT NULL,
 主キー (`id`)、
 ユニークキー `uniq_email` (`email`)、
 キー `idx_age` (`age`)
);

ユーザー（メール、年齢、住所）に値（「[email protected]」、「18」、「address1」）を挿入します。
ユーザー（メール、年齢、住所）に値（「[email protected]」、「20」、「address2」）を挿入します。
ユーザー（メール、年齢、住所）に値（「[email protected]」、「20」、「address3」）を挿入します。

専念;
ユーザーから*を選択します。



-- 1. trx_idの例の開始;
INFORMATION_SCHEMA.INNODB_TRX から TRX_ID を選択し、TRX_MYSQL_THREAD_ID = CONNECTION_ID() を指定します。
ユーザーから*を選択します。
INFORMATION_SCHEMA.INNODB_TRX から TRX_ID を選択し、TRX_MYSQL_THREAD_ID = CONNECTION_ID() を指定します。
エンジン INNODB ステータスを表示します。
ユーザーを更新し、ID = 3 で age = 22 に設定します。
-- トランザクション ID を照会する
INFORMATION_SCHEMA.INNODB_TRX から TRX_ID を選択し、TRX_MYSQL_THREAD_ID = CONNECTION_ID() を指定します。
-- INNODB エンジンのステータス SHOW ENGINE INNODB STATUS;
専念;

-- 2. 繰り返し読み取りと繰り返し不可能な読み取りの例 -- セッション 1
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
--セッション2
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
--セッション1
ユーザーから*を選択します。
--セッション2
ユーザーから*を選択します。
--セッション3
始める;
ユーザー（メール、年齢、住所）に値（「[email protected]」、「30」、「address4」）を挿入します。
専念;
-- セッション 1 は RC モードなので、trx3 によって送信された新しいデータを読み取ることができます。反復不可能な読み取りを証明する場合は、挿入ではなく更新を使用する必要があります。
ユーザーから*を選択します。
専念;
-- ここではセッション 2 は RR なので、trx3 select * from user によって送信された新しいデータは読み取られません。
専念;

-- 3. スナップショット読み取りファントム読み取りの例 -- セッション 1
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
-- ここではスナップショット read select * from user を使用します。
--セッション2
始める;
ユーザー（メール、年齢、住所）に値（「[email protected]」、「30」、「address4」）を挿入します。
専念;
ユーザーから*を選択します。
--セッション1
select * from user; -- test4@のデータはRRなのでここでは読み取れません
-- ここでファントム読み取りが発生します insert into user (email, age, address) values ​​("[email protected]", 30, "address4"); -- 電子メールの一意のインデックスの競合のため、挿入は失敗します commit;

-- 4. 電流読み取りファントム読み取りの例 -- RC
--セッション1
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
-- ここでは、条件を満たす age = 20 のすべてのレコードがロックされます。RC なので、GAP ロックはありません。delete from user where age = 20;
ユーザーから*を選択します。
--セッション2
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
-- trx1 には GAP ロックがないため、age=20 のレコードをユーザー (メール、年齢、住所) の値に挿入できます ("[email protected]", 20, "address4");
select * from user; -- 4 つのデータが見つかり、trx1 の削除されたデータを読み取ることができます。RC であるため、trx1 は送信されておらず、trx2 には影響しません。
専念;
--セッション1
select * from user; -- trx2 の新しく挿入されたデータを読み取ることができます。trx1 は現在読み取り中ですが、対応する次のキー ロックは追加されていないため、trx2 の新しいデータが挿入されコミットされることは妨げられません。

--RR
--セッション1
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
年齢が 20 のユーザーから削除します。
ユーザーから*を選択します。
--セッション2
始める;
-- trx1 が age=20 付近に GAP ロックを追加するため、ブロックが発生します。 -- 一意でないインデックスの場合、まずインデックスを通じてクエリ条件を満たす最初のレコードを見つけ、そのレコードに X ロックを追加し、GAP に GAP ロックを追加してから、主キー クラスター化インデックスのレコードに X ロックを追加します。
-- 次に次のものを読んで繰り返します。条件を満たさない最初のレコードに到達するまで、この時点ではレコード X ロックを追加する必要はありませんが、GAP ロックは依然として必要であり、最終的に最後に戻ります。
ユーザー（メール、年齢、住所）に値（「[email protected]」、「20」、「address4」）を挿入します。
-- タイムアウトまで、ERROR 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。
-- この時点でクエリを実行すると、コミットされたレコードが 3 つ表示されます。
--セッション1
-- 現時点では 1 つのレコードのみが表示され、他の 2 つは削除されています。select * from user;
専念;

-- ユニークインデックス + RC
--セッション1
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
email = "[email protected]" のユーザーから削除します。
--セッション2
始める;
-- trx1 は RC なので読み取ることができます
email = "[email protected]" のユーザーから * を選択します。
-- このレコードの age を更新しようとすると、タイムアウトまでブロックされます。これは、email が trx1 によってロックされている唯一のインデックスであり、対応する主キー インデックスもロックされるためです。 -- ここで操作される id=3 は、trx1 で操作された email の行レコードと同じであることに注意してください。update user set age = 40 where id = 3;
--セッション1
専念;
--セッション2
専念;

-- インデックスなし + RC
--セッション1
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
-- アドレスフィールドにはインデックスがないため、Innodb はすべての行をロックし、MySQL サーバーが判断してロックを解除します。delete from user where address = "address3";
--セッション2
セッショントランザクション分離レベルをコミット読み取りに設定します。
始める;
-- この行はロックされていないため成功します (最初にロックされ、その後解放されます)
ユーザーを更新し、age = 10 とし、address = "address2" とします。
-- この行も、trx1 のステートメントによってロックされているためブロックされます。条件を満たすすべてのステートメントがロックされます。 update user set age = 10 where address = "address3";
--セッション1
専念;
--セッション2
専念;

-- 非一意インデックス + RR
--セッション1
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
年齢が 20 のユーザーから削除します。
--セッション2
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
-- age=20 のレコードが trx1 でロックされ、GAP ロックが追加されているため、18 がロック間隔に該当するため、ブロックが発生します。 insert into user (email, age, address) values ​​("[email protected]", 18, "address4");
--セッション1
専念;
--セッション2
専念;

-- インデックスなし RR
--セッション1
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
-- インデックスがない場合、テーブル内のすべてのレコードがロックされ、主キー インデックスのすべてのギャップがロックされて、すべての同時更新操作が防止されます。delete from user where address = "address3";
--セッション2
セッショントランザクション分離レベルを繰り返し読み取りに設定します。
始める;
-- 主キーに GAP ロックが設定されているため、新しい挿入を正常に実行できず、ブロックされます。 insert into user (email, age, address) values ​​("[email protected]", 18, "address4");
--セッション1
専念;
--セッション2
専念;

-- 単純なデッドロックの例 -- セッション 1
始める;
ID = 1 のユーザーから削除します。
--セッション2
始める;
ID = 3 のユーザーから削除します。
--セッション1
ID = 3 のユーザーから削除します。
--セッション2
-- ここでMySQLはデッドロックが発生したと判断し、トランザクションを中断します。
-- エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再開してください。
ID = 1 のユーザーから削除します。
--セッション1
ロールバック;
--セッション2;
ロールバック;

-- 5. デッドロック挿入例 drop table if exists t1;
始める;
テーブルt1を作成（
 `id` bigint NULLではない auto_increment、
 主キー (`id`)
);
t1値に挿入する(1);
t1値に挿入する(5);
専念;
t1から*を選択します。
--セッション1
始める;
t1値に挿入する（2）
--セッション2
始める;
-- これにより、t1値への挿入がブロックされます（2）。
--セッション3
始める;
-- これにより、t1値への挿入がブロックされます（2）。
--セッション1;
-- この時点でロールバックが発生し、trx2 と trx3 が通知を受け取り、デッドロックが発生したため MySQL は自動的に 1 つの trx を中断します -- エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください
ロールバック;
--セッション2;
ロールバック;
--セッション3;
ロールバック;