Webセキュリティの小部屋

はじめに

ネットで調査していたのですが、PHP + PDO + MySQL の組み合わせのトランザクション制御をまとめてある記事が見当たりませんでした。

ですので、この記事では、PHP + PDO + MySQL の組み合わせのトランザクション制御をまとめて紹介いたします。

トランザクション制御とは

トランザクション制御とはいいますが、そもそもトランザクション制御とは何でしょうか。

簡単に言ってしまえば、データの更新時に、成功したらデータを更新して、失敗したらデータを元に戻し、データが壊れないようにすることです。

これらの性質は、原子性 (atomicity)、一貫性 (consistency)、分離性 (isolation)、持続性 (durability) の頭文字をとって「ACID」と呼ばれています。

ACID はデータを守るためのデータベース(RDBMS) の基本的な仕組みです。

PHP + PDO + MySQL でトランザクション制御を行う方法

基本的に以下の３つのメソッドを使用します。

• PDO::beginTransaction
• PDO::commit
• PDO::rollBack

各メソッドの内容は、トランザクションを開始する PDO::beginTransaction、更新を確定する PDO::commit、失敗時に更新をなかったことにする PDO::rollBack になります。

これを具体的な PHP のコードで表すと以下のようになります。

・function.php
function.php は各サンプルコードで共通の設定になります。

<?php
define("DSN","mysql:host=localhost;dbname=Test;charset=utf8"); //文字エンコーディングを必ず指定
define("USERNAME", "user01");
define("PASSWORD", "pass");
?>

・transaction.php
transaction.php では、try ブロックを2重にしてあります。2重にする理由は、データ更新時の成功か失敗かでコミットするかロールバックするか区別できるようにするためです。

<?php
  require_once("function.php");

  try{

    $dbh = new PDO(DSN, USERNAME, PASSWORD);
 
    // 静的プレースホルダを指定
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    // エラー発生時に例外を投げる
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    //パラメータ
    $id = 1;

    //プリペアドステートメント
    $stmt = $dbh->prepare("UPDATE USERS SET COUNT = COUNT + 1 WHERE ID = ?");

    //トランザクション処理を開始
    $dbh->beginTransaction();
  
    try {

      $stmt->bindParam(1, $id, PDO::PARAM_INT);
      $stmt->execute();

      //コミット
      $dbh->commit();
      
    }catch(PDOException $e){
    
      //ロールバック
      $dbh->rollback();

      throw $e;
    }
 
  } catch(PDOException $e){
    echo $e->getMessage();
  }

?>

トランザクション分離レベル(Isolation Level)

トランザクションには、トランザクション分離レベルというものがあります。

トランザクション分離レベルによって、トランザクションが複数同時に処理された時の動作が変わってきます。

まず、トランザクションの同時実行時のリードの種類には以下のものがあります。

• ダーティーリード
  • 他のトランザクションのセッションでコミットされていない更新データを読み込む
• ファジーリード
  • 他のトランザクションのセッションでコミットした更新データを読み込む。
• ファントムリード
  • 他のトランザクションのセッションが追加したデータを読み込む

また、トランザクション分離レベルは以下のようになります(MySQL)。

	ダーティーリード	ファジーリード	ファントムリード
READ UNCOMMITED	起きる	起きる	起きる
READ COMMITTED	起きない	起きる	起きる
REPEATABLE READ	起きない	起きない	起きない
SERIALIZABLE	起きない	起きない	起きない

※ REPEATABLE READ のファントムリードが起きないのは、MySQL の仕様
※ MySQL のデフォルトトランザクション分離レベルは REPEATABLE READ
※ ANSI/ISO SQL の既定では、REPEATABLE READ でファントムリードは起きる

PHP + PDO から MySQL にトランザクション分離レベルを設定するには、以下のように行います。サンプルコードには、READ COMMITTED を指定していますが、この部分を書き換えることで制御を変更することができます。

・isolation.php

