hironomiu/gist:8295933

## gistfile1.md

      
    Raw
  

              gistfile1.md
            
          
    トランザクション

準備


ターミナルは二つ起動すること以後ターミナルA、ターミナルBと呼ぶ
master,slave構成の場合はmasterのみで作業を行います

ターミナルA

# mysql

mysql> use test
mysql> create table tran_test(id int);
Query OK, 0 rows affected (0.07 sec)

mysql> show tables like 'tran_test';
+----------------------------+
| Tables_in_test (tran_test) |
+----------------------------+
| tran_test                  |
+----------------------------+
1 row in set (0.00 sec)


tran_testテーブルが作成されること

ターミナルB

# mysql

mysql> use test
mysql> show tables like 'tran_test';
+----------------------------+
| Tables_in_test (tran_test) |
+----------------------------+
| tran_test                  |
+----------------------------+
1 row in set (0.00 sec)


tran_testテーブルが参照できること

自動コミット、手動コミット＆ロールバック機能

自動コミット確認

ターミナルA

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


1（自動コミット有効）が返ること（デフォルト設定）

ターミナルB

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


1（自動コミット有効）が返ること（デフォルト設定）

ターミナルA

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


データが存在しないこと

ターミナルB

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


データが存在しないこと

ターミナルA

mysql> insert into tran_test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


挿入したデータが出力されること

ターミナルB

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


自動コッミとによりターミナルAで挿入されたレコード（1）が返ること

手動コミット（設定変更　自動コミットオフ）

ターミナルA


自動コミットをオフにする

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> insert into tran_test values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


1,2が返ること

ターミナルB

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


1のみ返ること

ターミナルA

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

ターミナルB

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


1,2が返ること

ロールバック確認

ターミナルA


自動コミットをオフで実施する。自動コミットオンの場合は前段で実施したオフのコマンドを実施する

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


mysql> insert into tran_test values (3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)


3が存在すること

ターミナルB

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


3が存在しないこと

ターミナルA

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


ロールバックにより3が取り消されること

ターミナルB

mysql> select * from tran_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)


特に変わりがないこと

P70 実技（start transaction含む）

ターミナルA


自動コミットオフ（@@autocommitが0）の場合、自動コミットオン（autocommitを1）に戻す

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


以下を実施

mysql> create table tbl1(col1 int unique);
Query OK, 0 rows affected (0.05 sec)

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

mysql> insert into tbl1 values(50),(100),(200);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update table tbl1 set col1=col1+100 where col1 <= 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table tbl1 set col1=col1+100 where col1 <= 100' at line 1
mysql> select * from tbl1;
+------+
| col1 |
+------+
|   50 |
|  100 |
|  200 |
+------+
3 rows in set (0.00 sec)

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

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

P74 Column


Valueの値に注目

mysql> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

P74 SELECT FOR UPDATE


ターミナルA,Bともにautocommitがオフとなっていること

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


オンの場合はオフに変更

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

ターミナルA

mysql> select * from tbl1 for update;
Empty set (0.00 sec)

ターミナルB

