Skip to content

Instantly share code, notes, and snippets.

@hironomiu

hironomiu/gist:189c5304e34b5893ad31

Last active November 28, 2017 07:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save hironomiu/189c5304e34b5893ad31 to your computer and use it in GitHub Desktop.
Save hironomiu/189c5304e34b5893ad31 to your computer and use it in GitHub Desktop.
Download ZIP
Treasure2016：DB トランザクション＆ロック初級(公開版)
Raw
gistfile1.md

トランザクション&ロック初級

本演習はDatabase:testを用いて行うこと。演習では2つのターミナルで各々mysql clientでDatabase:testに接続すること。

タイムスケジュール目安(122分)

演習：自動コミット(演習分)(解説2分)
演習：手動コミット(commit編)(演習5分)(解説2分)
演習：手動コミット(rollback編)(演習5分)(解説2分)
演習：悲観ロック(SELECT FOR UPDATE)(演習5分)(解説2分)
演習：ロック(一般的なINSERT、UPDATE)(演習5分)(解説2分)
Question　ロストアップデート対策(回答15分)(解説5分)
演習：デッドロック(演習5分)(解説2分)
Question　デッドロック(回答15分)(解説5分)

準備

term-a

$ mysql -u demouser -p

mysql> use test

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> prompt term-a > ;
PROMPT set to 'term-a > '
term-a >

term-b

$ mysql -u demouser -p

mysql> use test

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> prompt term-b > ;
PROMPT set to 'term-b > '
term-b >

演習：自動コミット

MySQLではトランザクションの自動コミットはデフォルトで有効になっています

自動コミットの確認

term-a,bともに1(自動コミット有効)が返ること(デフォルト設定)

term-a(合わせてテーブル作成)

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

term-a > create table transaction_exercise( id int unsigned auto_increment ,name varchar(10) ,primary key(id));
Query OK, 0 rows affected (0.06 sec)

term-b

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

データの確認

term-a

0件であること

term-a > select * from transaction_exercise;
Empty set (0.00 sec)

term-b

0件であること

term-b > select * from transaction_exercise;
Empty set (0.00 sec)

データ挿入

term-a

rollback後もデータがあること(=自動コミットされている)

