LOCK TABLESの記述をすると、エラーがでます

たびたびお世話になっています。

身内の拠点間のやりとりで、ショッピングカートの機能を使って、商品のやりとりを行おうとしています。

この機能の最後と思われる、販売と、販売の詳細にデータを書きこむ部分で長い間つまづいています。

一番下のお目汚しのコードで、

下の方のロックする部分と、その下のロックを解除する部分をコメントアウトすると、販売テーブル、販売の詳細のテーブルに正常にデータが書き込まれるのですが、下の方のロックと、ロックを解除する部分を入れると、

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.

このエラーが出てしまいます。

ネットで自分で調べれる範囲では、解決しませんでした。

もともとは、「気づけばプロ並みＰＨＰショッピングカート作りにチャレンジ」という本の中のコードを参考に拡張？して作っています。
この書籍のコードはすべて一度作り終えていまして、この書籍のコードでは実行してもエラーはでません。エラーがでない書籍のコードと、今回のコードの違いを長い間探していますが、LOCKテーブルのあたりの記述や、構造はほとんど同じと思います。
$rec=$stmt->fetch(PDO::FETCH_ASSOC);
などの記述も同じです。

ただし、書籍では
$dsn='mysql:dbname=shop;host=localhost';
$user='root';
$password='';
$dbh=new PDO($dsn,$user,$password);
$dbh->query('SET NAMES utf8');
のような記載があったので、そもそも大きく違うのかもしれません。

とりとめもないですが、何かヒントをいただければと思います。
よろしくお願いします。

（実際には、このテーブルの書き込み操作が終われば、最後にメールを送る部分のコードがあるのですが、検証の為削除しています。）

ここと

//テーブルをロック
$sql='LOCK TABLES sales WRITE ,sales_p WRITE,pro WRITE';
$stmt=$dbh->prepare($sql);
$stmt->execute();

ここをコメントアウトしていない時は上記の長文のエラーになります。

//テーブルのロックを解除    
$sql='UNLOCK TABLES';
$stmt=$dbh->prepare($sql);
$stmt->execute();

<?php

require_once( __DIR__.'/../common/h.php' );
   