mysql> insert into tbl1 values(10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


エラーとなること（前段で確認したinnodb_lock_wait_timeoutの時間分待つ）

P78（楽観ロック）


ターミナルA,Bともにautocommitがオフとなっていること

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


オンの場合はオフに変更

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

ターミナルA


準備　test DBにてテーブルの作成と2行レコードを挿入する。

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tbl2(id int not null auto_increment, col1 varchar(10), col2 int, primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl2 values(null,'abcd',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | abcd |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert into tbl2 values(null,'bcde',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | abcd |    1 |
|  2 | bcde |    1 |
+----+------+------+
2 rows in set (0.00 sec)

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

ターミナルB


準備（autocommitオフを考慮）

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

ターミナルA

mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | abcd |    1 |
+----+------+------+
1 row in set (0.01 sec)

ターミナルB

mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | abcd |    1 |
+----+------+------+
1 row in set (0.00 sec)

ターミナルA


Rows matched: 1  Changed: 1に注目

mysql> update tbl2 set col1 = 'opqr' , col2 = 2 where id = 1 and col2 = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
Empty set (0.00 sec)

mysql> select id,col1,col2 from tbl2 where col1 = 'opqr';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | opqr |    2 |
+----+------+------+
1 row in set (0.00 sec)

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


col1がabcdからopqrに変わっていること（更新の成功）

ターミナルB


Rows matched: 0  Changed: 0に注目

mysql> update tbl2 set col1 = 'xyza' , col2 = 2 where id = 1 and col2 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
Empty set (0.00 sec)

mysql> select id,col1,col2 from tbl2 where col1 = 'xyza';
Empty set (0.00 sec)

mysql> select id,col1,col2 from tbl2 where id = 1;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | opqr |    2 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from tbl2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | opqr |    2 |
|  2 | bcde |    1 |
+----+------+------+
2 rows in set (0.00 sec)

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

mysql> select * from tbl2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | opqr |    2 |
|  2 | bcde |    1 |
+----+------+------+
2 rows in set (0.00 sec)


col1=abcd,xyzaともに存在しないこと（更新の失敗）opqrが存在すること（ターミナルAの更新の成功）

分離レベル

準備


ターミナルA、Bともに下記SQLで分離レベルの確認を行う
ターミナルA、BともにREPEATABLE-READであること**

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA,Bともにautocommitがオンとなっていること

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)


オフの場合はオンに変更

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

ターミナルA

テーブルの作成とデータの挿入
mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |      10 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

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

ロストアップデート

ターミナルA

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

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

ターミナルB

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

mysql> update account set balance=balance+30 where act_code=100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      200 |      25 |
|      100 |      40 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

ターミナルA

mysql> update account set balance=6 where act_code = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |       6 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

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

ターミナルB

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |       6 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

ダーティリード

ターミナルB


分離レベルをRead Uncommittedに変更する

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

ターミナルA

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

mysql> update account set balance=balance+10 where act_code=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      16 |
+---------+
1 row in set (0.00 sec)

ターミナルB

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

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      16 |
+---------+
1 row in set (0.00 sec)

ターミナルA


act_code=100が6になっていること

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

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |       6 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

ターミナルB


6に戻っていること

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

反復不能読込み

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルB


分離レベルをRead Committedに変更する

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)


ターミナルA

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

ターミナルB

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

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

ターミナルA

mysql> update account set balance = balance + 10 where act_code = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |      20 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

ターミナルB


20が返ること

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      20 |
+---------+
1 row in set (0.00 sec)

ファントムリード

ターミナルB


分離レベルをRead Committedに変更する

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)


ターミナルA


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

ターミナルB

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      100 |
|      200 |
|      300 |
+----------+
3 rows in set (0.00 sec)

ターミナルA

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      100 |
|      200 |
|      300 |
+----------+
3 rows in set (0.00 sec)

mysql> insert into account values (400,0);
Query OK, 1 row affected (0.00 sec)

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      400 |
|      100 |
|      200 |
|      300 |
+----------+
4 rows in set (0.00 sec)

ターミナルB


400が存在すること

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      400 |
|      100 |
|      200 |
|      300 |
+----------+
4 rows in set (0.00 sec)

ファントムリード（双方REPEATABLE-READ）

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルB


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルB

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      100 |
|      200 |
|      300 |
+----------+
3 rows in set (0.00 sec)

ターミナルA

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      100 |
|      200 |
|      300 |
+----------+
3 rows in set (0.00 sec)

mysql> insert into account values (400,0);
Query OK, 1 row affected (0.00 sec)

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      400 |
|      100 |
|      200 |
|      300 |
+----------+
4 rows in set (0.00 sec)

ターミナルB


400が存在しないこと
commit後400が現れること

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      100 |
|      200 |
|      300 |
+----------+
3 rows in set (0.00 sec)

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

mysql> select act_code from account;
+----------+
| act_code |
+----------+
|      400 |
|      100 |
|      200 |
|      300 |
+----------+
4 rows in set (0.00 sec)

デッドロック（REPEATABLE-READ）

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルB


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA

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

mysql> insert into account values(1000,0);
Query OK, 1 row affected (0.00 sec)

ターミナルB


updateで待ち状態になること

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

mysql> insert into account values(2000,0);

mysql> update account set balance = 10 where act_code = 1000;

ターミナルA


待ち状態中に実行しDeadlock Lockエラーが出力されること

mysql> update account set balance = 10 where act_code = 2000;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ターミナルB


ターミナルAでエラー出力後、応答が返ること

Query OK, 0 rows affected (12.69 sec)
Rows matched: 0  Changed: 0  Warnings: 0

ターミナルA


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ターミナルB


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ターミナルA


Deadlock Lockの確認
LATEST DETECTED DEADLOCKが出力され今回実行したSQLが存在すること

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2014-02-28 10:36:46 7f78a939f700 INNODB MONITOR OUTPUT
=====================================

　　　　　　略
　　　　　　

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-02-28 10:34:53 7f78a939f700
*** (1) TRANSACTION:
TRANSACTION 104082, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 98979, OS thread handle 0x7f78a935e700, query id 396102 localhost root updating
update account set balance = 10 where act_code = 1000

デッドロック（SERIALIZABLE）

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルB


分離レベルをSERIALIZABLEに設定する

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

ターミナルA


分離レベルをSERIALIZABLEに設定する

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)


