====== MariaDB10.4 トランザクション ======
phpMyAdmin 4.9.0.1 (MariaDB 10.4.6)

 --- //[[http://www.y2sunlight.com|y2sunlight]] 2019-10-15//

[[mariadb:top|MariaDBに戻る]]

関連記事
  * [[mariadb:10.4:phpmyadmin|MariaDB10.4 phpMyAdminの使い方]]
  * [[mariadb:10.4:mysqli|MariaDB10.4 PHPサンプルプログラム]]
  * [[mariadb:10.4:system-variables|MariaDB10.4 システム変数の参照と変更]]
  * [[mariadb:10.4:server-info|MariaDB10.4 サーバ情報の取得]]
  * MariaDB10.4 トランザクション

以下「MySQL」は「MariaDB」に読み替えて下さい。

----

===== 概要 =====

MySQLではトランザクションセーフなテーブル（InnoDBなど）と非トランザクションセーフなテーブル(MyISAMなど)を作る事ができます。手動トランザクションが使えるのはトランザクションセーフなテーブルだけです。 MyISAMなどの非トランザクションセーフなテーブルの場合は、データベースの変更は直ちにコミットされます。

本章ではストレージエンジンとしてInnoDBを使ったテーブルを対象として、トランザクションについて説明します。InnoDBでは、以下のトランザクション分離レベルをサポートしています：

  * READ UNCOMMITTED --- 非コミット読み取り
  * READ COMMITTED --- コミット済み読み取り
  * REPEATABLE READ --- 再読み込み可能読み取り
  * SERIALIZABLE --- 直列化

ここでは、これらの分離レベルの違いをサンプルプログラムを使用して実感しながら説明したいと思います。

\\

===== MySQLのトランザクション =====

MySQLはデフォルトで自動トランザクション機能（自動コミットモード）が有効です。本編ではMySQLiを使っているので、プログラムの途中で自動コミットモードを切り替える場合は、次のようにします。

<code>
$mysqli->autocommit(true);  /* AUTOCOMMIT 有効 */
$mysqli->autocommit(false); /* AUTOCOMMIT 無効 */
</code>

MySQLではautocommit変数を使って自動コミットモードを切り替える事ができます。

<code sql>
SET autocommit=1; /* 有効 */
SET autocommit=0; /* 無効 */
</code>

接続中のセッションでautocommit変数の値を確認するには、以下をSQL文を実行します。

<code sql>
SELECT @@autocommit;
</code>

一旦、自動コミットモードを無効にした後は、トランザクションをコミットまたはロールバックする必要があります。新しいトランザクションはコミットまたはロールバックの直後から開始されます。また、autocommit変数をfalseからtrueに変更すると暗黙のコミットが発生します。これは$mysqli->autocommit()の場合も同様です。

自動コミットモードが有効な場合でも、一連のデータベースの更新に対して自動コミットモードを無効にする事ができます。この場合、コミットまたはロールバックが発生すると再び自動コミットモードは有効になります。MySQLのSQL文では次のようにします。

<code sql>
START TRANSACTION; /* トランザクションの開始 */
COMMIT;            /* コミットしてトランザクションを終了 */
ROLLBACK;          /* ロールバックしてトランザクションを終了 */
</code>

尚、MySQLではトランザクションの開始をBEGIN またはBEGIN WORK文でもできます。同じ事をMySQLiで行うには次のようにします。

<code>
$mysqli->begin_transaction();
$mysqli->commit();
$mysqli->rollback();
</code>

手動トランザクション（自動コミットモード無効）を使う上での注意点は、暗黙のコミットが発生するSQL文や、ロールバックできないSQL文が存在する事です。当然の事ですがこの中にはINSERT/UPDATE/DELETEは含まれません。トランザクションやロックを制御するSQL文またはDDL系のSQL文を使用する場合は注意が必要です。詳細については以下を参照して下さい。

