更新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は居住地を選択するためのドロップダウンボックスを実装します

推薦する

MySQL における explain の役割の詳細な説明

1. MYSQLインデックスインデックス: MySQL がデータを効率的に取得するのに役立つデータ構...

JavaでTomcatサーバーを起動/停止する方法

1. プロジェクト構造 2.Tomcat.javaを呼び出す パッケージ com.calltomca...

Centos7.4 サーバーへの Apache のインストールとインストール プロセス中に発生した問題の解決策

この記事では、CentOS 7.4 サーバーに Apache をインストールする方法と、インストール...

CentOS 7のインストールと設定方法のグラフィックチュートリアル

この記事は、CentOS 7の詳細なインストールチュートリアルを参考のために記録します。具体的な内容...

純粋な CSS3 で蝶が羽ばたく様子を再現する例

純粋なCSS3で蝶が羽ばたく様子を再現。まずはその効果をご覧ください どうですか?効果はかなりいいで...

MySQL インデックスに関するヒントのまとめ

目次1. インデックスの基礎知識1.1 インデックスの利点1.2 インデックスの有用性1.3 インデ...

HTML テーブルタグチュートリアル (32): セルの水平方向の配置属性 ALIGN

水平方向では、セルの配置を左、中央、右に設定できます。基本的な構文<TD ALIGN=&quo...

要素の読み込み効果を実現するための純粋なHTML+CSS

これは Element UI の読み込みコンポーネントのエフェクトです。かっこいいですね。実装してみ...

MySQL マスター スレーブ データベースが同期されない問題を解決する 2 つの方法

目次MySQL マスター スレーブ データベースが同期されない問題を解決する 2 つの方法1. 非同...

Windows システムでの MySQL 8.0.21 インストール チュートリアル (図とテキスト)

インストールの提案: インストールには .exe を使用せず、圧縮パッケージを使用してください。これ...

CSS スタイルの読み込みの優先順位に関する経験の共有

昨日のプロジェクト開発中に、スタイルの読み込み優先順位に関する問題が発生しました。クラスは定義され、...

動的な色切り替えの実装コードをサポートするために、CSS で SVG 画像を参照します。

表示する svg 画像を追加すると、React はファイルが見つからないというメッセージを表示します...

MySQL sql_modeの適切な設定に関する詳細な説明

MySQL sql_modeの適切な設定sql_mode は見落とされやすい変数です。デフォルト値は...

MySql8.0.19 インストールピットレコードを共有する

前回の記事ではMySql8.0.19のインストール手順を紹介しました。必要な方はクリックしてご覧くだ...