概要
MySQL5.6のInnoDBエンジン使用時の自動コミットモードとトランザクション周りの調査メモです。
記事の前半は自動コミットやトランザクションに関係する設定値の参照、変更方法の確認で、後半は自動コミットモードとトランザクション分離レベルの組み合わせによるデータの見え方の確認を行います。
環境
- Windows7 (64bit)
- MySQL 5.6.25
- InnoDB
参考
設定値
自動コミットモード (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
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. バイナリログ形式の設定
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
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
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
ロック待機のタイムアウトが発生すると、(トランザクション全体ではなく) 現在のステートメントがロールバックされます。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`;
用語
- ダーティーリード
- 他のトランザクションのコミットされていない変更が見えること。
- ノンリピータブルリード/ファジーリード
- 他のトランザクションのコミットされた変更が見えること。(反復不可能読み取り)
- ファントムリード
- 他のトランザクションで挿入されたレコードが見えること。