> [[https://mariadb.com/kb/en/library/start-transaction/]]

InnoDBではSQL92で定義されている4つのトランザクション分離レベルを全て提供しています。デフォルトの分離レベルREPEATABLE READです。デフォルトの分離レベルはオプションファイル(my.ini)で変更することができます。

{{fa>file-o}} ''{XAMPP Install Folder}/mysql/bin/my.ini''
<code ini my.ini>
[mysqld]
transaction-isolation = REPEATABLE-READ
</code>

SQL文で分離レベルを変更するには以下のようにします。

<code sql>
SET TRANSACTION ISOLATION LEVEL {分離レベル};
{分離レベル}=[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
</code>

READ UNCOMMITTEDでは非コミットリードが、READ COMMITEDではコミットリードができますが、REPEATABLE READではファントムリードができません。詳細については以下のURLを参照して下さい。

> [[https://mariadb.com/kb/en/library/set-transaction/]]

\\

===== SQL文による手動トランザクション =====

ブラウザからサンプルプログラム(sqlfile.php)を実行します。

<code>
http://localhost/mysql/sqlfile.php?f=transaction1a.sql
</code>

<file sql transaction1a.sql>
-- テーブル作成 -------------------------
DROP TABLE IF EXISTS syain;
CREATE TABLE syain (
  syain_no int(10) NOT NULL,
  syain_name varchar(50),
  syain_age int(10),
  PRIMARY KEY (syain_no)
);
INSERT INTO syain VALUES(1,'Suzuki',50);
#;
-- トランザクション開始 ------------------
-- SET AUTOCOMMIT = 0;
START TRANSACTION;
SELECT * FROM syain;

-- テーブルの更新
UPDATE syain SET syain_age = syain_age + 1;
INSERT INTO syain VALUES(2,'Yamamoto',30);
SELECT * FROM syain;

-- トランザクション終了 -----------------
ROLLBACK;
SELECT * FROM syain;
</file>

10行目の ''#;'' だけの行はブラウザ上で改行する為に使用しています。誤りではありません。

実行結果

[{{:mariadb:10.4:transaction1a.png|}}]

この例では、START TRANSACTION文でトランザクションを開始して、データ変更後、トランザクションをROLLBACK文でロールバックしています。トランザクション終了後は再び自動コミットモードが有効になります。

START TRANSACTION文の代わりにSET AUTOCOMMIT = 0 でトランザクションを開始する事もできます。但し、この場合は、トランザクション終了後も自動コミットモードは無効なので、新しい手動トランザクションが暗黙的に開始されます。

この例で、START TRANSACTION文を1行目に移動して実行すると、奇妙な現象が起こります。ロールバックが効かなくなるのです。これは、DROP TABLE文により暗黙のコミットが発生してトランザクションが終了している為です。それ以降は自動コミットモードになるのでROLLBACK文は無効になります。この現象はDROP TABLEやCREATE TABLEなどのDDL文の場合に発生します。トランザクションを設計する場合は注意する必要があります。

\\

===== MySQLi による手動トランザクション =====

ブラウザからサンプルプログラム(sqlfile.php)を実行します。

<code>
http://localhost/mysql/sqlfile.php?f=transaction1b.sql
</code>

<file sql transaction1b.sql>
-- テーブル作成 -------------------------
DROP TABLE IF EXISTS syain;
CREATE TABLE syain (
  syain_no int(10) NOT NULL,
  syain_name varchar(50),
  syain_age int(10),
  PRIMARY KEY (syain_no)
);
INSERT INTO syain VALUES(1,'Suzuki',50);
#;

-- トランザクション開始 ------------------
-- EVAL $mysqli->autocommit();
EVAL $mysqli->begin_transaction();
SELECT * FROM syain;

-- テーブルの更新
UPDATE syain SET syain_age = syain_age + 1;
INSERT INTO syain VALUES(2,'Yamamoto',30);
SELECT * FROM syain;

-- トランザクション終了 -----------------
EVAL $mysqli->rollback();
SELECT * FROM syain;
</file>

上のスクリプトファイルにはEVAL文が含まれています。EVALに続く式（例えば：$mysqli->autocommit()）はPHPのeval関数に引き渡されます。$mysqli変数は sqlfile.php の DoSqlScript()関数のローカルコープ内で使用できる変数です。[[mariadb:10.4:mysqli#サンプルプログラム|サンプルプログラム]]を参照して下さい。

=== 実行結果 ===

[{{:mariadb:10.4:transaction1b.png|}}]

この例では、MySQLi の begin_transaction メソッドでトランザクションを開始しrollback メソッドを使用してトランザクションをロールバックしています。

begin_transaction メソッドの代わりにAutoCommit を無効に設定 （ $mysqli->autocommit(false) ） してもトランザクションを開始する事ができます。但し、この場合は、トランザクション終了後も自動コミットモードは無効なので、新しい手動トランザクションが暗黙的に開始されます。また、begin_workメソッドを1行目に移動して実行すると、先の例「[[#SQL文による手動トランザクション]]」と同じにはなります。

MySQLiを利用してデータベースをアクセスする場合、移植性による理由から、begin_transaction/commit/rollbackメッソドを使用する場合が多いと思います。暗黙のコミットを発行するSQL文には十分な注意が必要です。

\\

===== トランザクションの分離レベル =====

MySQLは4つのトランザクション分離レベルを提供しています。SQL92では、これらの分離レベルは下表のように説明されています。しかし、**MySQLではREPEATABLE READレベルでファントムリードが発生しません**。

SQL92のトランザクション分離レベル
^  ^ ダーティリード ^ ノン・リピータブルリード ^ ファントムリード ^
^ READ UNCOMMITTED | 発生する | 発生する | 発生する |
^ READ COMMITTED | 発生しない | 発生する | 発生する |
^ REPEATABLE READ | 発生しない | 発生しない | 発生する |
^ SERIALIZABLE | 発生しない | 発生しない | 発生しない |

> ダーティリード：非コミットデータを読み込む事
> ノン・リピータブルリード：同じSELECT文で、同じ行に対する検索結果が1回目と２回目で違う事
> ファントム(幻影)リード：同じSELECT文で、１回目の検索に含まれない行が２回目の検索に含まれる事

以下では、MySQLのトランザクションの分離レベルに関する実験を行います。この実験では２種類のSQLスクリプトファイルを使用します。それらはsyainテーブルに関する照会系と更新系のトランザクションです。以下に照会系のトランザクション（transaction2a.sql）を示します。

<file sql transaction2a.sql>
-- テーブル作成 -------------------------
DROP TABLE IF EXISTS syain;
CREATE TABLE syain (
  syain_no int(10) NOT NULL,
  syain_name varchar(50),
  syain_age int(10),
  PRIMARY KEY (syain_no)
);
INSERT INTO syain VALUES(1,'Suzuki',50);
#;

-- トランザクション開始 ------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;
EVAL # トランザクションA開始;
SELECT * FROM syain;

EVAL # ここで他のトランザクションがデータを更新する;
EVAL sleep(5);
SELECT * FROM syain;
SELECT * FROM syain LOCK IN SHARE MODE;

-- トランザクション終了 -----------------
COMMIT;
EVAL # トランザクションA終了;

EVAL sleep(5);
SELECT * FROM syain;
</file>

上のSQLスクリプトでは、テーブルを作成後、トランザクションAを開始します。トランザクションAの中では、最初に、単純SELECT文を実行した後、5秒間sleepします。次に、２種類のSELECT文(単純SELECTと共有ロックモードでのSELECT)を実行し、トランザクションAを終了します。最後に、5秒間sleepして再度SELECT文を実行します。

このスクリプトは４つのSELECT文を実行します。最初の3つのSELECT文が同じトランザクションAの中で実行されている点に注意して下さい。第１のSELECT文とそれに続く２つのSELECT文の間に５秒の待機時間を設けます。この待機時間の間に他のトランザクションによるデータ更新操作が入ります。

尚、MySQLのSELECT文では３種類のロック操作ができます。

  * 単純SELECT文 --- ロックを取得しません
  * SELECT FOR UPDATE文 --------- 行レベルの排他ロックモードでSELECT文を実行します
  * SELECT LOCK IN SHARE MODE --- 行レベルの共有ロックモードでSELECT文を実行します

> 排他ロック：他のトランザクションからはデータの参照も更新もできません
> 共有ロック：他のトランザクションからはデータの参照はできますが、更新はできません

\\

==== READ UNCOMMITTED ====

transaction2a.sql のトランザクション分離レベルをREAD UNCOMMITTEDに設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル（transaction2b.sql）を準備します。

<file sql transaction2b.sql>
-- トランザクション開始 ------------------
START TRANSACTION;
EVAL #トランザクションB開始;

-- テーブルの更新
INSERT INTO syain VALUES(2,'Yamamoto',30);
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;

EVAL sleep(5);

-- トランザクション終了 -----------------
ROLLBACK;
EVAL #トランザクションB終了;
</file>

このスクリプトでは、トランザクションB開始してsyainテーブルを更新後、５秒待って、トランザクションBをロールバックします。READ UNCOMMITTEDで実行しているトランザクションAはこの５秒間の間にダーティリードを行う事になります。

以下の順でSQLスクリプトを実行します。2番目のスクリプトは５秒以内に実行します。

  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2a.sql</nowiki>''
  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2b.sql</nowiki>''

=== 実行結果 ===

[{{:mariadb:10.4:transaction2b1.png|}}]

２つ目の単純SELECT文でダーティリードが発生しています。一方、３つ目のSELECT文は共有ロックモード(LOCK IN SHARE MODE)で実行しているのでダーティリードは起こっていません。共有ロックモードのSELECT文は、更新系トランザクションの終了(transaction2b.sql内のROLLBACK文)を待ってから検索結果を返しています。これは、transaction2b.sqlのINSTER及びUPDATE文で排他ロックが設定されているためです。

同様の実験を、transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定して行ないます。

=== 実行結果 ===

[{{:mariadb:10.4:transaction2b2.png|}}]

２つ目の単純SELECT文ではダーティリードが発生しません。また、SELECT文が更新系トランザクションの終了を待たない点にも注目して下さい。これは、MySQLの読み取り一貫性がロックを取得しないで実現されているからです。この分離レベルではSELECTされた時点の最新のコミット済みスナップショットが保持されます。

\\

==== READ COMMITTED ====

transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル（transaction2c.sql）を準備します。

<file sql transaction2c.sql>
-- 自動コミットモード -------------------
-- レコード挿入
INSERT INTO syain VALUES(2,'Yamamoto',30);
SELECT * FROM syain;

-- レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;
</file>

このスクリプトはトランザクションを開始していないので自動コミットモードで作動し、２つのトランザクションがあります。最初のトランザクションはINSERTを、次のトランザクションはUPDATE文を実行しています。

以下の順でSQLスクリプトを実行します。2番目のスクリプトは５秒以内に実行します。

  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2a.sql</nowiki>''
  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2c.sql</nowiki>''

=== 実行結果 ===

[{{:mariadb:10.4:transaction2b3.png|}}]

トランザクションAの２つ目の単純SELECT文でノン・リピータブルリードとファントムリードが発生しています。

> ノン・リピータブルリード：１つ目の検索結果ではsyain_no=1に対するsyain_ageは50です
> ファントムリード：１つ目の検索結果にはsyain_no=2はありません

この例では、３つ目の（共有ロックを取得する）SELECT文の検索結果が、単純SELECT文の場合と同じです。両者の違いは次の実験で明らかになります。

\\

==== REPEATABLE READ ====

transaction2a.sql のトランザクション分離レベルを**REPEATABLE READ**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。

<file sql transaction2c.sql>
-- 自動コミットモード -------------------
-- レコード挿入
INSERT INTO syain VALUES(2,'Yamamoto',30);
SELECT * FROM syain;

-- レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;
</file>

以下の順でSQLスクリプトを実行します。2番目のスクリプトは５秒以内に実行します。

  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2a.sql</nowiki>''
  - ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2c.sql</nowiki>''

=== 実行結果 ===

[{{:mariadb:10.4:transaction2b4.png|}}]

２つ目の単純SELECT文ではノン・リピータブルリードもファントムリードも発生していません。この分離レベル（REPEATABLE READ）は、MySQLのデフォルトの分離レベルで、単純SELECT文を使用した場合でも、同一トランザクション内での読み取りの一貫性が保障されています。

このレベルでの単純SELECT文は、READ COMMITTEDの場合と同様にロックは取得されません。読み取り一貫性については、READ COMMITTEDの場合は、SELECT時の最新のスナップショットが保持されますが、 REPEATABLE READの場合は最初に読み取ったスナップショットが保持され、以降の同一トランザクション内での読み取りに使われます。

上の実行結果で、３つ目の（共有ロックを取得する）SELECT文の検索結果を見てください。この検索結果は前の例題（READ COMMITTED）の場合と同じです。ロックを取得するSELECT文を実行した場合は、最初に読み取ったスナップショットではなく、最新のデータを読み取っているようです。

\\

==== SERIALIZABLE ====

transaction2a.sql のトランザクション分離レベルを**SERIALIZABLE**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。

<file sql transaction2c.sql>
-- 自動コミットモード -------------------
-- レコード挿入
INSERT INTO syain VALUES(2,'Yamamoto',30);
SELECT * FROM syain;

-- レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;
</file>

ブラウザから ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2a.sql</nowiki>'' を実行し、直ぐに(５秒以内)、別のブラウザから ''<nowiki>http://localhost/mysql/sqlfile.php?f=transaction2c.sql</nowiki>'' を実行します。

=== 実行結果 ===

[{{:mariadb:10.4:transaction2b5.png|}}]

transaction2c.sqlを実行すると、INSERT文の後でブラウザの出力が一時的に（５秒間）止まります。この現象は、transaction2a.sqlの１つ目の単純SELECT文が共有ロックを取得（LOCK IN SHARE MODE）しているからです。この共有ロックの為にINSERTやUPDATE文などの排他ロックを取得する操作は待機状態になります。

このレベル（SERIALIZABLE）の単純SELECT文は暗黙の共有ロックモード（LOCK IN SHARE MODE）で実行されます。SERIALIZABLEレベルのトランザクションは分離レベルが最も高く、他のトランザクションの影響を受けないように設計されています。但し、この実行例（排他ロックを取得する操作が待機状態になる事）からも分かるように、トランザクションの同時実行性は最も低くなります。

\\