2005-08-30

MySQLの実験(4) - トランザクション

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

MySQLのトランザクション

MySQLはデフォルトで自動トランザクション機能(自動コミットモード)が有効です。しかし、本実験ではDBIを使っているので、この機能の初期状態は接続時(DBIのconnectメソッド)のAutoCommitの設定に従います。この実験で使用しているプログラム(sqlfile.cgi)は以下のようにしてMySQLサーバに接続しています。この場合も自動コミットモードは有効です。

%attr = (RaiseError=>0, PrintError=>1,AutoCommit=>1);
$dbh = DBI->connect($dsn,$user,$pass,\%attr) || CgiError("connect",$DBI::errstr);

自動コミットモードを無効にして接続するには以下のようにします。

%attr = (RaiseError=>0, PrintError=>1,AutoCommit=>0);
$dbh = DBI->connect($dsn,$user,$pass,\%attr) || CgiError("connect",$DBI::errstr);

また、プログラムの途中で自動コミットモードを切り替える場合は、次のようにします。

$dbh->{AutoCommit} = 1; # 有効
$dbh->{AutoCommit} = 0; # 無効

MySQLでは次のSQL文を使って自動コミットモードを切り替える事ができます。

SET AUTOCOMMIT=1; /* 有効 */
SET AUTOCOMMIT=0; /* 無効 */

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

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

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

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

$rc  = $dbh->begin_work;
$rc  = $dbh->commit;
$rc  = $dbh->rollback;

begin_workはAutoCommit属性を0にして自動コミットモードを無効にします。その状態はcommit/rollbackメソッドが呼び出されるまで続きます。この仕様は START TRANSACTION文の場合と微妙に異なります。

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

http://dev.mysql.com/doc/mysql/en/transactional-commands.html

InnoDBではSQL92で定義されている4つのトランザクション分離レベルを全て提供しています。デフォルトの分離レベルREPEATABLE READです。分離レベルの変更は次のSQL文で行います。

SET TRANSACTION ISOLATION LEVEL 分離レベル;
分離レベル=[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

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

http://dev.mysql.com/doc/mysql/en/innodb-transaction-model.html

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

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction1a.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;

※10行目のセミコロン(;)だけの行はブラウザ上で改行する為に使用しています。誤りではありません。

実行結果

perl-exp31a.gif

※上の実行結果はテーブル作成に関する出力を除いています。

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

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

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

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction1b.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 $dbh->{AutoCommit}=0;
EVAL $dbh->begin_work;
SELECT * FROM syain;

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

# トランザクション終了 -----------------
EVAL $dbh->rollback; #ロールバックの実行
SELECT * FROM syain;

上のスクリプトファイルにはEVAL文が含まれています。EVALに続く式(例えば:$dbh->begin_work)はPerlのeval関数に引き渡されます。$dbh変数は sqlfile.cgi のファイルレベルのレキシカルスコープを持っているのでbegin_workなどのデータベースハンドルメソッドやハンドル属性が使用できます。

実行結果

perl-exp31b.gif

この例では、DBIのデータベースハンドルのbegin_workメソッドでトランザクションを開始しrollback メソッドを使用してトランザクションをロールバックしています。

begin_workメソッドの代わりにAutoCommit 属性を0に設定 ( $dbh->{AutoCommit}=0 ) してもトランザクションを開始する事ができます。但し、この場合は、トランザクション終了後も自動コミットモードは無効なので、新しい手動トランザクションが暗黙的に開始されます。

この例で、begin_workメソッドを1行目に移動して実行すると、どうなるでしょう?先の例「SQL文による手動トランザクション」と同じにはなりません。DROP TABLE文により暗黙のコミットが発生していますが、自動コミットモードは無効のままです。従って、rollback メソッドは(CREATE TABLEを除いて)有効に機能します。begin_workメソッドはAutoCommit 属性を0に設定する事によって自動コミットモードを無効にしています。この状態はcommitまたはrollback メソッドが呼び出されるまで続きます。

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

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

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

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

ダーティリードノン・リピータブルリードファントムリード
READ UNCOMMITTED発生する発生する発生する
READ COMMITTED発生しない発生する発生する
REPEATABLE READ発生しない発生しない発生する
SERIALIZABLE発生しない発生しない発生しない

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

トランザクションの分離レベルに関する実験

以下では、MySQLのトランザクションの分離レベルに関する実験を行います。この実験では2種類のSQLスクリプトファイルを使用します。それらはsyainテーブルに関する照会系と更新系のトランザクションです。以下に照会系のトランザクション(transaction2a.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;

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

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

尚、MySQLのSELECT文では3種類のロック操作ができます。単純SELECT文はロックを取得しません。SELECT ... FOR UPDATE文は行レベルの排他ロックモードでSELECT文を実行し、SELECT ... LOCK IN SHARE MODEは行レベルの共有ロックモードでSELECT文を実行します。

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

トランザクション分離レベル:READ UNCOMMITTED

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

transaction2b.sql

#トランザクション開始 ------------------
START TRANSACTION;
EVAL '(トランザクション開始)';

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

EVAL sleep(5); 

# トランザクション終了 -----------------
ROLLBACK;
EVAL '(トランザクション終了)';

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

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2a.sql を実行し、直ぐに(5秒以内)、 別のブラウザから http://localhost/mysql/sqlfile.cgi?transaction2b.sql を実行します。

実行結果(transaction2a.sql)

perl-exp32a.gif

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

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

トランザクション分離レベル:READ COMMITTED

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

transaction2c.sql

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

# レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;

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

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2a.sql を実行し、直ぐに(5秒以内)、 別の ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2c.sql を実行します。

実行結果(transaction2a.sql)

perl-exp32b.gif

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

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

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

トランザクション分離レベル:REPEATABLE READ

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

transaction2c.sql

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

# レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2a.sql を実行し、直ぐに(5秒以内)、 別の ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2c.sql を実行します。

実行結果(transaction2a.sql)

perl-exp32c.gif

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

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

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

トランザクション分離レベル:SERIALIZABLE

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

transaction2c.sql

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

# レコード更新
UPDATE syain SET syain_age = syain_age + 1;
SELECT * FROM syain;

ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2a.sql を実行し、直ぐに(5秒以内)、 別の ブラウザから http://localhost/mysql/sqlfile.cgi?transaction2c.sql を実行します。

実行結果(transaction2a.sql)

perl-exp32d.gif

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

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



最終更新のRSS Last-modified: Tue, 30 Aug 2005 07:54:19 JST (4290d)