2012-06-16

『売上猫くん on MySQL』開発日記 - 番外25 - 行ロックについて

小社主催の「FileMaker+MySQL開発の講習」の受講者の方から「FileMakerとMySQLのシステムで、FileMaker(単体システム)のような行ロックってできないの?」との質問を講習前に頂いた。
FileMaker単体のシステムでは、ユーザがあるレコードを編集中に他のユーザが同レコードを編集しようとしても「他のユーザが編集中」旨が表示され、他のユーザは先行するユーザが編集を終了するまで編集することができなくなる。質問の趣旨はこれをFileMaker/MySQLのシステムでも実現したいということ。 
FileMaker標準機能では、FileMaker/MySQL 環境下で排他的行ロックはできない。 が、方法としては2つある。一つはMySQLのロック機能(select for update)を使用する方法(MySQLの排他的行ロック)、もう一つはMySQLテーブルにロックフラグを用意し、最初に編集を開始したユーザがそのフラグに自分のIDを書き込み、後続の他ユーザの編集を禁じるようにごりごりスクリプトで制御方法(FileMaker単体の行ロックと似ているという意味で、FileMakerライクなロック)。 以下はこの2つのロックの実装方法について。

1.MySQLの排他的行ロック
まず、MySQLの排他的行ロック機能を使うと、FileMakerはどのように反応するのか、試してみる。

MySQLコマンドラインで、

  begin;
  select 売上No from 売上ヘッダ where 売上No=1 for update;


を実行する。 次にFileMakerの売上画面で売上No=1のレコードを開いてみると、何事もなく表示される。次にこのレコードの任意のフィールド値を変更してみると普通に変更できる。 さらに、レコードをCommitすると… 出た、必殺のコーヒーマーク! これがMySQLの排他的行ロックである。 ここで、MySQLコマンドラインで、


 commit;


を実行すると、コヒーマークは消えて、レコードがCommitされた。 MySQLの排他的行ロック(select for update)は他ユーザがレコードをselectすることを妨げず、よってFileMakerのレイアウト上にも表示される。 また一旦表示されたレコードは、他のユーザが編集していたとしても、後発ユーザの編集を妨げるよなFileMaker的ロックは行われない。 しかし、いざCommitを実行しようとすると、コーヒーマックが出て、select for update を実行したユーザのトランザクションがCommitかRollbackされるまで、コヒーマークが出たまま、つまりロックされた状態となる。
【FileMakerとMySQLコマンドライン】
select ~ for update 後にFMで編集→確定ボタンすると、コーヒーカップが… 
注:
通常、ロックが発生から60秒後に、「[MySQL][ODBC 5.1 Driver][mysqld-5.1.58-community]Lock wait timeout exceeded; try restarting transaction」というエラーが返る。レコード確定ステップ実行後に後処理がある場合は、このエラーをトラップして処理を中止するなど適切な処理を行わないと、システムが暴走する可能性があるので要注意
FileMakerからも「SQLを実行」スクリプトステップに上記内容を直書きしたり、ストアドプロシージャをCALLすれば、ロックやトランザクションを利用できる。試しに


BEGIN
start transaction;
 select * from 売上ヘッダ where 売上No=1 for update;
 select sleep(60);
 rollback;
END;


というストアドプロシージャを作成して、「SQLを実行」ステップからこれをCALLしてみる。 問題なく実行でき、結果、60秒の間そのレコードは更新できなくなる。しかし 「SQLを実行」 ステップはクライアントPCにDSN登録がないと実行できない、という問題があるため、FileMakerからMySQLトリガを発生させてストアドを実行したい(この方法については『売上猫くん on MySQL』開発日記 - 3 - FMからストアド、トリガを参照)。 ということでトリガから上記のストアドを実行しようとすると、「 Not allowed to return a result set from a trigger」というエラーが出るので、レコードセットが返らないように下記のようにストアドを書きなおした。

