mysql ロッグ処理

こんにちは、

今、PHPで注文商品を注文テーブルと注文明細テーブルに登録する機能を作っております。
mysqlのデータベースの不整合を防ぐために、ロックをかけるとします。

一覧の処理をする前にロックすると思いますが、下記のようなエラーが発生してしまいました。

接続エラー：SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

ロック処理について、ネットでいろいろ調べましたが、あまり有力な情報が得られませんでした。私の調べ方よくないかもしれません。
こちらで、どなた方ご教授していただければありがたいでございます。
何卒宜しくお願い致します。

ソースコート：

session_start();
session_regenerate_id(true);
require_once 'lib/functions.php';


//DBへ接続
try{
    //postされたデータを代入
    $onamae = isset($_POST['name']) ? $_POST['name'] : '';
    $email = isset($_POST['email']) ? $_POST['email'] : '';
    $postal1 = isset($_POST['postal1']) ? $_POST['postal1'] : '';
    $postal2 = isset($_POST['postal2']) ? $_POST['postal2'] : '';
    $address = isset($_POST['address']) ? $_POST['address'] : '';
    $tel = isset($_POST['tel']) ? $_POST['tel'] : '';
    
    $honbun = '';
    $honbun .= $onamae. "様\n\nこの度はご注文ありがとうございました。\n";
    $honbun .= "\n";
    $honbun .= "ご注文商品\n";
    $honbun .= "-------------------\n";
    
    $cart = $_SESSION['cart'];
    $kazu = $_SESSION['kazu'];
    $max = count($cart);
    //var_dump($cart);
    
    //$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8', 'root', '');
    $pdo = pdo();
    $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
//ロック処理
    $sql = 'lock tables dat_sales write,dat_sales_product write';
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    for($i=0; $i<count($cart); $i++){
        $sql = 'select name,price,gazou from mst_product where code=?';
        $product = $pdo->prepare($sql);
        $product->bindValue(1, $cart[$i]);
        $product->execute();
        
        $rec = $product->fetch(PDO::FETCH_ASSOC);
        //var_dump($rec);
        if(isset($rec)){
            $name = $rec['name'];
            $price = $rec['price'];
            $suryo = $kazu[$i];
            $shokei = $price * $suryo;
            //var_dump($name);
        }
        
        $honbun .= $name.' ';
        $honbun .= $price. '円*';
        $honbun .= $suryo.'個 =';
        $honbun .= $shokei."円\n";
        $kakaku[] = $price;
        
    }
    //var_dump($price);
    $honbun .= "送料は無料です。\n";
    $honbun .= "--------------------\n";
    $honbun .= "\n";
    $honbun .= "代金は以下の口座にお振込ください。\n";
    $honbun .= "ロクマル銀行　やさい支店　普通口座　１２３４５６７\n";
    $honbun .= "入金確認が取れ次第、梱包、発送させていていただきます。\n";
    $honbun .= "\n";
    $honbun .= "▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫\n";
    $honbun .= "〜テスト〜\n";
    $honbun .= "\n";
    $honbun .= "テスト１２３\n";
    $honbun .= "電話番号０８０１２２２３３５５\n";
    $honbun .= "メール送信側\n";
    $honbun .= "▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫▫\n";
    $title = "ご注文ありがとうございます。";
    $header = "From:".$email;//お店側のメールアドレス
    $honbun = html_entity_decode($honbun, ENT_QUOTES, 'UTF-8');
    mb_language('Japanese');
    mb_internal_encoding('UTF-8');
    mb_send_mail('送信先',$title,$honbun,$header);
    
    $sql = 'insert into dat_sales(code_member,name,email,postal1,postal2,address,tel)values(?,?,?,?,?,?,?)';
    $product = $pdo->prepare($sql);
    $product->bindValue(1, 0);
    $product->bindValue(2, $onamae);
    $product->bindValue(3, $email);
    $product->bindValue(4, $postal1);
    $product->bindValue(5, $postal2);
    $product->bindValue(6, $address);
    $product->bindValue(7, $tel);
    $product->execute();
    
    $sql = 'select last_insert_id()';
    $product = $pdo->prepare($sql);
    $product->execute();
    $rec = $product->fetch(PDO::FETCH_ASSOC);
    $lastcode = $rec['last_insert_id()'];
    var_dump($kakaku);
    
    for($i=0; $i<count($cart); $i++){
        $sql = 'insert into dat_sales_product(code_sales,code_product,price,quantity)values(?,?,?,?)';
        $product = $pdo->prepare($sql);
        $product->bindValue(1, $lastcode);
        $product->bindValue(2, $cart[$i]);
        $product->bindValue(3, $kakaku[$i]);
        $product->bindValue(4, $kazu[$i]);
        $product->execute();
        var_dump($cart[$i]);
        var_dump($kakaku[$i]);
        var_dump($kazu[$i]);
    }
    //ロック解除
    $sql = 'unlock tables';
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    //var_dump();
    $pdo = null;
}catch(Exception $e){
    
    die('接続エラー：' . $e->getMessage());
}

