Edited at

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

More than 3 years have passed since last update.


概要

MySQL5.6のInnoDBエンジン使用時の自動コミットモードとトランザクション周りの調査メモです。

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

環境


  • Windows7 (64bit)

  • MySQL 5.6.25


    • InnoDB



参考


設定値


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

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

5.2.4.2. バイナリログ形式の設定


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`;


用語


  • ダーティーリード


    • 他のトランザクションのコミットされていない変更が見えること。



  • ノンリピータブルリード/ファジーリード


    • 他のトランザクションのコミットされた変更が見えること。(反復不可能読み取り)



  • ファントムリード


    • 他のトランザクションで挿入されたレコードが見えること。