<?php
  require_once("function.php");

  try{

    $dbh = new PDO(DSN, USERNAME, PASSWORD);
 
    // 静的プレースホルダを指定
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    // エラー発生時に例外を投げる
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    //パラメータ
    $id = 1;

    //トランザクション分離レベル設定
    $dbh->exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;");

    //プリペアドステートメント
    $stmt = $dbh->prepare("UPDATE USERS SET COUNT = COUNT + 1 WHERE ID = ?");
    
    //トランザクション処理を開始
    $dbh->beginTransaction();

    try {

      $stmt->bindParam(1, $id, PDO::PARAM_INT);
      $stmt->execute();

      //コミット
      $dbh->commit();

    }catch(PDOException $e){
    
      //ロールバック
      $dbh->rollback();

      throw $e;
    }
 
  } catch(PDOException $e){
    echo $e->getMessage();
  }

?>

データロック

複数の処理が同時にトランザクションでデータに更新を行うと、データに不整合が発生してしまいます。

そのために、MySQL では SQL で以下のロックを提供しています。

• 共有(s)ロック
  • トランザクションによる行の読み取りが許可される
• 排他(x)ロック
  • トランザクションによる行の更新または削除が許可される

トランザクションの SQL で、SELECT … LOCK IN SHARE と宣言することで、共有ロックをかけることができます。他のセッションも読み取ることができますが、最初のトランザクションがコミットするまで変更することができません。

トランザクションの SQL で、 SELECT … FOR UPDATE と宣言することで、排他ロックをかけることができます。排他ロックがかかっている間は、他のロックはブロックされます。

トランザクションがコミットまたはロールバックされると、全てのロックはクリアされます。

なお、カウントアップなどの処理では、排他ロックが必要なので、SELECT … FOR UPDATE を使用する必要があります。SELECT … LOCK IN SHARE では不整合が発生するのでご注意ください。

実際のサンプルコードは以下のようになります。

・lock.php

<?php
  require_once("function.php");

  try{

    $dbh = new PDO(DSN, USERNAME, PASSWORD);
 
    // 静的プレースホルダを指定
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    // エラー発生時に例外を投げる
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    //パラメータ
    $id = 1;

    //トランザクション処理を開始
    $dbh->beginTransaction();

    try {
      //プリペアドステートメント / ロック
      $stmt1 = $dbh->prepare("SELECT * FROM  USERS WHERE ID = ? FOR UPDATE");
      //$stmt1 = $dbh->prepare("SELECT * FROM  USERS WHERE ID = ? LOCK IN SHARE MODE”); //不整合が起きる

      $stmt1->bindParam(1, $id, PDO::PARAM_INT);
      $stmt1->execute();


      //プリペアドステートメント
      $stmt = $dbh->prepare("UPDATE USERS SET COUNT = COUNT + 1 WHERE ID = ?");
      $stmt->bindParam(1, $id, PDO::PARAM_INT);
      $stmt->execute();

      //コミット
      $dbh->commit();

    }catch(PDOException $e){
    
      //ロールバック
      $dbh->rollback();

      throw $e;
    }
 
  } catch(PDOException $e){
    echo $e->getMessage();
  }

?>

おわりに

駆け足でしたが、PHP + PDO + MySQL のトランザクション制御方法を網羅的に紹介できたと思うのですが、いかがだったでしょうか。

トランザクション制御は、簡単なようですが、デッドロックを起こさない事も含め考慮点が多いです。

この記事が、PHP + PDO + MySQL のトランザクション制御方法のサンプルになり、みなさんのいろいろな調査の負担を減らすことができれば幸いです。

参考サイト

• PHPでデータベースに接続するときのまとめ - Qiita
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.6 SET TRANSACTION 構文
• MySQL :: MySQL 5.6 リファレンスマニュアル :: MySQL 用語集
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.3 InnoDB のロックモード
• MySQL を使ってトランザクション分離レベルの違いを試す | CUBE SUGAR STORAGE
• 同時アクセスについて - PHP | 教えて！goo
• tree-tips: MySQLのトランザクション分離レベル | MySQL
• PHP - bindParam()とbindValue()の違い - Qiita

スポンサーリンク

カテゴリー:ブログ

Twitter でも、いろんな情報を発信しています。
@fnyaさんをフォロー