【MySQL】トランザクションを少し勉強しよう
こんばんは、葛の葉です。
今回はMySQLを使ってトランザクションを勉強しようと思います。
トランザクションとは
データベースはデータを保存しています。プログラマーやプログラムを使うユーザーはデータベースに対して、データを閲覧、追加、変更、削除を行いますが、そういったデータ操作についての処理を一つに纏めることをトランザクションと呼びます。トランザクションは最終的にその処理をデータベースに反映させる（コミットする）か、それらの処理を拒否する（ロールバックする）のどちらかを実施します。
トランザクションにおいては、纏まった処理を実施している最中に、他のユーザー（他のトランザクション）がデータベースにアクセスした際に、どういうデータの見え方をする必要があるかが問題となるケースがあります。
例えば、本屋さんのECサイトがあったとして、本の在庫数を管理するデータベースを持ってるとします。
| ID | 題名 | 在庫数 |
|---|---|---|
| 1 | ソロモンの鍵 | 30 |
| 2 | 黒い雌鳥 | 19 |
| 3 | ネクロノミコン | 2 |
太郎さんがネクロノミコンを買い物かごに入れたときに、花子さんがこの表を確認したとしましょう。そのときにネクロノミコンの数は1であるか2であるか、ということになります。
使用する環境はこちら
DockerComposeを使用します。リポジトリを以下に置いておくので使ってください。
リポジトリには今回使用するテーブルを用意しました。testデータベース内にbooksテーブルを用意しています。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | books | +----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM books; +----+-----------------+--------+ | ID | Name | Number | +----+-----------------+--------+ | 1 | Soromon no Kagi | 30 | | 2 | Kuroi Medori | 19 | | 3 | Nekuro Nomikon | 2 | +----+-----------------+--------+ 3 rows in set (0.00 sec)
MySQLのクライアントを起動します。
$ git clone https://github.com/kuzunoha-ne/sqlLearn.git $ cd sqlLeran $ docker-compose up -d $ docker-compose exec db mysql -p Enter password: root mysql>prompt one> PROMPT set to 'taro> ' taro>
トランザクション分離の勉強なので２つのターミナルがほしいですね。
$ docker-compose exec db mysql -p Enter password: root mysql> prompt two> PROMPT set to 'hanako> ' hanako>
こうしてtaro>というCLIとhanako>というCLIの２つが出来たと思います。それぞれ太郎さんと花子さんというようにします。
デフォルトは1構文に対して1トランザクションである。
まずはdatabaseを選択します。
taro> USE test; hanako> USE test;
そうしたらSQL文で色々みてみましょう。
taro> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 2 | +----+----------------+--------+ 1 row in set (0.00 sec) hanako> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 2 | +----+----------------+--------+ 1 row in set (0.00 sec) taro> INSERT INTO books VALUES(4, "hogehoge", 10); hanako> INSERT INTO books VALUES(5, "piyopiyo", 8); taro> SELECT * FROM books; +----+-----------------+--------+ | ID | Name | Number | +----+-----------------+--------+ | 1 | Soromon no Kagi | 30 | | 2 | Kuroi Medori | 19 | | 3 | Nekuro Nomikon | 2 | | 4 | hogehoge | 10 | | 5 | piyopiyo | 8 | +----+-----------------+--------+ 5 rows in set (0.00 sec) hanako> SELECT * FROM books; +----+-----------------+--------+ | ID | Name | Number | +----+-----------------+--------+ | 1 | Soromon no Kagi | 30 | | 2 | Kuroi Medori | 19 | | 3 | Nekuro Nomikon | 2 | | 4 | hogehoge | 10 | | 5 | piyopiyo | 8 | +----+-----------------+--------+ 5 rows in set (0.00 sec)
MySQLにおいてはSQL文を実行するたびに自動でコミットが行われるオートコミットという機能がデフォルトでついています。この機能の関係で、taroとhanakoが明示的にコミットを実施しなくてもコミットは行われています。一方で自動でコミットを実施するので、「やっぱりやめた」とするロールバックをすることは出来ません。
taro> rollback; Query OK, 0 rows affected (0.00 sec) taro> SELECT * FROM books; # 結果が変わらない +----+-----------------+--------+ | ID | Name | Number | +----+-----------------+--------+ | 1 | Soromon no Kagi | 30 | | 2 | Kuroi Medori | 19 | | 3 | Nekuro Nomikon | 2 | | 4 | hogehoge | 10 | | 5 | piyopiyo | 8 | +----+-----------------+--------+ 5 rows in set (0.00 sec)
トランザクションを明示的にしてみる。
taroがトランザクションをかけます。そして、ネクロノミコンを買おうとします。そのトランザクション中にhanakoさんがテーブルの中身を検索します。
taro> start transaction; Query OK, 0 rows affected (0.00 sec) taro> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 2 | +----+----------------+--------+ 1 row in set (0.00 sec) taro> UPDATE books SET Number=Number-1 WHERE ID=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 taro> SELECT * FROM books WRERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 1 | +----+----------------+--------+ 1 row in set (0.00 sec) hanako> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 2 | +----+----------------+--------+ 1 row in set (0.00 sec) taro> commit; Query OK, 0 rows affected (0.00 sec) taro> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 1 | +----+----------------+--------+ 1 row in set (0.00 sec) hanako> SELECT * FROM books WHERE ID=3; +----+----------------+--------+ | ID | Name | Number | +----+----------------+--------+ | 3 | Nekuro Nomikon | 1 | +----+----------------+--------+ 1 row in set (0.00 sec)
taroさんがcommitする前にhanakoさんがSELECTするとネクロノミコンの数は2になってます。commit後はhanakoさんがSELECTすると1になっています。
taro> start transaction; Query OK, 0 rows affected (0.00 sec) taro> UPDATE books SET Number=Number-1 WHERE ID=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 taro> SELECT * FROM books WHERE ID=2; +----+--------------+--------+ | ID | Name | Number | +----+--------------+--------+ | 2 | Kuroi Medori | 18 | +----+--------------+--------+ 1 row in set (0.00 sec) taro> rollback; Query OK, 0 rows affected (0.01 sec) taro> SELECT * FROM books WHERE ID=2; +----+--------------+--------+ | ID | Name | Number | +----+--------------+--------+ | 2 | Kuroi Medori | 19 | +----+--------------+--------+ 1 row in set (0.00 sec)