BEGIN
    declare temp int;
    start transaction;
        select Count(*) into temp from 売上ヘッダ where  売上No =1 for update;
        select sleep (60) into temp;
        update appliasons set ret="OK" where ID=appId;
    commit;
END
ところが残念なことが重なる。今度は「Explicit or implicit commit is not allowed in stored function or trigger.」というエラーが返ってくる。 これでMySQLのトリガではトランザクションが使用できないことがわかった。まえに小生が書いた記事『売上猫くん on MySQL』開発日記 - 番外12 - トリガの制約に、この件をちらっと書いていた(恥)。 
結論としては、ストアドを直接実行すればMySQLのロックを実行できるが、トリガを介したストアド実行によるロックは不可、ということになる。

尚、FM12の新関数ExecuteSQL関数では、MySQLのロックは利用できない(つか、この関数、相当中途半端な上、意味不明なクエリを発する。書いた通りの動きをしないこともあるし…。この辺りは後日機会があれば)。


2.FileMakerライクなロック
次にFileMakerライクなロック。これは開発者が自分でゴリゴリ書かなくてはならないのでかなり面倒だ。 以下は小社のFileMaker/MySQL講習テキストから実装方法を引用する。
【実装】
“編集”ボタンを用意し、レコードを変更する必要があるユーザには“編集”ボタンを実行して「編集モード」にすることを義務付ける。編集モードでなければレコードの変更は実行できないよう制御するとともに、“編集”ボタン実行時には、当該レコードのロックフラグフィールド(cntLockId)にユーザ固有のIDをセット。このフラグが解除されない間は他ユーザが当該レコードの変更を行えないように制御。ユーザがレコードの更新を終えCommitした瞬間にこのロックフラグフィールドを解除するように設計する。
名称
種類
仕様
cntLockId
フィールド
(
テキスト)
編集ボタンを実行するとこのフィールドにユーザ固有のID=Get ( 持続 ID )が入力され、このIDを持たない他ユーザは編集不可となる。 
cntLockTiime
フィールド
(
タイムスタンプ)
編集ボタン実行時に[cntLockId]とともに本フィールドにはサーバのタイムスタンプ(現日時)が入力される。 このタイムスタンプによりデッドロックになっていないかを判断し、管理者は適切な処理をおこなう。
cntLockAC
フィールド
(
テキスト)
必要に応じて作成。編集ボタン実行時にGet ( アカウント名 )が入力される。 これを利用すれば、編集実行者を画面上に表示することができる。
(ユーザが手入力可能なフィールド)
フィールド
OnObjectEnterスクリプトトリガを用いて、[cntLockId<> Get ( 持続 ID )の場合は、フィールドに入れない(フィールド値を変更できない)ように制御。
編集
ボタン
上記のcntLockId/ cntLockTiime/ cntLockAC参照。左記3フィールドに値を入力後にレコードを確定する。これによりそのレコードは他ユーザによる編集が不可となる。
確定
ボタン
上記のcntLockId/ cntLockTiime/ cntLockACの値をクリア(ロックを解除)後、レコードを確定する。
復帰
ボタン
デッドロックしないよう([cntLockId]の値が残らない)ように制御。
他のボタン
ボタン
削除ボタン、削ボタン(売上明細削除用)など、当該レコードを変更する可能性があるボタン、スクリプトは[cntLockId]に他ユーザのIDがある場合は、実行出来ないように制御する。

【編集ボタンによるFileMakerライクなロック】

[cntLockId]と同じGet(持続ID)を持つユーザのみが編集可となり、確定ボタンが完了すると[cntLockId][cntLockTime][cntLockAC]はnullとなり、他のユーザも編集可能となる。


注:
サーバのRemote Desktop Service を介して複数人でFileMakerを使用する場合、Get(持続ID)は重複すると思われる。この場合、他の方法で個別IDを取得すること。

(土屋)

2012-06-08

SQL関連記事と『売上猫くん on MySQL』開発日記の一覧








【FileMaker/MySQL環境での大容量データ取扱(英語のみ/動画による実例あり)】
Big Table Handling Model in FileMaker/MySQL Environment ― Tentative one
FileMaker vs Access 実行速度比較検証


【FMEasy在庫 on MySQL関連】
『FMEasy在庫』 のバックエンドを MySQL に変える (1) ― 概要
『FMEasy在庫』 のバックエンドを MySQL に変える (2) ― 在庫1
 


【その他のFileMaker/MySQL関連】
 ファイル起動時にFileMakerからMySQLに投げられるクエリ



【MySQL Workbench関連】
MySQL Workbench の EER モデリングツールでリレーションシップ検証 1 (Identifying と Non identifying)
MySQL Workbench の EER モデリングツールでリレーションシップ検証 2 (外部キー制約の種類と動作)

MySQL Workbench の EER モデリングツールでリレーションシップ検証 3 (リレーションシップアイコンの種類と使い方)
 



【売上猫くん開発日記 ― 番外編】
番外1 -全データベース削除→リストア
番外2 - MySQLミラーサイト
番外3 - 0を書き込みできない
番外4 - mysqldump で文字化け?
番外5 - ポータルで検索できない
番外6 - リモートのログを mysqlbinlog できない
番外7 - FileMaker内にMySQLの一般ログを表示する
番外8 - insert...select構文とODBC
番外9 - 外部キー制約設定でのたうちまわる
番外10 - 開発再開
番外11 - 外部キー ってさぁ… orz
番外12 - トリガの制約
番外13 - MySQL Workbenchの憂鬱(ほぼ私的メモ)
番外14 - テーブル毎に権限を設定する(ほぼ私的メモ)
番外15 - 再び権限(私的メモ)
番外16 - FMで大きなSQLテーブルは扱えるのか? ― その1
番外17 - FMで大きなSQLテーブルは扱えるのか? ― その2
番外18 - FMで大きなSQLテーブルは扱えるのか? ― その3
番外19 - FMで大きなSQLテーブルは扱えるのか? ― その4
番外20 - ウィンドウ内容の再表示ステップ実行時のクエリ
番外21 - βリリースしまつた
番外22 -SQL SECURITY の DEFINER/INVOKER指定
番外23 - FileMaker vs Access ~ 実行速度編 ~
番外24 - MySQL 5.5は使用できるのか?
番外25 - 行ロックについて


【売上猫くん開発日記 ― 本篇】(←メインになる筈が、見捨てられた哀れな本編)
1 (題目無)
2 - 開発環境
3 - FMからストアド、トリガ



2012-06-05

『売上猫くん on MySQL』開発日記 - 番外24 - MySQL 5.5は使用できるのか?

FileMakerとMySQL 5.0/5.1で開発してきた人へ:

『売上猫くん on MySQL R5.0β』は、FileMaker 11(FM11)/MySQL 5.1で開発した。 そのDBダンプをMySQL5.5上 にリストアして、FM11フロントエンド(NekoApp.fp7)からアクセスしようとすると、以下のような状況となり、アクセスできない。



このNekoApp.fp7をFileMaker 12(FM12)でNekoApp.fmp12に変換し、MySQL5.5にアクセスするとどうか?



と、さらに悲惨なこととなる。


NekoApp.fp7のリレーションシップ画面を開いてみると、下図のように一見すると正しくテーブルは認識されているようだが、TOをダブルクリックしてみるとTOとテーブルのリンクが消失していて、テーブルを指定し直す必要がある。


また、その指定し直しの過程でリレーションシップが飛んでしまったりもする。 TOとリレーションシップをすべて再設定・確認するだけども大変な作業になるし、検証作業も含めると… 膨大な作業になる。


注:
  • 使用しているODBCのバージョンは、5.1.11。 
  • FM社が公式サポートするMySQLは現時点でMy SQL 5.1 Community Editionのみ。
  • MySQL5.1を介さず、最初からFM11/12とMySQL5.5でリレーションシップ(TO)を作成しておけば、とりあえずは動作するようである(要検証)。

2012-06-01

簡単? FileMakerで在庫管理(2) ―― 繰越処理の考え方

 今回は、繰越処理に伴う在庫算出について考えていきましょう。 在庫算出の基本については、以下の記事をご覧ください。

 簡単? FileMakerで在庫管理(1) ―― 在庫算出の基本


繰越処理の考え方


 入出庫のレコードが増えてくると、在庫算出時に合計する入出庫明細レコードが膨大になり、処理速度が低下します。
 そこで、在庫数の繰越処理を行うことによって、在庫算出処理を行うことにより、在庫数算出を高速化する必要があります。
 
 たとえば、商品Aの2011年6月~2012年5月の入出庫明細データが10万件あったとします。
 2012年5月31日時点の商品Aの在庫を算出しようとして、入出庫明細レコードを10万件を対象に、Sum(入庫明細::入庫数)-Sum(出庫明細::出庫数) をやっていては、処理時間が膨大になってしまいますが、繰越処理を実行することによって2012年4月末時点の商品Aの在庫数を商品テーブルに保管しておき、5月1日~5/31の入出庫明細レコードのみをSumの対象となるようにリレーションを組めば、Sumの対象となるデータは、単純試算では10分の1以下となります。

 次に在庫算出の計算式を、以下のように繰越処理を行った場合と、行ってない場合とでは、在庫算出の計算式が違ってきます。

A式: 導入時在庫数+(4/25以前の入庫計)-(4/25以前の出庫計)

B式: 繰越処理日≦在庫基準日の場合の在庫算出式
繰越在庫数+(4/1~4/25の入庫計)-(4/1~4/25の出庫計)


C式: 繰越処理日<在庫基準日の場合の在庫算出式
  導入時在庫数+(2/28以前の入庫計)-(2/28以前の出庫計)

 要はA、B、Cの式をCase分けして、ひとつにまとめる必要があるわけです。



繰越処理と制御


 さて、繰越処理で考えておかなければいけない重要なことが一つあります。それは、繰越処理日(上記例では3/31)以前のデータ、在庫に関していえば、3/31以前の入出庫レコードの出庫日と入庫日、出庫数と入庫数を変更不可にしておく必要があります。また、3/31以前の同レコードは削除させてはならないし、繰越処理日以前の入出庫日を持つ同レコードは作成させてもいけません。 

 その理由は、[繰越在庫数]との整合性がなくなってしまうからです。

やがて訪れるデータ削除の時期について:
 長期間使用するシステムでは、繰越処理を行ってもレコードはどんどん蓄積されていきます。当社が開発・保守を担うシステムでは百数十万件の入出明細データを持つテーブルがありますが、ほぼ定期的にデータ削除を行っています。小社では、FileMaker のテーブルの最大レコード蓄積数は200~300百万件を一つの目安としていますが、データの削除は、システム仕様、実行速度要求、保持データ数要求、バックアップポリシー等の諸事情を勘案し、システムが安定動作することを大前提に実行してください。一般ユーザがデータ削除を行う場合は、一括データ削除機能も必要になります。
 また、削除したデータを照会したい、といった要望も必ずといっていいほどユーザより提示されるので、できれば過去データの照会についても予め考慮してシステム設計を行います。


在庫管理は簡単か?


 残念ながら答えは NO です
 システム的には、繰越処理とそれに伴う伝票制御、旧データ削除時の処理を考慮せざるを得ず、在庫管理システムを作るのはやはり簡単ではない、というのが結論になります。

 また、繰越処理関連を適正に実装できたとしても、1カ月に同一商品の取引が数百回以上あるような業務では在庫算出処理の遅延も予想され、別の在庫算出手法(拙稿の「FileMaker V10による在庫管理、一考」の123を参照)の採用も選択肢に入れなければならないでしょう。



土屋