require 'header.php';
?>

<div class="">
    <!--<p><?php echo nl2br($honbun); ?></p>-->
    
    <!--<p><?php echo h($name); ?>様</p>-->
    <!--<p>この度、ご注文ありがとうございました。</p>-->
    <!--<p>ご注文内容は<a href="<?php echo h($email); ?>"><?php echo h($email); ?></a>メールにてご案内いたしましたので、ご確認ください。</p>-->
    <!--<p>なお、商品は、下記の住所にご発送させていただきます。</p>-->
    <!--<p>〒<?php echo h($postal1); ?>-<?php echo h($postal2); ?></p>-->
    <!--<p><?php echo h($address); ?></p>-->
    <!--<p><?php echo h($tel); ?></p>-->
</div>

<?php
require 'footer.php';
コード

行動規範の内容に同意します

回答1件

ベストアンサー

エラー自体はプリペアドステートメントで対応していない LOCK TABLES クエリを実行しようとしているために発生していると思われます。

参考：MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.5 準備済みステートメントのための SQL 構文

というわけで、直接クエリを実行すればエラーは無くなると思います。

php
1$sql = 'lock tables dat_sales write,dat_sales_product write';
2$pdo->query($sql);

ですが、現代のPHP+MySQLにおいてWebアプリケーションの一般処理でテーブルをロックすることはまずありません。またテーブルをロックしたとしても dat_sales にINSERTした直後にアプリやDBの処理が止まって仕舞った場合、"注文番号はあるが何も商品を買っていない" という不整合が容易に発生します。

そのような不整合を防ぐにはトランザクションという機能を使います。トランザクションを使うことで完全な失敗（DBに何も記録しない）か完全な成功（DBに全てのデータが漏れなく記録される）のどちらかになり、中途半端に更新されてDBの情報が破綻するといったトラブルを防ぐことができます。データベースを使う上で極めて重要な機能・概念ですので、ぜひ勉強してマスターして下さい。

PHP: トランザクションおよび自動コミット - Manual

あと細かいところですがいくつか：

DB の最後に挿入された行のIDは、PDOの lastInsertId() を使って一発で取得できます。

php
1// before
2$sql = 'select last_insert_id()';
3$product = $pdo->prepare($sql);
4$product->execute();
5$rec = $product->fetch(PDO::FETCH_ASSOC);
6$lastcode = $rec['last_insert_id()']
7
8// after
9$lastcode = $pdo->lastInsertId();

同じ文法のプリペアドステートメントは使い回せるのでループの外に出しましょう。データベースの負荷が減り、パフォーマンスもあがります。

php
1// Before
2for($i=0; $i<count($cart); $i++){
3    $sql = 'insert into dat_sales_product(code_sales,code_product,price,quantity)values(?,?,?,?)';
4    $product = $pdo->prepare($sql);
5    $product->bindValue(1, $lastcode);
6    $product->bindValue(2, $cart[$i]);
7    //...//
8    $product->execute();
9}
10
11// After
12$sql = 'insert into dat_sales_product(code_sales,code_product,price,quantity)values(?,?,?,?)';
13$product = $pdo->prepare($sql);
14for($i=0; $i<count($cart); $i++){
15    $product->bindValue(1, $lastcode);
16    $product->bindValue(2, $cart[$i]);
17    //...//
18    $product->execute();
19}