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)を作成しておけば、とりあえずは動作するようである(要検証)。