MySQL デッドロックルーチン: 一意のインデックスの下でのバッチ挿入順序の不一致

序文

デッドロックの本質はリソースの競合です。バッチ挿入の順序が一貫していないと、デッドロックに陥りやすくなります。この状況を分析してみましょう。デモンストレーションの便宜上、バッチ挿入は複数挿入として書き換えられます。

まずは小さな実験をいくつかやってみましょう。簡略化されたテーブル構造は次のようになります。

テーブル `t1` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` varchar(5)、
 `b` varchar(5)、
 主キー (`id`)、
 ユニークキー `uk_name` (`a`,`b`)
);

実験1:

レコードが存在しない場合に、同じ順序の 2 つのバッチ挿入が同時に実行されると、2 番目のバッチ挿入はロック待機状態になります。

t1	t2
始める;	始める;
ignore を t1(a, b)values("1", "1") に挿入します。		成功
	ignore を t1(a, b)values("1", "1") に挿入します。	ロック待機状態

現在のロック状態を確認できます

mysql> information_schema.innodb_locks から * を選択します。
+-------------+--------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+--------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+
| 31AE:54:4:2 | 31AE | S | レコード | `d1`.`t1` | `uk_name` | 54 | 4 | 2 | '1', '1' |
| 31AD:54:4:2 | 31AD | X | レコード | `d1`.`t1` | `uk_name` | 54 | 4 | 2 | '1', '1' |
+-------------+--------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+

トランザクション t1 の挿入を実行すると、ロックブレークポイントは表示されません。これは、MySQL 挿入の原理に関連しています。

挿入により暗黙的なロックが追加されます。暗黙のロックとは何ですか?暗黙のロックはロックがないことを意味する

t1 でレコードを挿入する場合、ロックは適用されません。この時点では、トランザクション t1 はまだコミットされていません。トランザクション t2 が挿入を試みると、このレコードが見つかります。t2 は S ロックを取得しようとし、レコードのトランザクション ID がアクティブかどうかを判断します。アクティブであれば、トランザクションが終了していないことを意味します。これにより、t1 は暗黙的なロックを明示的なロック (X ロック) にアップグレードできます。

ソースコードは以下のとおりです

t2 Sロック取得結果: DB_LOCK_WAIT

実験2:

一貫性のないバッチ挿入順序によるデッドロック

t1	t2
始める
t1(a, b)values("1", "1")に挿入します。		成功
	t1(a, b)values("2", "2")に挿入します。	成功
t1(a, b)values("2", "2")に挿入します。		t1はSロックを取得しようとし、t2の暗黙のロックを明示的なXロックにアップグレードし、DB_LOCK_WAITに入ります。
	t1(a, b)values("1", "1")に挿入します。	t2 は S ロックを取得しようとしますが、これにより t1 の暗黙的なロックが明示的な X ロックにアップグレードされ、デッドロックが発生します。

------------------------
最近検出されたデッドロック
------------------------
181101 9:48:36
*** （１）取引：
トランザクション 3309、アクティブ 215 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 3 ロック構造体、ヒープ サイズ 376、2 行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 2、OS スレッド ハンドル 0x70000a845000、クエリ ID 58 localhost ルート更新
t1(a, b)values("2", "2") に挿入
*** (1) このロックが許可されるのを待機しています:
レコード ロック スペース ID 55 ページ番号 4 n ビット 72 テーブル `d1`.`t1` のインデックス `uk_name` トランザクション ID 3309 ロック モード S 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 1; 16 進数 32; 昇順 2;;
 1: 長さ 1; 16 進数 32; 昇順 2;;
 2: 長さ 4; 16 進数 80000002; 昇順 ;;

*** （２）取引：
トランザクション 330A、アクティブ 163 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
3 つのロック構造体、ヒープ サイズ 376、2 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 3、OS スレッド ハンドル 0x70000a888000、クエリ ID 59 localhost ルート更新
t1(a, b)values("1", "1") に挿入
*** (2) ロックを保持する:
レコード ロック スペース ID 55 ページ番号 4 n ビット 72 テーブル `d1`.`t1` のインデックス `uk_name` trx ID 330A lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 3 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 1; 16 進数 32; 昇順 2;;
 1: 長さ 1; 16 進数 32; 昇順 2;;
 2: 長さ 4; 16 進数 80000002; 昇順 ;;

*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 55 ページ番号 4 n ビット 72 テーブル `d1`.`t1` のインデックス `uk_name` トランザクション ID 330A ロック モード S 待機中
レコード ロック、ヒープ番号 2 物理レコード: n_fields 3; コンパクト フォーマット; 情報ビット 0
 0: 長さ 1; 16 進数 31; 昇順 1;;
 1: 長さ 1; 16 進数 31; 昇順 1;;
 2: 長さ 4; 16 進数 80000001; 昇順 ;;

*** トランザクションをロールバックします (2)

このような問題をどうやって解決するのでしょうか?

考えられる解決策の 1 つは、アプリケーション層でソートした後にデータを挿入することです。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません: