見出し画像

「元に戻す(Ctrl+Z, ⌘Z)」ができない PostgreSQLとの付き合いかた 〜コネクション、トランザクション、コミット、ロールバック〜

watanabe_kf1983

プログラム自学案内の28回目です。前回の記事では DMLによる集計、結合操作を実践的に紹介しました。今回の記事では、データベースのコネクション、トランザクションについて紹介します。これまでの記事はこちら。

コネクションの紹介

まずはコネクションの紹介です。PostgreSQLの「複数の人と同時に会話できる」聖徳太子的な働きとともに理解しましょう。

２つ以上のクライアントとやりとり

PostgreSQLはサーバとして働きますから、２つ以上のクライアントとやり取りすることができます。試すのは簡単です。二つターミナルを起動して、それぞれのターミナルで、psqlを使ってPostgreSQLのデータベースにログインすればいいのです。

2つのクライアントが PostgreSQL の mydbデータベースに接続しているところ

データベースのコネクション

コネクション（connection, 接続）とは、クライアントがデータベースにログインしてから、ログアウトするまで続く、データベースとクライアントとのつながりを言います。

Webサーバでは、ログインしてログアウトするまでの一連の流れをセッションと言いましたね。これと違う言い方をするのには理由（ニュアンスの違い）があるのですが、細かい事は置いておいて、対比的に理解してしまいましょう。

ログインからログアウトまで

なお、今回の記事では紹介しませんが、PostgreSQLへの接続（ログイン・ログアウト）は、ネットワークごしに他のマシンから行うこともできます。

トランザクション学習の準備

つづいて、トランザクションの紹介です。まずは準備です。

サンプルシナリオ：カレンダーのおすそ分け

理解の助けとなるように、まずはデータベースの利用目的となるシナリオのサンプルを考えてみます。

登場人物
・花子さん
・花子さんのパパ

キーワード
・卓上カレンダー
・壁掛カレンダー
・おすそわけの注文
・20部

花子さんのパパは、家業として花沢不動産を営んでいます。

ところで、花沢不動産が毎年、取引先に配っているカレンダーが今年は大量に余ってしまいました（卓上、壁掛 それぞれ20部）。そこで、パパは友人と娘の友人で、希望するひとにカレンダーをおすそ分けすることにしました。

そして、パパはカレンダーが欲しいひとから受けた注文を、PostgreSQLで管理することにしたのでした。。。

二つの接続からデータベースを確認する

二つターミナルを立ち上げそれぞれ psqlでデータベースに接続し、一つの接続をパパからの接続、もう一つの接続を花子からの接続に見立てください。

左をパパからの接続、右を花子からの接続に見立てる

順番どおりに、それぞれの接続に対して、次の操作をしてください。

はじめに、パパがテーブルを作ります。

パパの操作

-- おすそわけ注文テーブル
CREATE TABLE orders (
    -- おすそ分けが欲しい人
    orderer VARCHAR,
    -- おすそ分けする品物
    item VARCHAR,
    -- 個数
    quantity DECIMAL,

    PRIMARY KEY (orderer, item)
);

次に、花子さんは、そのテーブルに学校で受付けた注文を登録します。

花子の操作

-- 友達から受け付けた注文
INSERT INTO orders VALUES ('磯野くん', '壁掛カレンダー', 1);
INSERT INTO orders VALUES ('磯野くん', '卓上カレンダー', 3);
INSERT INTO orders VALUES ('中島くん', '卓上カレンダー', 3);

-- 受け付けた注文の合計を確認
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

そのあと、パパからも、花子さんが受け付けた注文が見えるようになります。

パパの操作

-- 受け付けた注文の合計を確認
-- パパが知らないところで花子が注文を受け付けているので、
-- 部数が増えている
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

ここまででトランザクションの説明に必要な準備は完了です。

準備完了した状態

実践で学ぶトランザクション

トランザクションとは

トランザクション（transaction）とは、ひとまとめに反映されるデータの登録・更新・削除操作の集まりのことを言います。

PostgreSQLでは、次の3つのキーワードでトランザクションについての指示をします。

  • BEGIN トランザクション開始

  • COMMIT トランザクション完了

  • ROLLBACK トランザクション中止

ことに COMMIT（コミット）、 ROLLBACK（ロールバック） は PostgreSQLに限らず、どのRDBMS製品でも共通するSQL（DCL）のキーワードです。

試してみましょう。

トランザクションのコミット

次の順にSQLを実行し、COMMIT文により変更がデータベースに ひとまとめに反映される ことを確かめてみてください。

花子の操作

-- トランザクション開始
BEGIN;

-- 追加注文
INSERT INTO orders VALUES ('かおりちゃん', '壁掛カレンダー', 3);
INSERT INTO orders VALUES ('早川さん', '卓上カレンダー', 3);

