この記事では、例を使用して、MySQL トランザクション、分離レベル、およびロックの使用について説明します。ご参考までに、詳細は以下の通りです。

トランザクションとは、一緒に成功または失敗する SQL ステートメントのグループです。トランザクションには、原子性、一貫性、独立性、および永続性も必要です。

1. トランザクションの基本要素（ACID）

1. 原子性: トランザクションの開始後、すべての操作は完全に成功するか、完全に失敗します。中間状態になる可能性はありません。トランザクションは、原子のように分割できない全体です。

2. 一貫性: トランザクションの開始前と終了後に、データベースの整合性制約に違反しません。A が B に送金し、A がお金を差し引きますが、B はそれを受け取りません。

3. 独立性: 同時に発生するトランザクション (同時トランザクション) によって、データベースが不整合な状態になることがないようにする必要があります。各トランザクションは独立して実行され、他のトランザクションの存在には影響しません。

4. 永続性: トランザクションによってデータベースに加えられたすべての変更はディスクに保存され、失われることはありません。

2. トランザクションの同時実行性の問題

1. ダーティ リード: トランザクション A は、トランザクション B のコミットされていない書き込みデータを読み取ります。読み取られたデータはダーティ データと呼ばれます。

2. 反復不可能な読み取り: トランザクション A は同じデータを複数回読み取りますが、読み取りプロセス中にトランザクション B がデータを変更してコミットします。これにより、同じデータを複数回読み取ると、異なる結果になります。

3. ファントム リード: トランザクション A は、ステータス = 1 を設定するなど、テーブル内のすべてのデータ行を変更しますが、同時に、トランザクション B は、ステータス = 0 で新しいデータ行をテーブルに挿入します。トランザクション A を操作するユーザーにとっては、錯覚のように、テーブル内に変更されていないレコードがまだ存在します。

3. トランザクション分離の4つのレベル

4. データベース分離レベルの取得と設定

'%isolation%' のような変数を表示します。
'%isolation%' のようなグローバル変数を表示します。

システム変数を使用してクエリを実行する

@@GLOBAL.tx_isolation を選択します。
@@SESSION.tx_isolation を選択します。
@@tx_isolation を選択します。

mysql8の場合、次の変数を使用してクエリを実行します。

@@GLOBAL.transaction_isolation を選択します。
@@SESSION.transaction_isolation を選択します。
@@transaction_isolation を選択します。

分離レベルの設定

SET GLOBAL tx_isolation = '分離レベル';
SET SESSION tx_isolation = '分離レベル';
SET @@tx_isolation = '分離レベル';

mysql8の場合は、次の文を使用して設定します。

SET GLOBAL transaction_isolation = '分離レベル';
SET SESSION transaction_isolation = '分離レベル';
SET @@transaction_isolation = '分離レベル';

5. 例を通して分離レベルを説明する

まず、テーブルといくつかのデータを準備します。

テーブル「アカウント」を作成します（
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) デフォルト '' コメント 'name',
 `money` 小数点(11,2) デフォルト '0.00' コメント 'Money',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

`account` (`name`, `money`) VALUES ('A', '500.00') に INSERT INTO します。
`account` (`name`, `money`) に VALUES ('B', '100.00') を挿入します。

1. コミットされていないものを読む

transaction_isolation = 'READ-UNCOMMITTED' を設定します。
自動コミットを 0 に設定します。

トランザクション B はテーブル内のデータを変更しましたが、コミットしませんでした。トランザクション A は変更されたデータを読み取りました。何らかの理由でトランザクション B がロールバックされた場合、トランザクション A によって読み取られたデータはダーティ データになります。

2. 提出された内容を読む

transaction_isolation を 'READ-COMMITTED' に設定します。
自動コミットを 0 に設定します。

トランザクション B がデータを変更してもコミットしない場合、トランザクション A は元のデータを取得するため、ダーティ リードの問題が解決されます。

しかし、トランザクション B がコミットされると、トランザクション A は最後のクエリを実行し、その結果が前のクエリと矛盾し、反復不可能な読み取りの問題が発生します。

3. 繰り返し読む

transaction_isolation を 'REPEATABLE-READ' に設定します。
自動コミットを 0 に設定します。

トランザクション B はデータを変更し、コミットしました。トランザクション A による 2 つのクエリの結果は一貫しており、反復不可能な読み取りの問題が解決されました。

このとき、トランザクション A は A という名前のお金のデータを変更します。

A という名前のファイルの金額は 400 ではなく 350 になります。繰り返し読み取りにより、データの一貫性が保証されます。

トランザクション A ですべてのアカウントの金額を 200 に変更し、トランザクション B で新しいデータを挿入します。

トランザクション A は依然として 2 つのデータを取得するため、新しいデータが追加されたときにトランザクション A で発生するファントム リードの問題が解決されます。

4. シリアル化

transaction_isolation を 'SERIALIZABLE' に設定します。
自動コミットを 0 に設定します。

トランザクション A がテーブルをクエリします。コミットされていない場合、トランザクション B の挿入ステートメントは、タイムアウトになるかトランザクション A がコミットされるまでそこで待機します。

逆に、トランザクション B がテーブルにデータを挿入した後にコミットしない場合、トランザクション A はトランザクション B がコミットするまでテーブルに対するクエリを待機します。

このとき、テーブルの読み取りと書き込みの両方でテーブルがロックされるため、当然、同時実行パフォーマンスに大きな影響が出ます。

分離レベルが高いほど、データの整合性と一貫性が保証されます。

6. MySQL ロック

ロックには 2 つの種類があります。

内部ロック: 複数のセッションによるテーブルの内容の競合を管理するために、MySQL サーバーによって内部的に実行される内部ロック。

外部ロック: MySQL は、他のセッションがテーブルにアクセスできないようにするために、明示的にテーブル ロックを取得する機能をクライアント セッションに提供します。

内部ロックには 2 つのタイプがあります。

1. 行レベルのロック: 行レベルのロックはきめ細かく、アクセスされた行のみがロックされるため、複数のセッションが同時に書き込みアクセスを実行できます。

2. テーブル レベルのロック: MySQL は、MyISAM、Memory、および Merge テーブルにテーブル レベルのロックを使用し、一度に 1 つのセッションのみがテーブルを更新できるようにしています。これにより、これらのストレージ エンジンは読み取りベースの操作により適したものになります。

外部ロック: ロックは LOCK TABLE と UNLOCK TABLE を使用して制御できます。

READ (共有ロック): 複数のセッションがロックを取得せずにテーブルからデータを読み取ることができます。また、複数のセッションが同じテーブルに対してロックを取得することもできます。READ ロックが保持されている場合、どのセッションもテーブルにデータを書き込むことはできません。書き込み操作は、READ ロックが解除されるまで待機します。

WRITE (排他ロック): テーブルが WRITE によってロックされている場合、WRITE ロックが解除されない限り、ロックを保持しているセッション以外のセッションはデータを読み書きできません。

テーブルのロックステートメント:

LOCK TABLES table_name [READ | WRITE];

テーブルのロック解除ステートメント:

テーブルのロックを解除します。

データベース内のすべてのテーブルをロックします。

読み取りロック付きでテーブルをフラッシュします。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。