更新SQL文に基づくMySQLロックの理解

更新SQL文に基づくMySQLロックの理解

序文

MySQL データベース ロックは、データの一貫性を実現し、同時実行性の問題を解決するための重要な手段です。データベースは、複数のユーザーが共有するリソースです。同時実行が発生すると、さまざまな奇妙な問題が発生します。プログラム コードと同様に、マルチスレッド同時実行が発生した場合、特別な制御を行わないと、「ダーティ」データ、変更の損失などの予期しない問題が発生します。したがって、データベースの同時実行性はトランザクションを使用して制御する必要があり、トランザクションの同時実行性の問題はデータベース ロックを使用して制御する必要があるため、データベース ロックは同時実行性制御とトランザクションに関連しています。

この記事では主に、更新 SQL ステートメントに基づく MySQL ロックの理解について説明します。詳しい紹介を見てみましょう。

1. 地殻環境

(root@localhost) [user]> 'version' のような変数を表示します。
+---------------+------------+
| 変数名 | 値 |
+---------------+------------+
| バージョン | 5.7.23-log |
+---------------+------------+

(root@localhost) [ユーザー]> desc t1;
+-------------+--------------+------+-----+---------+----------------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | 自動増分 |
| n | int(11) | はい | | NULL | |
| テーブル名 | varchar(64) | はい | | NULL | |
| 列名 | varchar(64) | はい | | NULL | |
| パッド | varchar(100) | はい | | NULL | |
+-------------+--------------+------+-----+---------+----------------+

(root@localhost) [ユーザー]> t1からcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 3406 |
+----------+

(root@localhost) [user]> t1(pad) に一意のインデックス idx_t1_pad を作成します。
クエリは正常、影響を受けた行は 0 行 (0.35 秒)
レコード: 0 重複: 0 警告: 0

(root@localhost) [ユーザー]> t1(n) にインデックス idx_t1_n を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)
レコード: 0 重複: 0 警告: 0
(root@localhost) [user]> t1 からのインデックスを表示します。
+-------+------------+------------+--------------+---------------+------------+------------+------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | Null | インデックス タイプ |
+-------+------------+------------+--------------+---------------+------------+------------+------------+
| t1 | 0 | プライマリ | 1 | id | A | 3462 | | BTREE |
| t1 | 0 | idx_t1_pad | 1 | パッド | A | 3406 | はい | BTREE |
| t1 | 1 | idx_t1_n | 1 | n | A | 12 | はい | BTREE |
+-------+------------+------------+--------------+---------------+------------+------------+------------+
「Leshami」著者、「http://blog.csdn.net/leshami」ブログを選択します。
+---------+------------------------------+
| 著者 | ブログ |
+---------+------------------------------+
| レシャミ | http://blog.csdn.net/leshami |
+---------+------------------------------+

2. 主キーに基づいて更新する

(root@localhost) [user]> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

(root@localhost) [ユーザー]> update t1 set table_name='t1' where id=1299;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

trx_idを選択します。
 trx_state、
 trx_started、
 trx_mysql_thread_id、
 trx_tables_locked、
 trx_rows_locked、
 trx_rows_modified、
 trx_分離レベル
INFORMATION_SCHEMA.INNODB_TRX から \G

-- 以下の結果から、trx_rows_locked で行がロックされていることがわかります ***************************** 1. 行 ****************************
 トランザクションID: 6349647
 trx_state: 実行中
 開始日時: 2018-11-06 16:54:12
trx_mysql_スレッドID: 2
 trx_tables_locked: 1
 ロックされた行数: 1
 trx_rows_modified: 1
trx_isolation_level: 繰り返し読み取り 

(root@localhost) [ユーザー]> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

3. セカンダリユニークインデックスに基づく

(root@localhost) [user]> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

(root@localhost) [ユーザー]> t1 を更新し、table_name='t2' を設定し、pad='4f39e2a03df3ab94b9f6a48c4aecdc0b' を設定します。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

trx_idを選択します。
 trx_state、
 trx_started、
 trx_mysql_thread_id、
 trx_tables_locked、
 trx_rows_locked、
 trx_rows_modified、
 trx_分離レベル
INFORMATION_SCHEMA.INNODB_TRX から \G

-- 以下のクエリ結果から、trx_rows_locked で 2 行がロックされていることがわかります ***************************** 1. 行 ****************************
 トランザクションID: 6349649
 trx_state: 実行中
 開始日時: 2018-11-06 16:55:22
trx_mysql_スレッドID: 2
 trx_tables_locked: 1
 ロックされた行数: 2
 trx_rows_modified: 1
