- ターミナルは二つ起動すること以後ターミナルA、ターミナルBと呼ぶ
- master,slave構成の場合はmasterのみで作業を行います
# 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テーブルが作成されること
# 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テーブルが参照できること
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
- 1(自動コミット有効)が返ること(デフォルト設定)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
- 1(自動コミット有効)が返ること(デフォルト設定)
mysql> select * from tran_test;
Empty set (0.00 sec)
- データが存在しないこと
mysql> select * from tran_test;
Empty set (0.00 sec)
- データが存在しないこと
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)
- 挿入したデータが出力されること
mysql> select * from tran_test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
- 自動コッミとによりターミナルAで挿入されたレコード(1)が返ること
- 自動コミットをオフにする
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が返ること
mysql> select * from tran_test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
- 1のみ返ること
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tran_test;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
- 1,2が返ること
- 自動コミットをオフで実施する。自動コミットオンの場合は前段で実施したオフのコマンドを実施する
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が存在すること
mysql> select * from tran_test;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
- 3が存在しないこと
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が取り消されること
mysql> select * from tran_test;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
- 特に変わりがないこと
- 自動コミットオフ(@@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)
- 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)
- ターミナル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)
mysql> select * from tbl1 for update;
Empty set (0.00 sec)
mysql> insert into tbl1 values(10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- エラーとなること(前段で確認したinnodb_lock_wait_timeoutの時間分待つ)
- ターミナル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)
- 準備 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)
- 準備(autocommitオフを考慮)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | abcd | 1 |
+----+------+------+
1 row in set (0.01 sec)
mysql> select id,col1,col2 from tbl2 where col1 = 'abcd';
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | abcd | 1 |
+----+------+------+
1 row in set (0.00 sec)
- 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に変わっていること(更新の成功)
- 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)
テーブルの作成とデータの挿入
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)
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)
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)
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)
mysql> select * from account;
+----------+---------+
| act_code | balance |
+----------+---------+
| 100 | 6 |
| 200 | 25 |
| 300 | 300 |
+----------+---------+
3 rows in set (0.00 sec)
- 分離レベルを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)
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)
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)
- 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)
- 6に戻っていること
mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
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)
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)
- 20が返ること
mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
| 20 |
+---------+
1 row in set (0.00 sec)
- 分離レベルを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)
- 分離レベルを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)
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> 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)
- 400が存在すること
mysql> select act_code from account;
+----------+
| act_code |
+----------+
| 400 |
| 100 |
| 200 |
| 300 |
+----------+
4 rows in set (0.00 sec)
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
- 分離レベルを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)
mysql> select act_code from account;
+----------+
| act_code |
+----------+
| 100 |
| 200 |
| 300 |
+----------+
3 rows in set (0.00 sec)
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)
- 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)
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
- 分離レベルを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)
mysql> insert into account values(1000,0);
Query OK, 1 row affected (0.00 sec)
- 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;
- 待ち状態中に実行し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
- ターミナルAでエラー出力後、応答が返ること
Query OK, 0 rows affected (12.69 sec)
Rows matched: 0 Changed: 0 Warnings: 0
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- 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
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
- 分離レベルを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)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
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)
mysql> select balance from account where act_code = 100;
+---------+
| balance |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
- update後、待ちが発生すること
mysql> update account set balance = balance + 10 where act_code = 100;
- ターミナル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
- ターミナルAでDeadlock後に応答が返ること
Query OK, 1 row affected (18.39 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- 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
- テーブルの再作成とデータの挿入(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)
- 分離レベルを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)
- 分離レベルを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)
- データの確認後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)
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
- Lock wait timeoutが発生すること
mysql> update account set balance = balance + 30 where act_code = 300;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- 後処理 ロールバックにて巻き戻すこと
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
- ターミナルはA,B,Cの3つ使う
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
- 分離レベルを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)
- 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)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where balance <= 100 for update;
+----------+---------+
| act_code | balance |
+----------+---------+
| 100 | 10 |
| 200 | 25 |
+----------+---------+
2 rows in set (0.00 sec)
- 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)
mysql> update account set balance = 100 where balance <= 100;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
- 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)
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)
- ターミナルはA,B,Cの3つ使う
- binlog_formatがmixedで検証すること
- テーブルの再作成とデータの挿入
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)
- 分離レベルを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)
- 分離レベルを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)
- 分離レベルを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)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where balance <= 100 for update;
+----------+---------+
| act_code | balance |
+----------+---------+
| 100 | 10 |
| 200 | 25 |
+----------+---------+
2 rows in set (0.00 sec)
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)
- 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
- 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)
- ターミナル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)