More than 5 years have passed since last update.

MySQLのautocommitとトランザクション分離レベルのメモ

MySQL

Last updated at 2015-10-01Posted at 2015-10-01

#概要

MySQL5.6のInnoDBエンジン使用時の自動コミットモードとトランザクション周りの調査メモです。
記事の前半は自動コミットやトランザクションに関係する設定値の参照、変更方法の確認で、後半は自動コミットモードとトランザクション分離レベルの組み合わせによるデータの見え方の確認を行います。

環境

Windows7 (64bit)
MySQL 5.6.25
InnoDB

参考

[InnoDB のトランザクションモデルおよびロック] (http://dev.mysql.com/doc/refman/5.6/ja/innodb-transaction-model.html)
[SET TRANSACTION 構文] (http://dev.mysql.com/doc/refman/5.6/ja/set-transaction.html)
[バイナリログ形式の設定] (http://dev.mysql.com/doc/refman/5.6/ja/binary-log-setting.html)

設定値

自動コミットモード (autocommit)

[autocommit] (https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_autocommit)

my.cnf

5.5.8～

[mysqld]
autocommit = 1

value	description
1 (ON)	default. 自動コミットを有効にする。データの変更は直ぐに反映される。
0 (OFF)	自動コミットを無効にする。トランザクションをコミットしなければデータの変更は反映されない。

自動コミットを有効にすると1ステートメント = 1トランザクションとなります。

MySQL 5.5.8以前*

autocommitはMySQL 5.5.8より設定可能なオプションです。
バージョン5.5.8以前の場合は下記のように行います。

～5.5.7

[mysqld]
init_connect='SET autocommit=0'

設定の確認

mysql> select @@global.autocommit, @@session.autocommit;
+---------------------+----------------------+
| @@global.autocommit | @@session.autocommit |
+---------------------+----------------------+
|                   1 |                    1 |
+---------------------+----------------------+

変数に@@global.、@@session.のどちらも省略した場合はsessionとして扱われる。
@@local.は@@session.のシノニム。

または

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

設定の変更

mysql> set autocommit = 0;

トランザクション分離レベル (transaction-isolation)

[tx_isolation] (https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_tx_isolation)
[transaction-isolation] (https://dev.mysql.com/doc/refman/5.6/ja/server-options.html#option_mysqld_transaction-isolation)

my.cnf

デフォルトの分離レベルを指定することができます。

[mysqld]
transaction-isolation = REPEATABLE-READ

value	description
READ-UNCOMMITTED	他のトランザクションのコミットされていない変更が見えます。
READ-COMMITTED	他のトランザクションのコミットされた変更が見えます。
REPEATABLE-READ	default. トランザクション開始時にコミットされていたデータのみ見えます。
SERIALIZABLE	すべてのselectでロックを行うことでトランザクションを直列化し競合が発生しないようにします。

REPEATABLE-READ

ANSI/ISO SQL標準で定められている分離レベルのREPEATABLE-READでは、ファントムリードと呼ばれる現象が発生する可能性がありますが、MySQLの実装では発生しません。

バイナリログ形式

[5.2.4.2. バイナリログ形式の設定] (http://dev.mysql.com/doc/refman/5.6/ja/binary-log-setting.html)
InnoDBテーブルを使用中で、トランザクション分離レベルが READ COMMITTED または READ UNCOMMITTED の場合、行ベースのロギングのみを使用することができます。ロギング形式を STATEMENT に変更することは可能ですが、InnoDB は挿入を実行できないため、実行時にこれを行うと、非常に速くエラーが発生します。

グローバル値またはセッション値 binlog_format を変更するには、SUPER 権限が必要です。

設定の確認

mysql> select @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+

または

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

設定の変更

mysql> set session transaction isolation level READ COMMITTED;

トランザクションアクセスモード (tx_read_only)

MySQL 5.6.5より設定可能なオプションです。

[tx_read_only] (https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_tx_read_only)
[transaction-read-only] (https://dev.mysql.com/doc/refman/5.6/ja/server-options.html#option_mysqld_transaction-read-only)

my.cnf

デフォルトのアクセスモードを指定することができます。

[mysqld]
transaction-read-only = FALSE

value	description
FALSE	default. 読み取り/書き込み可
TRUE	読み取り専用

設定の確認

mysql> select @@global.tx_read_only, @@session.tx_read_only;
+-----------------------+------------------------+
| @@global.tx_read_only | @@session.tx_read_only |
+-----------------------+------------------------+
|                     0 |                      0 |
+-----------------------+------------------------+

または

mysql> show variables like 'tx_read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tx_read_only  | OFF   |
+---------------+-------+

設定の変更

mysql> set session transaction read only;

binlog_format

[5.2.4.2. バイナリログ形式の設定] (http://dev.mysql.com/doc/refman/5.6/ja/binary-log-setting.html)

my.cnf

[mysqld]
binlog_format = STATEMENT

value	description
STATEMENT	default. ロギングはステートメントに基づきます
ROW	ロギングは行に基づきます
MIXED	ロギングは混合形式を使用します

設定の確認

mysql> select @@global.binlog_format, @@session.binlog_format;
+------------------------+-------------------------+
| @@global.binlog_format | @@session.binlog_format |
+------------------------+-------------------------+
| STATEMENT              | STATEMENT               |
+------------------------+-------------------------+

または

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

設定の変更

mysql> set session binlog_format = 'ROW';

SUPER権限が必要です。

innodb_lock_wait_timeout

[innodb_lock_wait_timeout] (https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_lock_wait_timeout)
ロック待機のタイムアウトが発生すると、(トランザクション全体ではなく) 現在のステートメントがロールバックされます。

innodb_lock_wait_timeout は InnoDB の行ロックにのみ適用されます。MySQL のテーブルロックは InnoDB 内部では発生せず、このタイムアウトはテーブルロックの待機には適用されません。

my.cnf

[mysqld]
innodb_lock_wait_timeout = 50

value	description
50	default. 単位は秒.
1	最小値.
1073741824	最大値.

設定の確認

mysql> select @@global.innodb_lock_wait_timeout, @@session.innodb_lock_wait_timeout;
+-----------------------------------+------------------------------------+
| @@global.innodb_lock_wait_timeout | @@session.innodb_lock_wait_timeout |
+-----------------------------------+------------------------------------+
|                                50 |                                 50 |
+-----------------------------------+------------------------------------+

または

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

設定の変更

mysql> set innodb_lock_wait_timeout = 10;

SUPER権限が必要です。

innodb_rollback_on_timeout

MySQL 5.6 では、InnoDB はデフォルトで、トランザクションタイムアウト時に最後のステートメントのみをロールバックします。--innodb_rollback_on_timeout を指定すると、トランザクションタイムアウトによって、InnoDB はトランザクション全体を中止してロールバックします (MySQL 4.1 と同じ動作です)。

my.cnf

[mysqld]
innodb_rollback_on_timeout = FALSE

value	description
FALSE	default. タイムアウト時の最後のステートメントのみをロールバックします。
TRUE	トランザクション全体を中止してロールバックします。

設定の確認

変数スコープはグローバルのみです。

mysql> select @@global.innodb_rollback_on_timeout;
+-------------------------------------+
| @@global.innodb_rollback_on_timeout |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

または

mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+

設定の変更

commandでの変更はできません。

mysql> set innodb_rollback_on_timeout = 1;
ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable

確認

2つのmysql clientを立ち上げて1つをセッションA、もう1つをセッションBとしデータの更新が他のセッションからどのように見えるか確認します。

確認作業に使用するテーブルは下記の通りです。

create table tr_test (
  id int not null,
  label varchar(20) not null,
  status varchar(10),
  update_at timestamp(6) not null default current_timestamp(6) on update current_timestamp(6),
  primary key (id)
) engine = INNODB;

確認(1)：自動コミットを有効、REPEATABLE-READ(デフォルト)

MySQL5.6のデフォルトの組み合わせでの確認です。

configuration	value	description
autocommit	1	自動コミットを有効
tx_isolation	REPEATABLE-READ	おなじトランザクション内なら他のトランザクションでコミットしたデータは見えません

変更

mysql> set autocommit = 1;
mysql> set session transaction isolation level REPEATABLE READ;

確認

mysql> select @@global.autocommit, @@session.autocommit;
mysql> select @@global.tx_isolation, @@session.tx_isolation;

シーケンス

      +------------------+     +------------------+
Seq   |     Sesson-A     |     |     Sesson-B     |
      +------------------+     +------------------+

       (Transaction Start)
               |
1          [INSERT]
               |
        (Transaction End)

                                (Transaction Start)
                                        |
2                                   [SELECT] *Seq1のINSERTは見える
                                        |
                                 (Transaction End)

       (Transaction Start)
               |
3          [UPDATE]
               |
        (Transaction End)

                                (Transaction Start)
                                        |
4                                   [SELECT] *Seq3のUPDATEは見える
                                        |
                                 (Transaction End)

実行SQL

下記のSQL文を上から順番に実行します。

Seq-1: セッションA

Sesson-A

mysql> insert into tr_test (id, label, status) values (1, 'session-A', null);
Query OK, 1 row affected (0.00 sec)

Seq-2: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 18:42:16.645110 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-3: セッションA

Sesson-A

mysql> update tr_test set status = 'A' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Seq-4: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | A      | 2015-10-01 18:43:14.940444 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

確認(2)：自動コミットを混在(無効/有効)、REPEATABLE-READ

セッションAは自動コミット無効、セッションBは自動コミット有効の状態にしたときの確認です。

セッションA

変更

mysql> set autocommit = 0;
mysql> set session transaction isolation level REPEATABLE READ;

セッションB

変更

mysql> set autocommit = 1;
mysql> set session transaction isolation level REPEATABLE READ;

確認

mysql> select @@global.autocommit, @@session.autocommit;
mysql> select @@global.tx_isolation, @@session.tx_isolation;

シーケンス

      +------------------+     +------------------+
Seq   |     Sesson-A     |     |     Sesson-B     |
      +------------------+     +------------------+

       (Transaction Start)
               |
1          [INSERT]
               |                (Transaction Start)
               |                        |
2              |                    [SELECT] *Seq1のINSERTは見えない
               |                        |
               |                 (Transaction End)
3          [COMMIT]
               |
        (Transaction End)

                                (Transaction Start)
                                        |
4                                   [SELECT] *Seq3のCOMMIT後は見える
                                        |
                                 (Transaction End)

       (Transaction Start)
               |
5          [UPDATE]
               |                (Transaction Start)
               |                        |
6              |                    [SELECT] *Seq5のUPDATEは見えない
               |                        |
               |                 (Transaction End)
7          [COMMIT]
               |
        (Transaction End)

                                (Transaction Start)
                                        |
8                                   [SELECT] *Seq7のCOMMIT後は見える
                                        |
                                 (Transaction End)

実行SQL

下記のSQL文を上から順番に実行します。

Seq-1: セッションA

Sesson-A

mysql> insert into tr_test (id, label, status) values (1, 'session-A', null);
Query OK, 1 row affected (0.00 sec)

Seq-2: セッションB

Sesson-B

mysql> select * from tr_test;
Empty set (0.00 sec)

Seq-3: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-4: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 19:19:45.939762 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-5: セッションA

Sesson-A

mysql> update tr_test set status = 'A' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Seq-6: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 19:19:45.939762 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-7: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-8: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | A      | 2015-10-01 19:21:35.104006 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

確認(3)：自動コミットを無効、REPEATABLE-READ

configuration	value	description
autocommit	0	自動コミットを無効
tx_isolation	REPEATABLE-READ	おなじトランザクション内なら他のトランザクションでコミットしたデータは見えません

変更

mysql> set autocommit = 0;
mysql> set session transaction isolation level REPEATABLE READ;

確認

mysql> select @@global.autocommit, @@session.autocommit;
mysql> select @@global.tx_isolation, @@session.tx_isolation;

シーケンス

      +------------------+     +------------------+
Seq   |     Sesson-A     |     |     Sesson-B     |
      +------------------+     +------------------+

                                (Transaction Start) *このトランザクション以降の変更は見えない
                                        |
       (Transaction Start)              |
               |                        |
1          [INSERT]                     |
2              |                    [SELECT] *Seq1のINSERTは見えない
3          [COMMIT]                     |
               |                        |
        (Transaction End)               |
4                                   [SELECT] *Seq3のCOMMIT後でも見えない
                                        |
5                                    [COMMIT]
                                        |
                                (Transaction End)

                                (Transaction Start) *このトランザクション以降の変更は見えない
                                        |
6                                   [SELECT] *トランザクションが変わったのでSeq1のINSERTは見える
                                        |
       (Transaction Start)              |
               |                        |
7          [UPDATE]                     |
8              |                    [SELECT] *Seq7のUPDATEは見えない
9          [COMMIT]                     |
               |                        |
        (Transaction End)               |
10                                  [SELECT] *Seq9のCOMMIT後でも見えない
                                        |
11                                   [COMMIT]
                                        |
                                (Transaction End)

                                (Transaction Start)
                                        |
12                                   [SELECT] *トランザクションが変わったのでSeq7のUPDATEは見える
                                        |

実行SQL

下記のSQL文を上から順番に実行します。

Seq-1: セッションA

Sesson-A

mysql> insert into tr_test (id, label, status) values (1, 'session-A', null);
Query OK, 1 row affected (0.00 sec)

Seq-2: セッションB

Sesson-B

mysql> select * from tr_test;
Empty set (0.00 sec)

Seq-1でinsertしたデータは見えません。

Seq-3: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-4: セッションB

Sesson-B

mysql> select * from tr_test;
Empty set (0.00 sec)

Seq-3でcommitした後でも、Seq-1でinsertしたデータは見えません。

Seq-5: セッションB

Sesson-B

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

トランザクションを変えるためのcommitです。

Seq-6: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 17:40:48.042134 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

新しいトランザクションが始まったのでSeq-1でinsertしたデータは見えます。

Seq-7: セッションA

Sesson-A

mysql> update tr_test set status = 'A' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Seq-8: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 17:40:48.042134 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-7でupdateしたデータはみえません。

Seq-9: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-10: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | NULL   | 2015-10-01 17:40:48.042134 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-9のcommit後でもみえません。

Seq-11: セッションB

Sesson-B

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

トランザクションを変えるためのcommitです。

Seq-12: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  1 | session-A | A      | 2015-10-01 17:43:06.123031 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

新しいトランザクションが始まったのでSeq-7でupdateしたデータは見えます。

確認(4)：自動コミットを無効、READ-COMMITTED

configuration	value	description
autocommit	0	自動コミットを無効
tx_isolation	READ-COMMITTED	おなじトランザクション内でも他のトランザクションでコミットしたデータは見えます

変更

mysql> set autocommit = 0;
mysql> set session transaction isolation level READ COMMITTED;

確認

mysql> select @@global.autocommit, @@session.autocommit;
mysql> select @@global.tx_isolation, @@session.tx_isolation;

シーケンス

      +------------------+     +------------------+
Seq   |     Sesson-A     |     |     Sesson-B     |
      +------------------+     +------------------+

                                (Transaction Start) *このトランザクション以降でも
                                        |            commitされたデータは見える
       (Transaction Start)              |
               |                        |
1          [INSERT]                     |
2              |                    [SELECT] *Seq1のINSERTは見えない
3          [COMMIT]                     |
               |                        |
        (Transaction End)               |
4                                   [SELECT] *Seq3のCOMMIT後は見える
                                        |
       (Transaction Start)              |
               |                        |
5          [UPDATE]                     |
6              |                    [SELECT] *Seq5のUPDATEは見えない
7          [COMMIT]                     |
               |                        |
        (Transaction End)               |
8                                   [SELECT] *Seq7のCOMMIT後は見える
                                        |

実行SQL

下記のSQL文を上から順番に実行します。

Seq-1: セッションA

Sesson-A

mysql> insert into tr_test (id, label, status) values (2, 'session-A', null);
Query OK, 1 row affected (0.00 sec)

Seq-2: セッションB

Sesson-B

mysql> select * from tr_test;
Empty set (0.00 sec)

Seq-1でinsertしたデータは見えません。

Seq-3: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-4: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  2 | session-A | NULL   | 2015-10-01 18:14:37.047186 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-3でcommitした後は、Seq-1でinsertしたデータが見えます。

Seq-5: セッションA

Sesson-A

mysql> update tr_test set status = 'A' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Seq-6: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  2 | session-A | NULL   | 2015-10-01 18:14:37.047186 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-5でupdateしたデータはみえません。

Seq-7: セッションA

Sesson-A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Seq-8: セッションB

Sesson-B

mysql> select * from tr_test;
+----+-----------+--------+----------------------------+
| id | label     | status | update_at                  |
+----+-----------+--------+----------------------------+
|  2 | session-A | A      | 2015-10-01 18:16:25.781405 |
+----+-----------+--------+----------------------------+
1 row in set (0.00 sec)

Seq-7のcommitした後は、Seq-7でupdateしたデータが見えます。

メモ

SUPER権限の付与

この記事内でのコマンド実行にはSUPER権限が必要なものがあります。

mysql> set session binlog_format = 'ROW';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

SUPER権限の付与

mysql> grant super on *.* to `test_user`@`localhost`;

用語

ダーティーリード
他のトランザクションのコミットされていない変更が見えること。
ノンリピータブルリード/ファジーリード
他のトランザクションのコミットされた変更が見えること。(反復不可能読み取り)
ファントムリード
他のトランザクションで挿入されたレコードが見えること。

110

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up