trx_isolation_level: 繰り返し読み取り 

(root@localhost) [ユーザー]> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

3. 二次非一意インデックスに基づく

(root@localhost) [user]> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

(root@localhost) [ユーザー]> update t1 set table_name='t3' where n=8;
クエリは正常、350 行が影響を受けました (0.01 秒)
一致した行: 351 変更された行: 351 警告: 0

trx_idを選択します。
 trx_state、
 trx_started、
 trx_mysql_thread_id、
 trx_tables_locked、
 trx_rows_locked、
 trx_rows_modified、
 trx_分離レベル
INFORMATION_SCHEMA.INNODB_TRX から \G
 
--以下のクエリ結果から、703 行がロックされていることがわかります ******************************** 1. 行 ****************************
  トランザクションID: 6349672
  trx_state: 実行中
 開始日時: 2018-11-06 17:06:53
trx_mysql_スレッドID: 2
 trx_tables_locked: 1
 ロックされた行数: 703
 変更された行数: 351
trx_isolation_level: 繰り返し読み取り

(root@localhost) [ユーザー]> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

4. インデックスなしで更新する

(root@localhost) [user]> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

(root@localhost) [ユーザー]> update t1 set table_name='t4' where column_name='id';
クエリは正常、26 行が影響を受けました (0.00 秒)
一致した行: 26 変更された行: 26 警告: 0

trx_idを選択します。
 trx_state、
 trx_started、
 trx_mysql_thread_id、
 trx_tables_locked、
 trx_rows_locked、
 trx_rows_modified、
 trx_分離レベル
INFORMATION_SCHEMA.INNODB_TRX から \G

-- 以下のクエリ結果から、trx_rows_locked では 3429 行がロックされており、26 行のみが更新されていることがわかります。この結果は、テーブルの合計行数 3406 を超えています。
************************** 1. 行 ****************************
  トランザクションID: 6349674
  trx_state: 実行中
 開始日時: 2018-11-06 17:09:41
trx_mysql_スレッドID: 2
 trx_tables_locked: 1
 ロックされた行数: 3429
 trx_rows_modified: 26
trx_isolation_level: 繰り返し読み取り

(root@localhost) [ユーザー]> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

-- show engine innodb status show engine innodb status\G でも確認することができます。

------------
取引
------------
Trx ID カウンター 6349584
トランザクションのパージが完了しました。n:o < 0 を元に戻します。n:o < 0 状態: 実行中ですがアイドル状態です
履歴リストの長さ 0
各セッションのトランザクションのリスト:
---トランザクション 421943222819552、開始されていません
ロック構造体 0 個、ヒープ サイズ 1136、行ロック 0 個
---トランザクション 6349583、アクティブ 2 秒
ロック構造体 2 個、ヒープ サイズ 1136、行ロック 1 個、UNDO ログ エントリ 1


------------
取引
------------
Trx ID カウンター 6349586
トランザクション番号 6349585 のパージが完了しました。元に戻す番号 0 状態: 実行中ですがアイドル状態です
履歴リストの長さ 1
各セッションのトランザクションのリスト:
---トランザクション 421943222819552、開始されていません
ロック構造体 0 個、ヒープ サイズ 1136、行ロック 0 個
---トランザクション 6349585、アクティブ 8 秒
ロック構造体 3 個、ヒープ サイズ 1136、行ロック 2 個、UNDO ログ エントリ 1
MySQL スレッド ID 2、OS スレッド ハンドル 140467640694528、クエリ ID 29 localhost root

5. ロック関連のクエリSQL

1: 現在の取引を表示する

INFORMATION_SCHEMA.INNODB_TRX から * を選択します。

2: 現在ロックされているトランザクションを表示する

INFORMATION_SCHEMA.INNODB_LOCKS から * を選択します。

3: ロックを待機している現在のトランザクションを表示する

INFORMATION_SCHEMA.INNODB_LOCK_WAITS から * を選択します。

trx_idを選択します。
 trx_state、
 trx_started、
 trx_mysql_thread_id thr_id、
 trx_tables_locked tb_lck、
 trx_rows_locked 行_lck、
 trx_rows_modified row_mfy、
 trx_isolation_level は _lvl です
INFORMATION_SCHEMA.INNODB_TRX から;

r.`trx_id` 待機中のtrx_idを選択します。
 r.`trx_mysql_thread_id` 待機スレッド、
 r.`trx_query` 待機クエリ、
 b.`trx_id` bolcking_trx_id、
 b.`trx_mysql_thread_id` ブロッキングスレッド、
 b.`trx_query` ブロッククエリ
