UI設計したり、ウェブシステムを開発したりします。 

SQL見るだけクエリ#043 MySQLのトランザクションの使い方について

記入日:2023-02-20 編集日:2023-02-20

SQL見るだけクエリ。今回はMySQLのトランザクションの使い方について考えてみようと思います。

この記事を読むと(約5分)
MySQLのトランザクションの使い方がわかります。

トランザクションとは

複数のSQL文を一連のひとつの固まりとして扱うことで、データの整合性を保つSQLの仕組みです。

 

トランザクションを開始するには、$pdo->beginTransaction();を実行します。
トランザクション内で実行したSQL文を確定するには、$pdo->commit();を実行します。
逆に、エラーが発生した場合は、トランザクション内で実行したSQL文を取り消すには、$pdo->rollBack();を実行します。

 

具体例として、次のようなテーブルがあるとします。

CREATE TABLE place (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  stock INT
);

INSERT INTO place VALUES (1, 'Tokyo', 1000), (2, 'Osaka', 2000);

このテーブルには、
アイテムのID、拠点、在庫数が保存されています。

今回、TokyoからOsakaに商品在庫を500個移動させる処理を考えます。
この処理を行うには、Tokyoの在庫数を500個引き、Osakaの在庫数に500個足す必要があります。
この処理の過程で、もしエラーが発生した場合どうなるでしょうか?例えば、

UPDATE place SET stock = stock - 500 WHERE id = 1; -- Tokyoから500個引く
UPDATE place SET stock = stock + 600 WHERE id = 2; -- Osakaに600個足す(間違えた!)

このように間違ったSQL文を実行してしまった場合、データベースに不整合が生じます。
Tokyoからは500個送られましたが、Osakaには600個受け取られてしまいました。
このような状況を防ぐためにSQLのトランザクション機能を使います。

START TRANSACTION; -- トランザクション開始
UPDATE place SET stock = stock - 500 WHERE id = 1; -- Tokyoから500個引く
UPDATE place SET stock = stock + 600 WHERE id = 2; -- Osakaに600個足す(間違えた!)
ROLLBACK; -- ロールバックする

このようにロールバックすると、トランザクション内で実行したSQL文はすべて取り消されます。
データベースは元の状態に戻ります。

もちろん正しく送れた場合はコミットします。

START TRANSACTION; -- トランザクション開始
UPDATE place SET balance = balance - 500 WHERE id = 1; -- Tokyoから500個引く
UPDATE place SET balance = balance + 500 WHERE id = 2; -- Osakaに500個足す(正しい!)
COMMIT; -- コミットする

このようにコミットすると、トランザクション内で実行したSQL文は確定され、
データベースは正常に更新されます。

 

実際に、ロールバックが必要となる場合は、以下のような場合があるかと思います。
例えば、存在しないテーブルやカラムを参照したり、
重複したプライマリキーを挿入したりするとエラーになります。

 

PDOでMySQLに接続してトランザクションを実行するコード例

// データベースに接続する
$dsn = 'mysql:host=localhost;dbname=test';
$user = 'root';
$password = '';
$pdo = new PDO($dsn, $user, $password);

// トランザクションを開始する
$pdo->beginTransaction();

try {
    // テーブルAからレコードを削除する
    $sql1 = "DELETE FROM tableA WHERE id = 1";
    $pdo->exec($sql1);

    // テーブルBにレコードを挿入する
    $sql2 = "INSERT INTO tableB (name, age) VALUES ('Tom', 25)";
    $pdo->exec($sql2);

    // テーブルCのレコードを更新する
    $sql3 = "UPDATE tableC SET score = score + 10 WHERE id = 2";
    $pdo->exec($sql3);

    // トランザクション内で実行したSQL文を確定する
    $pdo->commit();
} catch (PDOException $e) {
    // エラーが発生した場合は、トランザクション内で実行したSQL文を取り消す
    $pdo->rollBack();
}

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。