3. Cloud SQLのトランザクション処理で在庫数量を更新
ここからは、GAEクラウド側の処理に入ります。スマートフォンから入力されたデータはGAEに送信され、Cloud SQLのトランザクション処理によってMySQLのレコードを更新します。
トランザクション処理の排他制御機能
データベースは同時に複数のユーザからアクセスされることも多くあります。このような場合、複数ユーザからのデータベース更新処理で、更新データの整合性が損なわれる場合もあり、それを防止する機能が排他制御です。
ここではトランザクション処理によって、入出庫数量更新による在庫数量の変化をデータベースに記録する例で考えてみます。
図13は在庫数量の更新でトランザクション処理を使用しない場合の処理の流れです。図で最初に担当者Aが在庫数量を更新するために現在の在庫数500個を読み込み、入庫した20個と合計して在庫数量を520個にして書き込みます。しかし担当者Bも50個の入庫処理を行っているため、在庫数量更新のために現在の在庫数量を読み込みますが、この時点ではまだ担当者Aの更新数は反映されておらず、500個がデータベースから読み込まれます。その後担当者Bはすぐに計算を行って入庫数量50個を加算した550個でデータベースの在庫数量を更新します。一方担当者Aは計算に手間取ってしまい、担当者Bの書き込みのあとに、入庫数量20個を加算した520個で更新します。この場合実際には20個と50個で合計70個の在庫増加があったわけで、更新後の正確な在庫数量は570個なのですが、この処理の流れでは520個となり、50個の食い違いが発生してしまいます。
データベース管理システムには、このような問題に対応するためにロック（Lock）とアンロック（Unlock）の機能があります。データベースをアクセスするプログラム内でロックを実行すると、対象となるテーブルや行レコードを他のユーザから、読み取ることや更新することができないようになります。アンロックではロックされているテーブルや行レコードのロックを解除します。
このロック・アンロック機能を使うことによって、図13での問題を解除することができます。
図14はロック・アンロックを使用して図13の処理の流れを修正したものです。図で、担当者Aは在庫数量の500個を読み込む前に、データベースにロックを掛けます。そのあと担当者Bは受注合計額を読み込もうと思っても、ロックが掛けられているため読み込むことはできません。この間に担当者Aは読み込んだ在庫数に20個を加算し、合計在庫数の520個でデータベースを更新します。更新処理が完了すると担当者Aはアンロックを実行してロックを解除します。ロックの解除によってはじめて担当者Bは在庫数量を読み込めるようになりますが、担当者Aによって520個に更新された後になり520個が読み込まれます。担当者Bも同様にロックを掛けて在庫数計算を行い、入庫数50個を加算した570個でデータベースを更新してロックを解除します。
このようにロック・アンロック機能によってデータベースの更新処理でデータの不整合を防ぐことができますが、ロックを掛けることによって、他のユーザはデータベースにアクセスできないことになり、その分処理が遅れてしまうことにもなります。従ってロック処理は、他のユーザに影響を与えない最少の範囲にすることが必要です。この問題への対応はデータベース製品によっても異なりますが、従来多かったテーブル単位のロックに対して、最近はテーブルの行レコード単位での、つまり狭い範囲でのロックができるものが多くなっています。また、ここでは読み込み/書き込み両方を阻止するロックの例でしたが、このようなロックの掛け方は「排他ロック（Exclusive Lock）」と言われます。しかし、データベースのロックでは書き込みのみを阻止するだけで問題がない場合もあり、このような場合は、書き込みのみを阻止する「共有ロック（Shared Lock）」が使用されます。
ロックに関する問題としてDead Lock（デッドロック）があります。これはデータベースにアクセスするAとBの2人のユーザが、AはBの処理が終わるのを待ち、BもまたAの処理が終わるのを待つという、いわゆるすくみの状態になってしまうことです。しかしこの問題に対しては、現在のデータベースではデッドロックを自動的に解除してくれるようになっており、深刻な問題になることはまずありません。しかし、一応このような問題もあるということは認識しておくべきでしょう。
在庫テーブルの作成
ここからクラウドサーバ側のプログラミングに入っていきますが、最初に行うのは在庫テーブルの作成で、作成は連載第1回で紹介したGoogle APIs Consoleから行います。
CREATE TABLE `inventory` ( `part_no` varchar(12) NOT NULL , `location` varchar(12) NOT NULL , `quantity` int default 0, `emp_no` varchar(8) NOT NULL, `remark` varchar(50) default NULL, `moddate` varchar(20) default NULL, PRIMARY KEY (`part_no`), KEY (`location`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MySQLのテーブルにはMyISAM形式とInnoDB形式の2種類ありますがトランザクション処理はMyISAM形式のテーブルでは使用できません。リスト4ではトランザクション処理のできるInnoDBを指定しています。
この他に、GAEをアクセスするためにはappengine-web.xmlとweb.xmlの指定が必要ですが、これがGAEを使用するための基本的な設定なのでここでは省略します。なお、App Engineプラグインを1.6.5またはそれ以上にバージョンアップしている場合は appengine-web.xmlに<threadsafe>true</threadsafe>のタグを追加してください。
サーブレットの記述
package com.business;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
@SuppressWarnings("serial")
public class stockSvlt extends HttpServlet {
@Override
public void doPost (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException{
req.setCharacterEncoding("utf-8");
res.setContentType("text/html; charset=utf-8");
PrintWriter out;
out = res.getWriter();
String mode = req.getParameter("mode"); //（1）
String stock_op = req.getParameter("stock_op");
String part_no = req.getParameter("part_no");
String location = req.getParameter("location");
Integer op_quant = Integer.parseInt(req.getParameter("op_quant"));
String emp_no = req.getParameter("emp_no");
String remark = req.getParameter("remark"); //（2）
String resp = "";
stockBean stock = new stockBean(); //（3）
if(mode.equals("stockop")){ //（4）
resp = stock.StockOp(stock_op, part_no, location, op_quant, emp_no, remark);
//（5）
}
out.println(resp); //（6）
out.flush();
out.close();
}
}
リスト3の（8）からPOSTメソッドからサーブレットに送信された在庫数量更新リクエストは、リスト5のdoPostメソッドで受信します。doPostでは（1）～（2）でクライアントから送信されてきたパラメータを変数にセットした後、（3）でデータベースの更新処理を行うビーンズ（stockBean）をインスタンス化（stock）し,（5）でインスタンスメソッド（StockOp）の呼び出しを行っています。ここで（4）にif文評価は呼び出すメソッドが1種類だけなのでここでは必要ありませんが、メソッドの追加を予定してこのようにしています。後は、（6）と次の行のflushでクライアントへのレスポンス（更新実行ステータス）を確実に行い出力をクローズします。
ビーンズの記述
クラウドサーバ側での処理はビーンスで行われています。ここでのポイントはCloud SQLのトランザクション処理で実行されるデータベースの更新処理です。
package com.business;
import java.text.*;
import java.util.Date;
import java.sql.*;
import com.google.appengine.api.rdbms.AppEngineDriver;
public class stockBean {
String user = "user1";
String pass = "pass1";
Connection con = null;
int peCnt = 0;
public stockBean(){
try{
DriverManager.registerDriver(new AppEngineDriver());
con = DriverManager.getConnection("jdbc:google:rdbms://branecosmology:business/business", user, pass); //（1）コネクションオブジェクトを取得
} catch (Exception e) {
return;
}
}
///////////////////////////////////////////
// 入出庫処理
public String StockOp(String stock_op,
String part_no,
String location,
Integer op_quant,
String emp_no,
String remark){
String rv = "";
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
TimeZone tz = TimeZone.getTimeZone("Asia/Tokyo"); //（2）
SimpleDateFormat sdf = new SimpleDateFormat("yyyy:MM:dd:HH:mm:ss"); //（3）
sdf.setTimeZone(tz); //（4）
String moddate = sdf.format(new Date()); //（5）
try{
con.setAutoCommit(false); //（6）
String sql1 = "select quantity from inventory where location = ? and part_no = ?";
//（7）
ps1 = con.prepareStatement(sql1); //（8）
ps1.setString(1, location);
ps1.setString(2, part_no);
ResultSet rs1 = ps1.executeQuery(); //（9）
if(rs1.next()) {
int quantity = rs1.getInt("quantity");
if(stock_op.equals("stock_in")){ //（10）
quantity += op_quant;
}else{
quantity -= op_quant;
}
String sql2 = "update inventory set quantity = ?, emp_no = ? , remark = ?, moddate = ? where location = ? and part_no = ?"; //（11）
ps2 = con.prepareStatement(sql2); //（12）
ps2.setInt(1, quantity);
ps2.setString(2, emp_no);
ps2.setString(3, remark);
ps2.setString(4, moddate);
ps2.setString(5, location);
ps2.setString(6, part_no);
if(ps2.executeUpdate() == 1){ //（13）
con.commit(); //（14）
rv = "{\"stat\": \"" + "在庫更新成功: " + part_no + "\"}";
}else{
con.rollback(); //（15）
rv = "{\"stat\": \"" + "在庫更新エラー: " + part_no + "\"}";
}
} else {
rv = "{\"stat\": \"在庫参照不成功\"}";
}
con.setAutoCommit(true); //（16）
ps1.close();
ps2.close();
con.close();
}catch(SQLException e1) {
rv = "{\"stat\": \"" + "SQLエラー: " + e1 + "\"}";
}catch(Exception e2) {
rv = "{\"stat\": \"" + "エラー: " + e2 + "\"}";
}
return rv;
}
}
リスト7でコンストラクタの処理は前回サンプルと同じなのでここでは省略し、ここでの処理内容のStockOpメソッドについて見ていきます。
タイムゾーン指定
在庫情報更新では更新日時を記録しますが、日本での時刻を記録するための処理は必要になり、（2）～（5）で行っています。最初に行うのは（2）の書式で日本のタイムゾーンオブジェクトを取得することで、（3）で日時の表示フォーマットを指定（sdf）した後に（4）で取得タイムゾーンの適用を指定し、（5）で指定したフォーマットとタイムゾーンで日付を取得しています。
トランザクション処理で在庫数量更新
次にトランザクション処理について見ていきます。Cloud SQLではデータベースにMySQLを使用していますが、トランザクションについて何も指定していない場合、暗黙的トランザクションモードでSQLが実行されます。この場合リスト7の（1）でコネクションオブジェクトが取得された段階でトランザクションが開始され、図15左の流れのように、1つのSQLが実行されるたびにコミットが行われます。しかしこの場合は図13のようなデータの整合性が保てないデータベースアクセスを可能にしてしまいます。この場合図14のようにデータの整合性を保った更新処理を行うためには図16右のように手動コミットモードにして、2種類のSQL実行後にコミットを行う必要があり、このように変更したサンプルがリスト6です。
リスト6ではコンストラクタでコネクションオブジェクトが取得された後、StockOpメソッド内で（6）の処理を実行すると自動コミットモードオフになり手動コミットモード（明示的トランザクション）に切り替えられます。手動コミットモードではトランザクションのコミット、ロールバックの制御をプログラム内の任意のタイミングでコード記述により行います。サンプルでは、この後2種類のSQL文が実行されますが、（6）で手動コミットモードを指定しているためそれぞれのSQLが実行されるたびに自動的にコミットが行われることはなく、2種類のSQL実行の成功が確認された後（14）でコミットを行っています。
SQLの処理内容
サンプルでは動的SQLでSQLが実行されていますが、（7）のSQL文から（8）のprepareStatementでプリコンパイルされたSQL文を表すオブジェクトのps1を作成し、参照処理SQLの場合は（9）のようにexecuteQueryメソッドでSQLを実行します。このSQLでは、倉庫ロケーションと部品番号をキーにして在庫数量（quantity）を検索していますが、検索が成功の場合は（10）からのif～else評価で入庫の場合は在庫数量をプラスし、出庫の場合はマイナスにする計算書を行い、その結果を在庫数量に反映するために、（11）のSQL文で更新処理を行っています。ここで、自動コミットモードの場合はこの処理を行っている間に、他の更新処理で在庫数量は変更されてしまっていることもありえますが、この場合は2つのSQL実行の間自動コミットが行われることがないため、他の在庫更新処理によって在庫数量が変更されることはありません。更新処理のSQLは（12）でSQLオブジェクトps2が生成された後（13）の.executeUpdateで実行されます。実行が完了するとif～else評価が行われて、更新が成功した場合のみ（14）のコミットが実行されて処理内容がデータベース内で永続化され、不成功の場合は（15）でロールバック処理が行われて、トランザクション内で行われた2つのSQL文はすべて実行されなかったことになります。
その後は後始末の処理ですが、通常SQL文は自動コミットモードで行われるため（16）で自動コミットモードに戻し、SQLオブジェクトのps1、ps2をクローズし、最後にデータベースコネクションをクローズして終了します。
さいごに
今回は、スマートフォンを使用した在庫管理システムについて取り上げてみました。このサンプルでのサーバ環境ではGAEのCloud SQLを使用していますが、読まれた方はお気づきと思いますが、今回も含めて、連載でこれまで見てきたサンプルは別にクラウドでなくとも、オンプレミスのシステムでも有効な内容になっています。
次回は、生産管理での部品構成表示について取り上げて見たいと思います。また生産管理システムは筆者が長年IT企業の立場から従事してきた内容ですので、その仕組みについても少し詳しく解説してみる予定です。