information_schema.`INNODB_LOCK_WAITS` から w
 INNER JOIN 情報スキーマ.`INNODB_TRX` b
 オン b.`trx_id` = w.`blocking_trx_id`
 INNER JOIN 情報スキーマ.`INNODB_TRX` r
 ON r.`trx_id` = w.`requesting_trx_id`;

VI. 要約

1. MySQL テーブルが更新されると、更新中の where 述語条件に従ってレコード ロックが決定されます。

2. クラスター化インデックス フィルタリングの場合、インデックスがデータであるため、更新行のみがロックされます。これは、クラスター化インデックスの性質によって決まります。

3. 非クラスター化ユニークインデックスフィルタリングの場合、テーブルに戻す必要があるため、ユニークインデックスでフィルタリングされた行数とテーブルに戻される行数の合計がロックされます。

4. 非クラスター化非ユニークインデックスフィルタリングではギャップロックが関係するため、より多くのレコードがロックされます。

5. フィルタ条件にインデックスがない場合、またはインデックスを使用できない場合は、テーブル全体のすべてのデータ行がロックされます。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません:
  • Mysql クロステーブル更新マルチテーブル更新 SQL ステートメントの概要
  • MySQL ログを通じて実行ステートメントと更新ログをリアルタイムで表示するチュートリアル
  • SQL UPDATE 更新ステートメントの使用法 (単一列と複数列)
  • JavaリフレクションJavaBeanオブジェクトは、挿入、更新、削除、クエリSQLステートメント操作を自動的に生成します。
  • MySQLのUPDATE文の落とし穴を記録する
  • SQL 更新ステートメントでの使用からの更新セットの実装
  • SQL更新文の実行プロセスの分析

<<:  nginx が複数のプロキシ層を通過して実際の送信元 IP を取得するプロセスの詳細な説明

>>:  jQueryは居住地を選択するためのドロップダウンボックスを実装します

推薦する

Dockerはローカルイメージをパッケージ化し、他のマシンに復元します

1. docker imagesを使用して、このマシン上のすべてのイメージファイルを表示します。 2...

MySQL 5.7.20 共通ダウンロード、インストール、設定方法と簡単な操作スキル(解凍版無料インストール)

早朝に MySQL 5.7.19 のインストールを終えたばかりですが、午前中に MySQL が最新バ...

H5でクリックされたときにaタグの背景色をキャンセルする方法

1. モバイル端末でクリックされたときにタグの青色を解除する { -webkit-tap-highl...

Windows で削除された MySQL 8.0.17 のルート アカウントとパスワードを回復する方法

少し前にSQLの独学を終え、MySQL 8.0.17をダウンロードしました。インストールして設定した...

CSS3で実装された水平ヘッダーメニュー

結果:実装コードhtml <nav class="dropdownmenu"...

Web 開発 js 文字列連結プレースホルダーと conlose オブジェクト API の詳細な説明

目次プレースホルダーの置き換えコンソール印刷テーブル()ログ、情報、警告、エラーグループ()、グルー...

W3C チュートリアル (7): W3C XSL アクティビティ

スタイル シートは、ドキュメントの表示方法、発音方法、または入力方法を記述します。XSL 言語は、X...

Angularの動的コンポーネントの詳細な説明

目次使用シナリオ達成方法1. 動的コンポーネントを配置する場所2. コンポーネントのインスタンスを取...

Linux 負荷分散 LVS の詳細な理解

目次1. LVS 負荷分散2. 負荷分散LVSの基本紹介3. LVSアーキテクチャ3.1 ロードバラ...

MySQL データベース 8 - データベース内の関数の適用の詳細な説明

データベースの組み込み関数の使用この記事では、主に日付関数、文字列関数、数学関数など、データベースの...

vueはEle.me UIを使用してteambitionのフィルタリング機能を模倣します

目次問題の説明一般的な機能効果は次のとおりです。思考分析完全なコード要約する問題の説明Teambit...

Linux の MariaDB データベースについて

目次Linux の MariaDB データベースについて1. データベースとは何ですか? 2. デー...

MySqlを最適化するためにnot inを使用する方法

最近、プロジェクトで選択クエリを使用する際に、未使用の主キー ID を除外するために not in ...

layui をベースにしたログインページの実装

この記事の例では、ログインページを実装するためのlayuiの具体的なコードを参考までに共有しています...

Docker を使用した Laravel アプリケーションのデプロイ例

この記事で使用されているPHPベースイメージはphp:7.3-apacheです。この記事の Lara...