-- 追加注文を受けた分だけ、部数が増えている（ようにみえる）
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

パパの操作

-- 花子の追加注文はまだパパからは見えない
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

花子の操作

-- トランザクション完了（追加注文をひとまとめにDBに反映）
COMMIT;

パパの操作

-- 花子の追加注文がDBに反映され、パパからも見えるようになる
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

トランザクションのロールバック

次の順にSQLを実行し、ROLLBACK文によりデータベースへの変更が 取りやめられる ことを確かめてみてください。

花子の操作

-- トランザクション開始
BEGIN;

-- 追加注文
INSERT INTO orders VALUES ('イタ電', '壁掛カレンダー', 200);

-- 追加注文を受けた分だけ、部数が増えている（ようにみえる）。
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

-- 壁掛けカレンダーの部数が20部を超過してはまずいので、注文受付を取りやめる
-- トランザクション中止
ROLLBACK;

-- 部数が元に戻っていることを確認
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

トランザクションの効能

これまでの操作でわかる、トランザクションの効能は次の二つです。

  • 更新中の状態を、他の接続から隠すことができる

  • 更新中の状態で、何か問題が起きたときに、更新をキャンセルできる

どちらも大切ですが、より重要なのは後者、すなわち、キャンセルできることの方 だと私は思っています。更新するSQLが1文だけであっても、BEGINでトランザクションを開始し、COMMITで反映させるというやりかたは、とても役にたちます。

SQL文はしばしば間違えるもの。でも、間違えると大変（ことに、DELETE文でWHERE以下を間違えたりすると）。なぜって、RDBには「元に戻す（Ctrl+Z, ⌘Z）」機能がないからです。ですので、更新操作はなるべく、トランザクションの中で、SELECT文でデータベースがどのように更新されたか結果を確認してから、正しければCOMMITでデータベースに反映、まちがえたらROLLBACK、としたいものです。

また、SQLの間違い以外でも、さきの「部数超過」のような例に限らず、データベースへの反映をキャンセルしたくなることは起きます（後の回で「例外処理」とともに説明する予定です）。

いちいちBEGINでトランザクションを開始するのは面倒、という人は、「PostgreSQLで AUTO COMMIT モード をオフにする」やり方を調べてください。このやり方、私は結構おすすめです。

同時更新が引き起こす問題

同時接続とトランザクションについて学んだところで、最後に、面白い問題を紹介します。

花子もパパも必ず、受け付けたカレンダーの合計が、在庫の20部に収まることを確認してから、トランザクションをCOMMITするように心がけているとします。

しかし、2人がたまたま、ほぼ同時にこれをやった場合どうなるでしょう。

花子の操作 (15時14分40秒)

BEGIN;
INSERT INTO orders VALUES ('担任の先生', '卓上カレンダー', 6);
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

パパの操作 (15時14分50秒)

BEGIN;
INSERT INTO orders VALUES ('スナックのママ', '卓上カレンダー', 6);
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;
COMMIT;
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

花子の操作 (15時15分00秒)

COMMIT;
SELECT item, SUM(quantity) AS "合計" FROM orders GROUP BY item;

2人とも、部数超過が起きないようにチェックをしてからCOMMITしているのに、在庫以上の注文を受け付けてしまいました！

チェックしたのに卓上カレンダーが20部を超えてしまう

これを防ぐには、どうすればいいでしょうか？　またそれ以前の話として、この問題が起きる可能性をどれほど深刻にとらえればいいでしょうか？　杞憂？　ササイなリスク？　致命的なリスク？

この同時更新問題に対する対策の選択肢はいくつかありますが、どれを選べば良いかはそれぞれの状況によるため、答えはありません。でですね、この問題に対して、対策案をいくつも並べることができ、かつ、その中から適切な選択肢をちゃんと選べるかどうか。これこそ、サーバサイドエンジニアとしての真価が問われる場面なのです。

次回、この課題についてさらに、深掘りして考えてみたいと思います。読者のみなさんも、次回記事を読む前に、この問題への対策を考えてみてください。

まとめと次回予告

今回の記事では、RDBMSがもつ重要な機能、トランザクション制御について紹介しました。

次回予告です。次回は、今回の記事の最後にあげた同時更新問題の、解決のポイントとなる、並列処理と排他制御についての考え方を紹介したいと思います。

このトピックは、ちょっと高度で初心者の口に合わない、サンマのハラワタみたいな味わいのトピックです。この連載で取り上げるには、本来そぐわないのかもしれません。

でも、「サンマはハラワタが一番美味しい」という人だっています。「ここが一番美味しいところ」と思っている私にダマされたと思って、少しばかり、お付き合いくださいませ。

この記事が気に入ったらサポートをしてみませんか？