ターミナルA

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


ターミナルB

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

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

ターミナルA

mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

ターミナルB


update後、待ちが発生すること

mysql> update account set balance = balance + 10 where act_code = 100;

ターミナルA


ターミナルBで待ちが発生中に実行すること
Deadlockが発生すること

mysql> update account set balance = balance + 30 where act_code = 100;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ターミナルB


ターミナルAでDeadlock後に応答が返ること

Query OK, 1 row affected (18.39 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ターミナルA


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ターミナルB


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ターミナルA


Deadlock Lockの確認
LATEST DETECTED DEADLOCKが出力され今回実行したSQLが存在すること

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2014-02-28 10:57:21 7f78a939f700 INNODB MONITOR OUTPUT
=====================================

　　　　　　略
　　　　　　

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-02-28 10:54:27 7f78a939f700
*** (1) TRANSACTION:
TRANSACTION 104116, ACTIVE 98 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 98979, OS thread handle 0x7f78a935e700, query id 396122 localhost root updating
update account set balance = balance + 10 where act_code = 100

インデックス未定義によるテーブルロック

ターミナルA 準備


テーブルの再作成とデータの挿入（pkが無いので注意）

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルB


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA


データの確認後start transactionを行う

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |      10 |
|      200 |      25 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

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

ターミナルB

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

ターミナルA

mysql> update account set balance = balance + 10 where act_code = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ターミナルB


Lock wait timeoutが発生すること

mysql> update account set balance = balance + 30 where act_code = 300;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ターミナルA


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ターミナルB


後処理 ロールバックにて巻き戻すこと

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

ネクストキーロッキング(REPEATABLE-READ)


ターミナルはA,B,Cの3つ使う

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルA 準備


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルB 準備


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルC 準備


testDBに遷移後、分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA

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

ターミナルB

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

ターミナルC

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

ターミナルA

mysql> select * from account where balance <= 100 for update;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |      10 |
|      200 |      25 |
+----------+---------+
2 rows in set (0.00 sec)

ターミナルB


Lock wait timeoutとなること

mysql> insert into account(act_code,balance) values(400,0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

ターミナルA

mysql> update account set balance = 100 where balance <= 100;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

ターミナルC


Lock wait timeoutとなること

mysql> insert into account(act_code,balance) values(500,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

ターミナルA

mysql> update account set balance = 200 where balance <= 100;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |     200 |
|      200 |     200 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

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

ネクストキーロッキング(READ-COMMITTED)


ターミナルはA,B,Cの3つ使う
binlog_formatがmixedで検証すること

ターミナルA 準備


テーブルの再作成とデータの挿入

mysql> drop table account;
Query OK, 0 rows affected (0.01 sec)

mysql> create table account(act_code int primary key,balance int,index(balance));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into account(act_code,balance) values(100,10),(200,25),(300,300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ターミナルA 準備


分離レベルをREAD-COMMITTEDに設定する（デフォルト値）

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

ターミナルB 準備


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルC 準備


分離レベルをREPEATABLE-READに設定する（デフォルト値）

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


ターミナルA

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

ターミナルB

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

ターミナルC

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

ターミナルA

mysql> select * from account where balance <= 100 for update;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |      10 |
|      200 |      25 |
+----------+---------+
2 rows in set (0.00 sec)

ターミナルB

mysql> insert into account(act_code,balance) values(400,0);
Query OK, 1 row affected (0.00 sec)

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

ターミナルA


Rows matched: 3  Changed: 3に注目

mysql> update account set balance = 100 where balance <= 100;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

ターミナルC


Lock wait timeoutとなること
但しmysql5.5,5.6では再現せず

mysql> insert into account(act_code,balance) values(500,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

ターミナルA


ターミナルCが再現しないため実施しない

mysql> update account set balance = 200 where balance <= 100;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
|      100 |     200 |
|      200 |     200 |
|      300 |     300 |
+----------+---------+
3 rows in set (0.00 sec)

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