try
{
    
$post=h($_POST)

//$sales_nameは注文した人の名前を入力しています。
$sales_name=$post['sales_name'];
//注文時点の消費税を念の為書き込んでいます。消費税のテーブルから期間で取得しています。
$sales_rateb=$post['sales_rateb']; 

$cart=$_SESSION['cart'];    
    
$men_id=$_SESSION['men_id'];
$men_no=$_SESSION['men_no'];
$men_name_disp=$_SESSION['men_name_disp'];
$men_grant=$_SESSION['men_grant'];
$men_key=$_SESSION['men_key'];

//サブキーがある場合。FCオーナーや、エリアマネージャーでログインしている場合
//サブキーをメインキーに代入
if(isset($_SESSION['subkey'])){
    $men_key=$_SESSION['subkey'];
}

//店舗アカウントで注文する場合と、店舗を統括している人が店舗画面から注文する場合を想定しています。    
if(isset($_SESSION['sub_no'])){
    $men_no=$_SESSION['sub_no'];
}
    
if(isset($_SESSION['sub_name_disp'])){
    $men_name_disp=$_SESSION['sub_name_disp'];
}

require_once( __DIR__.'/../common/database.php' ); 
$dbh=new PDO($dsn,$dbUser,$dbPass);
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

//men_noに一致する店舗の情報を取り出し    
$sql='SELECT * FROM men WHERE men_no=:men_no';
$stmt=$dbh->prepare($sql);
$stmt->bindValue(':men_no',$men_no,PDO::PARAM_STR);
$stmt->execute();    
 

$rec=$stmt->fetch(PDO::FETCH_ASSOC);
    
print $sales_name.'様<br />';
print 'ご注文ありがとうございました。<br />';
    
if(isset($rec['men_email1'])){
    print $rec['men_email1'];
    $men_email1=$rec['men_email1'];
}
if(isset($rec['men_email2'])){
    print $rec['men_email2'];
    $men_email2=$rec['men_email2'];
}
if(isset($rec['men_email3'])){
    print $rec['men_email3'];
    $men_email3=$rec['men_email3'];    
}
        
print 'に、下記のメールを送りましたのでご確認ください。<br />';
print '商品は以下の住所に発送させていただきます。<br><br />';
print $rec['men_postal1'].'-'.$rec['men_postal2'].'<br />';
print $rec['men_address'].'<br />';
print $rec['men_ate'].'<br />';    


$honbun='';
$honbun.=$sales_name."様\n\nこのたびはご注文ありがとうございました。\n";
$honbun.="\n\n";
$honbun.="ご注文商品\n";
$honbun.="-------------\n";
    
$cart=$_SESSION['cart'];
$kazu=$_SESSION['kazu'];    
$sales_tax=$_SESSION['kazu'];    
$max=count($cart);
    
    
for($i=0;$i<$max;$i++)
{
    //セッションの中身に一致する商品情報を取り出して、本文に追加。ループ。
    $sql='SELECT * FROM pro WHERE pro_no=:pro_no';
    $stmt=$dbh->prepare($sql);
    $stmt->bindValue(':pro_no',$cart[$i],PDO::PARAM_STR);
    $stmt->execute();       

    $recb=$stmt->fetch(PDO::FETCH_ASSOC);
       
    $pro_name =$recb['pro_name'];
    $pro_price =$recb['pro_price'];
    $pro_tax =$recb['pro_tax'];

    $kakaku[]=$pro_price;
    $suryo=$kazu[$i];
    $shokei=$pro_price*$suryo;
    
    $honbun.=$pro_name.' ';
    $honbun.=$pro_price.'円×';
    $honbun.=$suryo.'個=';
    $honbun.=$shokei."円\n";
}
    
//確認用 メールじゃないので改行はされない。
//print $honbun;

//テーブルをロック
$sql='LOCK TABLES sales WRITE ,sales_p WRITE,pro WRITE';
$stmt=$dbh->prepare($sql);
$stmt->execute();

//注文テーブルに、注文店舗のキー、    
$sql='INSERT INTO sales(sales_men_key,sales_name)VALUES(:sales_men_key,:sales_name)';    
$stmt=$dbh->prepare($sql);
$stmt->bindValue(':sales_men_key',$men_key,PDO::PARAM_STR);
$stmt->bindValue(':sales_name',$sales_name,PDO::PARAM_STR);
$stmt->execute();

//上の注文テーブルで書き込まれたキーを取り出す。    
$sql='SELECT LAST_INSERT_ID()';
$stmt=$dbh->prepare($sql);
$stmt->execute();
$rec=$stmt->fetch(PDO::FETCH_ASSOC);
$lastcode=$rec['LAST_INSERT_ID()'];

for($i=0;$i<$max;$i++)
{
    
    //課税かどうかを取り出す
    $sql="SELECT * FROM pro WHERE pro_no =:pro_no";
    $stmt=$dbh->prepare($sql);
    $stmt->bindValue(':pro_no',$cart[$i],PDO::PARAM_STR);    
    $stmt->execute(); 

    $rec=$stmt->fetch(PDO::FETCH_ASSOC);
    if($rec==false)
    {
        break;
    }
    $pro_tax = $rec['pro_tax'];    
    
  //販売詳細をデータベースに書き込み
    $sql='INSERT INTO sales_p(sales_code,sales_pro_no,sales_tax,sales_rate,sales_price,sales_quan) VALUES (:sales_code,:sales_pro_no,:sales_tax,:sales_rate,:sales_price,:sales_quan)';
    $stmt=$dbh->prepare($sql);
    $stmt->bindValue(':sales_code',$lastcode,PDO::PARAM_STR);    
    $stmt->bindValue(':sales_pro_no',$cart[$i],PDO::PARAM_STR);    
    $stmt->bindValue(':sales_tax',$pro_tax,PDO::PARAM_STR);
    $stmt->bindValue(':sales_rate',$sales_rateb,PDO::PARAM_STR);    
    $stmt->bindValue(':sales_price',$kakaku[$i],PDO::PARAM_STR);    
    $stmt->bindValue(':sales_quan',$kazu[$i],PDO::PARAM_STR);    
    
    $stmt->execute();
    
}

    
//テーブルのロックを解除    
$sql='UNLOCK TABLES';
$stmt=$dbh->prepare($sql);
$stmt->execute();
    
$dbh=null;

}
catch(Exception $e)
{
    print 'ただいま障害により大変ご迷惑をおかけしております。';
    print $e->getMessage();
    exit();
}
    
?>

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

回答2件

ベストアンサー

LOCK TABLES 構文は、MySQL のプリペアド・ステートメントとしてサポートされていないためです。