term-a > insert into transaction_exercise values(null,'hoge');
Query OK, 1 row affected (0.01 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

term-a > rollback;
Query OK, 0 rows affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

term-b

term-aで挿入したデータが見えること

term-b >select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

余談

term-a

エラー

term-a > insert into transaction_exercise values(1,'hoge');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

どういう状態でしょう？

term-a > update transaction_exercise set name = 'fuga' where id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

分離レベルの確認(REPEATABLE-READがデフォルト)

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

演習：手動コミット(commit編)

手動コミットの設定

term-a,bともに0(手動コミット)にする

term-a

term-a > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-b

term-b >set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-a

挿入したid:2,fugaが出力されること

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

term-a > insert into transaction_exercise values (null,'fuga');
Query OK, 1 row affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-b

term-aが未コミットのためid:1のみ出力されること

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

term-a

commit

term-a > commit;
Query OK, 0 rows affected (0.01 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-b

なぜ2が表示されないのか興味のある人は調べてみましょう

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
+----+------+
1 row in set (0.00 sec)

commitをするとid:2が出力される

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

演習：手動コミット(rollback編)

データは 演習：手動コミット(commit編)から続く

手動コミットの設定

term-a,bともに0(手動コミット)にする

term-a

term-a > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

term-a > select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-b

term-b >set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

term-b >select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-a

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-b

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-a

term-a > insert into transaction_exercise values(null,'piyo');
Query OK, 1 row affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
|  3 | piyo |
+----+------+
3 rows in set (0.00 sec)

term-b

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-a

term-a > rollback;
Query OK, 0 rows affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-b

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-b > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+
2 rows in set (0.00 sec)

余談

AUTO_INCREMENTはcommit,rollbackに関わらず消費される

term-a > insert into transaction_exercise values(null,'piyo');
Query OK, 1 row affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
|  4 | piyo |
+----+------+
3 rows in set (0.00 sec)

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
|  4 | piyo |
+----+------+
3 rows in set (0.00 sec)

演習：悲観ロック(SELECT FOR UPDATE)

term-a,bともに0(手動コミット)にする

term-a

term-a > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-b

term-b > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-a

term-a > select * from transaction_exercise for update;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
|  4 | piyo |
+----+------+
3 rows in set (0.00 sec)

term-b

エラーとなる(エラーの理由を考えてみよう)

term-b > insert into transaction_exercise values (null,'hogehoge');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

term-a

term-a > insert into transaction_exercise values(null,'fugafuga');
Query OK, 1 row affected (0.00 sec)

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

tran-b

エラーとならず成功する

term-b > insert into transaction_exercise values (null,'hogehoge');
Query OK, 1 row affected (0.00 sec)

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-b > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | piyo     |
|  6 | fugafuga |
|  7 | hogehoge |
+----+----------+
5 rows in set (0.00 sec)

余談

tran-a

term-a > select * from transaction_exercise where id = 4 for update;
+----+------+
| id | name |
+----+------+
|  4 | piyo |
+----+------+
1 row in set (0.00 sec)

tran-b

updateはエラーとなる

term-b > insert into transaction_exercise values (null,'bar');
Query OK, 1 row affected (0.00 sec)

term-b > commit;
Query OK, 0 rows affected (0.01 sec)

term-b > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | piyo     |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
+----+----------+
6 rows in set (0.00 sec)

term-b > update transaction_exercise set name = 'piyopiyo' where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

tran-a

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

演習：ロック(一般的なINSERT、UPDATE)

term-a,bともに0(手動コミット)にする

term-a

term-a > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-b

term-b > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-a

term-a > select max(id) from transaction_exercise;
+---------+
| max(id) |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

term-a > insert into transaction_exercise values(8+1,'hogehoge');
Query OK, 1 row affected (0.00 sec)

term-b

エラーとなる

term-b > insert into transaction_exercise values(8+1,'hogehoge');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

term-a

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | piyo     |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
|  9 | hogehoge |
+----+----------+
7 rows in set (0.00 sec)

term-b

エラーとなる

term-b > insert into transaction_exercise values(8+1,'hogehoge');
ERROR 1062 (23000): Duplicate entry '9' for key 'PRIMARY'
term-b > rollback;
Query OK, 0 rows affected (0.00 sec)

term-a

term-a > select * from transaction_exercise where name = 'piyo';
+----+------+
| id | name |
+----+------+
|  4 | piyo |
+----+------+
1 row in set (0.00 sec)

term-a > update transaction_exercise set name = 'piyopiyo' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

term-b

エラーとなる

term-b > select * from transaction_exercise where name = 'piyo';
+----+------+
| id | name |
+----+------+
|  4 | piyo |
+----+------+
1 row in set (0.00 sec)

term-b > update transaction_exercise set name = 'biyo' where id = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

term-a

piyo -> piyopiyoに

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-a > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | piyopiyo |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
|  9 | hogehoge |
+----+----------+
7 rows in set (0.00 sec)

tran-b

piyopiyo -> biyoに(ロストアップデートとなるケースあり)

term-b > update transaction_exercise set name = 'biyo' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-b > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | biyo     |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
|  9 | hogehoge |
+----+----------+
7 rows in set (0.00 sec)

Question　ロストアップデート対策(15分)

term-[a,b]ともにbiyoの行をselect文で突き止め、その行に対してupdateしましょう。 term-aはnameをpiyoに、term-bはnameをbiyobiyoにupdateしましょう。 その際に最初に更新した結果が残る(言い換えると次の更新が出来ない、もしくは空振りする)よう term-[a-b]で同じselect文、update文を作成してください。

余裕のある人は回答を2例用意しましょう

演習：デッドロック

term-a,bともに0(手動コミット)にする

term-a

term-a > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-a > commit;
Query OK, 0 rows affected (0.00 sec)

term-b

term-b > set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

term-b > commit;
Query OK, 0 rows affected (0.00 sec)

term-a

term-a > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | biyo     |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
|  9 | hogehoge |
+----+----------+
7 rows in set (0.00 sec)

term-a > update transaction_exercise set name = 'geho' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

term-b

term-b > select * from transaction_exercise;
+----+----------+
| id | name     |
+----+----------+
|  1 | hoge     |
|  2 | fuga     |
|  4 | biyo     |
|  6 | fugafuga |
|  7 | hogehoge |
|  8 | bar      |
|  9 | hogehoge |
+----+----------+
7 rows in set (0.00 sec)

term-b > update transaction_exercise set name = 'gafu' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

term-a

term-a > update transaction_exercise set name = 'gafugafu' where id = 2;
待ち処理中に�以下の処理を続ける

term-b

Deadlockエラーが出力されること

term-b > update transaction_exercise set name = 'gehogeho' where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
term-b > rollback;
Query OK, 0 rows affected (0.00 sec)

term-a

正常終了すること

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

term-a > rollback;
Query OK, 0 rows affected (0.00 sec)

Question　デッドロック

INSERT、DELETEを使ってデッドロックを発生させてみましょう。デッドロック後はterm-a,bともにrollbackしましょう。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment