このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン 前のリビジョン 次のリビジョン | 前のリビジョン | ||
mariadb:10.4:transaction [2020/10/20 14:44] y2sunlight [SQL文による手動トランザクション] |
mariadb:10.4:transaction [2020/11/20 09:55] (現在) y2sunlight |
||
---|---|---|---|
行 1: | 行 1: | ||
====== MariaDB10.4 トランザクション ====== | ====== MariaDB10.4 トランザクション ====== | ||
+ | phpMyAdmin 4.9.0.1 (MariaDB 10.4.6) | ||
+ | |||
--- // | --- // | ||
行 5: | 行 7: | ||
関連記事 | 関連記事 | ||
- | * [[xampp: | ||
* [[mariadb: | * [[mariadb: | ||
* [[mariadb: | * [[mariadb: | ||
行 12: | 行 13: | ||
* MariaDB10.4 トランザクション | * MariaDB10.4 トランザクション | ||
- | 以下「MySQL」は「MariaDB」に読み替えて下さい | + | 以下「MySQL」は「MariaDB」に読み替えて下さい。 |
---- | ---- | ||
- | MySQLではトランザクションセーフなテーブル(InnoDBなど)と非トランザクションセーフなテーブル(MyISAMなど)を作る事ができます。手動トランザクションが使えるのはトランザクションセーフなテーブルだけです。 MyISAMなどの非トランザクションセーフなテーブルの場合は、データベースの変更は直ちにコミットされます。本章ではストレージエンジンとしてInnoDBを使ったテーブルを対象とします。 | + | ===== 概要 ===== |
+ | |||
+ | MySQLではトランザクションセーフなテーブル(InnoDBなど)と非トランザクションセーフなテーブル(MyISAMなど)を作る事ができます。手動トランザクションが使えるのはトランザクションセーフなテーブルだけです。 MyISAMなどの非トランザクションセーフなテーブルの場合は、データベースの変更は直ちにコミットされます。 | ||
+ | |||
+ | 本章ではストレージエンジンとしてInnoDBを使ったテーブルを対象として、トランザクションについて説明します。InnoDBでは、以下のトランザクション分離レベルをサポートしています: | ||
+ | |||
+ | * READ UNCOMMITTED --- 非コミット読み取り | ||
+ | * READ COMMITTED --- コミット済み読み取り | ||
+ | * REPEATABLE READ --- 再読み込み可能読み取り | ||
+ | * SERIALIZABLE --- 直列化 | ||
+ | |||
+ | ここでは、これらの分離レベルの違いをサンプルプログラムを使用して実感しながら説明したいと思います。 | ||
+ | |||
+ | \\ | ||
- | ==== MySQLのトランザクション ==== | + | ===== MySQLのトランザクション |
MySQLはデフォルトで自動トランザクション機能(自動コミットモード)が有効です。本編ではMySQLiを使っているので、プログラムの途中で自動コミットモードを切り替える場合は、次のようにします。 | MySQLはデフォルトで自動トランザクション機能(自動コミットモード)が有効です。本編ではMySQLiを使っているので、プログラムの途中で自動コミットモードを切り替える場合は、次のようにします。 | ||
行 64: | 行 78: | ||
InnoDBではSQL92で定義されている4つのトランザクション分離レベルを全て提供しています。デフォルトの分離レベルREPEATABLE READです。デフォルトの分離レベルはオプションファイル(my.ini)で変更することができます。 | InnoDBではSQL92で定義されている4つのトランザクション分離レベルを全て提供しています。デフォルトの分離レベルREPEATABLE READです。デフォルトの分離レベルはオプションファイル(my.ini)で変更することができます。 | ||
- | < | + | {{fa> |
+ | < | ||
[mysqld] | [mysqld] | ||
transaction-isolation = REPEATABLE-READ | transaction-isolation = REPEATABLE-READ | ||
行 80: | 行 95: | ||
> [[https:// | > [[https:// | ||
- | ==== SQL文による手動トランザクション ==== | + | \\ |
+ | |||
+ | ===== SQL文による手動トランザクション ===== | ||
ブラウザからサンプルプログラム(sqlfile.php)を実行します。 | ブラウザからサンプルプログラム(sqlfile.php)を実行します。 | ||
行 97: | 行 115: | ||
); | ); | ||
INSERT INTO syain VALUES(1,' | INSERT INTO syain VALUES(1,' | ||
- | ; | + | #; |
-- トランザクション開始 ------------------ | -- トランザクション開始 ------------------ | ||
-- SET AUTOCOMMIT = 0; | -- SET AUTOCOMMIT = 0; | ||
行 113: | 行 131: | ||
</ | </ | ||
- | 10行目のセミコロン(;)だけの行はブラウザ上で改行する為に使用しています。誤りではありません。 | + | 10行目の |
実行結果 | 実行結果 | ||
行 125: | 行 143: | ||
この例で、START TRANSACTION文を1行目に移動して実行すると、奇妙な現象が起こります。ロールバックが効かなくなるのです。これは、DROP TABLE文により暗黙のコミットが発生してトランザクションが終了している為です。それ以降は自動コミットモードになるのでROLLBACK文は無効になります。この現象はDROP TABLEやCREATE TABLEなどのDDL文の場合に発生します。トランザクションを設計する場合は注意する必要があります。 | この例で、START TRANSACTION文を1行目に移動して実行すると、奇妙な現象が起こります。ロールバックが効かなくなるのです。これは、DROP TABLE文により暗黙のコミットが発生してトランザクションが終了している為です。それ以降は自動コミットモードになるのでROLLBACK文は無効になります。この現象はDROP TABLEやCREATE TABLEなどのDDL文の場合に発生します。トランザクションを設計する場合は注意する必要があります。 | ||
- | ==== MySQLi による手動トランザクション ==== | + | \\ |
+ | |||
+ | ===== MySQLi による手動トランザクション | ||
ブラウザからサンプルプログラム(sqlfile.php)を実行します。 | ブラウザからサンプルプログラム(sqlfile.php)を実行します。 | ||
行 133: | 行 153: | ||
</ | </ | ||
- | <file sql transaction1b.sql> | + | <file sql transaction1b.sql> |
+ | -- テーブル作成 ------------------------- | ||
DROP TABLE IF EXISTS syain; | DROP TABLE IF EXISTS syain; | ||
CREATE TABLE syain ( | CREATE TABLE syain ( | ||
行 142: | 行 163: | ||
); | ); | ||
INSERT INTO syain VALUES(1,' | INSERT INTO syain VALUES(1,' | ||
- | ; | + | #; |
- | #トランザクション開始 ------------------ | + | |
- | #EVAL $mysqli-> | + | -- トランザクション開始 ------------------ |
+ | -- EVAL $mysqli-> | ||
EVAL $mysqli-> | EVAL $mysqli-> | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | # テーブルの更新 | + | -- テーブルの更新 |
UPDATE syain SET syain_age = syain_age + 1; | UPDATE syain SET syain_age = syain_age + 1; | ||
INSERT INTO syain VALUES(2,' | INSERT INTO syain VALUES(2,' | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | # トランザクション終了 ----------------- | + | -- トランザクション終了 ----------------- |
EVAL $mysqli-> | EVAL $mysqli-> | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
行 160: | 行 182: | ||
上のスクリプトファイルにはEVAL文が含まれています。EVALに続く式(例えば:$mysqli-> | 上のスクリプトファイルにはEVAL文が含まれています。EVALに続く式(例えば:$mysqli-> | ||
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 170: | 行 192: | ||
MySQLiを利用してデータベースをアクセスする場合、移植性による理由から、begin_transaction/ | MySQLiを利用してデータベースをアクセスする場合、移植性による理由から、begin_transaction/ | ||
- | ==== トランザクションの分離レベル ==== | + | \\ |
+ | |||
+ | ===== トランザクションの分離レベル | ||
MySQLは4つのトランザクション分離レベルを提供しています。SQL92では、これらの分離レベルは下表のように説明されています。しかし、**MySQLではREPEATABLE READレベルでファントムリードが発生しません**。 | MySQLは4つのトランザクション分離レベルを提供しています。SQL92では、これらの分離レベルは下表のように説明されています。しかし、**MySQLではREPEATABLE READレベルでファントムリードが発生しません**。 | ||
行 188: | 行 212: | ||
<file sql transaction2a.sql> | <file sql transaction2a.sql> | ||
- | # テーブル作成 ------------------------- | + | -- テーブル作成 ------------------------- |
DROP TABLE IF EXISTS syain; | DROP TABLE IF EXISTS syain; | ||
CREATE TABLE syain ( | CREATE TABLE syain ( | ||
行 197: | 行 221: | ||
); | ); | ||
INSERT INTO syain VALUES(1,' | INSERT INTO syain VALUES(1,' | ||
- | ; | + | #; |
- | #トランザクション開始 ------------------ | + | |
+ | -- トランザクション開始 ------------------ | ||
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | ||
- | #SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | + | -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
- | #SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; | + | -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
- | #SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | + | -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
START TRANSACTION; | START TRANSACTION; | ||
- | EVAL # | + | EVAL # トランザクションA開始; |
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | EVAL # | + | EVAL # ここで他のトランザクションがデータを更新する; |
EVAL sleep(5); | EVAL sleep(5); | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
SELECT * FROM syain LOCK IN SHARE MODE; | SELECT * FROM syain LOCK IN SHARE MODE; | ||
- | # トランザクション終了 ----------------- | + | -- トランザクション終了 ----------------- |
COMMIT; | COMMIT; | ||
- | EVAL # | + | EVAL # トランザクションA終了; |
EVAL sleep(5); | EVAL sleep(5); | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | |||
</ | </ | ||
行 235: | 行 259: | ||
> 共有ロック:他のトランザクションからはデータの参照はできますが、更新はできません | > 共有ロック:他のトランザクションからはデータの参照はできますが、更新はできません | ||
+ | \\ | ||
- | === トランザクション分離レベル:READ UNCOMMITTED === | + | ==== READ UNCOMMITTED |
transaction2a.sql のトランザクション分離レベルをREAD UNCOMMITTEDに設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル(transaction2b.sql)を準備します。 | transaction2a.sql のトランザクション分離レベルをREAD UNCOMMITTEDに設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル(transaction2b.sql)を準備します。 | ||
<file sql transaction2b.sql> | <file sql transaction2b.sql> | ||
- | #トランザクション開始 ------------------ | + | -- トランザクション開始 ------------------ |
START TRANSACTION; | START TRANSACTION; | ||
EVAL # | EVAL # | ||
- | # テーブルの更新 | + | -- テーブルの更新 |
INSERT INTO syain VALUES(2,' | INSERT INTO syain VALUES(2,' | ||
UPDATE syain SET syain_age = syain_age + 1; | UPDATE syain SET syain_age = syain_age + 1; | ||
行 252: | 行 277: | ||
EVAL sleep(5); | EVAL sleep(5); | ||
- | # トランザクション終了 ----------------- | + | -- トランザクション終了 ----------------- |
ROLLBACK; | ROLLBACK; | ||
EVAL # | EVAL # | ||
行 261: | 行 286: | ||
以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | 以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | ||
- | - < | + | - '' |
- | - < | + | - '' |
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 272: | 行 297: | ||
同様の実験を、transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定して行ないます。 | 同様の実験を、transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定して行ないます。 | ||
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 278: | 行 303: | ||
2つ目の単純SELECT文ではダーティリードが発生しません。また、SELECT文が更新系トランザクションの終了を待たない点にも注目して下さい。これは、MySQLの読み取り一貫性がロックを取得しないで実現されているからです。この分離レベルではSELECTされた時点の最新のコミット済みスナップショットが保持されます。 | 2つ目の単純SELECT文ではダーティリードが発生しません。また、SELECT文が更新系トランザクションの終了を待たない点にも注目して下さい。これは、MySQLの読み取り一貫性がロックを取得しないで実現されているからです。この分離レベルではSELECTされた時点の最新のコミット済みスナップショットが保持されます。 | ||
- | === トランザクション分離レベル:READ COMMITTED === | + | \\ |
+ | |||
+ | ==== READ COMMITTED | ||
transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル(transaction2c.sql)を準備します。 | transaction2a.sql のトランザクション分離レベルを**READ COMMITTED**に設定します。そして、syainテーブルの更新系トランザクションを含む以下のSQLスクリプトファイル(transaction2c.sql)を準備します。 | ||
<file sql transaction2c.sql> | <file sql transaction2c.sql> | ||
- | # 自動コミットモード ------------------- | + | -- 自動コミットモード ------------------- |
- | # レコード挿入 | + | -- レコード挿入 |
INSERT INTO syain VALUES(2,' | INSERT INTO syain VALUES(2,' | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | # レコード更新 | + | -- レコード更新 |
UPDATE syain SET syain_age = syain_age + 1; | UPDATE syain SET syain_age = syain_age + 1; | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
行 297: | 行 324: | ||
以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | 以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | ||
- | - < | + | - '' |
- | - < | + | - '' |
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 311: | 行 338: | ||
この例では、3つ目の(共有ロックを取得する)SELECT文の検索結果が、単純SELECT文の場合と同じです。両者の違いは次の実験で明らかになります。 | この例では、3つ目の(共有ロックを取得する)SELECT文の検索結果が、単純SELECT文の場合と同じです。両者の違いは次の実験で明らかになります。 | ||
- | === トランザクション分離レベル:REPEATABLE READ === | + | \\ |
+ | |||
+ | ==== REPEATABLE READ ==== | ||
transaction2a.sql のトランザクション分離レベルを**REPEATABLE READ**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。 | transaction2a.sql のトランザクション分離レベルを**REPEATABLE READ**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。 | ||
<file sql transaction2c.sql> | <file sql transaction2c.sql> | ||
- | # 自動コミットモード ------------------- | + | -- 自動コミットモード ------------------- |
- | # レコード挿入 | + | -- レコード挿入 |
INSERT INTO syain VALUES(2,' | INSERT INTO syain VALUES(2,' | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | # レコード更新 | + | -- レコード更新 |
UPDATE syain SET syain_age = syain_age + 1; | UPDATE syain SET syain_age = syain_age + 1; | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
行 328: | 行 357: | ||
以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | 以下の順でSQLスクリプトを実行します。2番目のスクリプトは5秒以内に実行します。 | ||
- | - < | + | - '' |
- | - < | + | - '' |
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 341: | 行 370: | ||
上の実行結果で、3つ目の(共有ロックを取得する)SELECT文の検索結果を見てください。この検索結果は前の例題(READ COMMITTED)の場合と同じです。ロックを取得するSELECT文を実行した場合は、最初に読み取ったスナップショットではなく、最新のデータを読み取っているようです。 | 上の実行結果で、3つ目の(共有ロックを取得する)SELECT文の検索結果を見てください。この検索結果は前の例題(READ COMMITTED)の場合と同じです。ロックを取得するSELECT文を実行した場合は、最初に読み取ったスナップショットではなく、最新のデータを読み取っているようです。 | ||
- | === トランザクション分離レベル:SERIALIZABLE === | + | \\ |
+ | |||
+ | ==== SERIALIZABLE | ||
transaction2a.sql のトランザクション分離レベルを**SERIALIZABLE**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。 | transaction2a.sql のトランザクション分離レベルを**SERIALIZABLE**に設定します。syainテーブルの更新系トランザクションを含むSQLスクリプトファイル(transaction2c.sql)は前の実験と同じものを使用します。 | ||
<file sql transaction2c.sql> | <file sql transaction2c.sql> | ||
- | # 自動コミットモード ------------------- | + | -- 自動コミットモード ------------------- |
- | # レコード挿入 | + | -- レコード挿入 |
INSERT INTO syain VALUES(2,' | INSERT INTO syain VALUES(2,' | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
- | # レコード更新 | + | -- レコード更新 |
UPDATE syain SET syain_age = syain_age + 1; | UPDATE syain SET syain_age = syain_age + 1; | ||
SELECT * FROM syain; | SELECT * FROM syain; | ||
</ | </ | ||
- | ブラウザから http:// | + | ブラウザから |
- | 実行結果 | + | === 実行結果 |
[{{: | [{{: | ||
行 366: | 行 397: | ||
このレベル(SERIALIZABLE)の単純SELECT文は暗黙の共有ロックモード(LOCK IN SHARE MODE)で実行されます。SERIALIZABLEレベルのトランザクションは分離レベルが最も高く、他のトランザクションの影響を受けないように設計されています。但し、この実行例(排他ロックを取得する操作が待機状態になる事)からも分かるように、トランザクションの同時実行性は最も低くなります。 | このレベル(SERIALIZABLE)の単純SELECT文は暗黙の共有ロックモード(LOCK IN SHARE MODE)で実行されます。SERIALIZABLEレベルのトランザクションは分離レベルが最も高く、他のトランザクションの影響を受けないように設計されています。但し、この実行例(排他ロックを取得する操作が待機状態になる事)からも分かるように、トランザクションの同時実行性は最も低くなります。 | ||
- | + | \\ | |