sql
1mysql> PREPARE stmt1 FROM 'LOCK TABLES hoge WRITE';
2ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

以下のページの
"準備済みステートメント内で許可される SQL 構文"
という項に LOCK TABLES 構文が記載されていないことからも、それが分かります。
https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax-prepared-statements.html

で、どうすれば良いかと言うと、
sales, sales_p, proテーブルがいずれも InnoDBストレージエンジン(※)であるならば、
maiko0318様の回答にある通り LOCK TABLES の代わりにトランザクションを使用するのが良いでしょう。
※ 私の知る限り、トランサクションをサポートしているのは InnoDB のみです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-introduction.html
https://dev.mysql.com/doc/refman/5.6/ja/storage-engines.html

php
1//テーブルをロック
2// $sql='LOCK TABLES sales WRITE ,sales_p WRITE,pro WRITE';
3// $stmt=$dbh->prepare($sql);
4// $stmt->execute();
5$dbh->beginTransaction();
6
7（中略）
8
9//テーブルのロックを解除    
10// $sql='UNLOCK TABLES';
11// $stmt=$dbh->prepare($sql);
12// $stmt->execute();
13$dbh->commit();
14
15$dbh=null;
16
17}
18catch(Exception $e)
19{
20    $dbh->rollback(); // 追加
21    print 'ただいま障害により大変ご迷惑をおかけしております。';
22    print $e->getMessage();
23    exit();
24}

LOCK TABLES には

取得・更新対象のレコードだけでなく、テーブル全体をアクセス不可能にしてしまう。
処理の途中にエラーが発生した際に、ロールバックできない

といったデメリットがあります。

そのため、何らかの理由により InnoDB 以外のストレージエンジンを使用していたり、
「メンテナンスのためにテーブル全体をロックしたい」
といった特別な動機がない限り、使用する必要はありません。

最後に、

この書籍のコードはすべて一度作り終えていまして

これは素晴らしいことです。

「プログラミングを習得したい」
という情熱が伺えます。

しかしながら、質問欄にご提示のコードや以下の徳丸氏のブログを読む限り、
http://hemipteron26.rssing.com/chan-13974855/all_p5.html
該当の書籍は
「とにかく１つ、動くアプリケーションを作れるようになる」
ことに主眼を置いており、セキュリティをはじめ、いわゆる「品質の高いコード」の書き方については目をつぶっている部分が多々あるように見受けます。

この本だけで満足せず、たゆまず勉強を続けられることをお勧めします。

投稿2017/02/14 01:07

KiyoshiMotoki

総合スコア4791

mi_

2017/02/14 01:22

ご回答ありがとうございます！そもそもサポートされていないとは、まったく思いもしませんでした。今日はパソコンに触れないので、明日さっそくとりかかりたいと思います。理解不足の点が多いため、ご提示いただいたリンク先や、言葉について勉強したいと思います。ありがたいお言葉にも大変励まされます。モチベーション高く勉強していきたいと思います。

mi_

2017/02/14 02:08

ご提示いただいたブログのリンク先から、書籍のセキュリティ面での修正点などの副読本がダウンロードできました。このようなきっかけを与えていただき、大変ありがたく思います。

KiyoshiMotoki

2017/02/14 02:39

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

そもそもロックテーブルの意味をご存知でしょうか？
他者によるテーブル全体を読み書き不能にするコードです。

誰かがアクセス中なら待ちますけど、時間制限でエラーで返ってきてしまいます。
そもそもロックしなくともinsertだけなら問題ないと思いますがね。

必要なのはロックではなくてトランザクション処理の方かと思いますね。

投稿2017/02/13 20:11

maiko0318

総合スコア876

mi_

2017/02/14 01:07

ご回答ありがとうございます！販売詳細のテーブルに書き込む内容に、販売テーブルの番号が必要だったので、書籍のようにロックするのがベストと思っていました。タイミング的にかぶることはかなりなさそうなのでロックしないほうがいい気がしてきましたが、ロックする場合はどのようなことを併用した方がいいか気になります。時間制限がどの程度か、意識していませんでしたが、ロックの仕組みだけでは、かぶったときに即座にエラーになりそうなことでしょうか？

mi_

2017/02/14 01:15

KiyoshiMotokiさんの回答で、必要なのはロックではなくトランザクション処理というそのものズバリのアドバイスの意味がわかりました。蛇足な質問をして申し訳ないです。

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

あなたの回答