備忘録: SQL Server のトリガ と PHP
はじめに
データベースにこんなテーブルがある。(各テーブルの id は共通)
[tblA]
+-----+-------+-------+ | id| name| regid| +-----+-------+-------+ |00001| Tanaka| AaBbCc| |00002| Suzuki| DdEeFf|
[tblB]
+-----+---------------+ | id| message| +-----+---------------+ |00001| Hello, Tanaka!| |00002| Hello, Suzuki!| |00001| Test message|
で、
- tblB に データが INSERT される
- INSERT されたデータの id を基に tblA から regid を取得
- 取得した regid を GCM サーバに送信(プッシュ通知)
という実装がしたかった。
ダメだったやつ
浅はかな考えのぼく「なるほど、SQL Server のトリガでは挿入された行を参照できる inserted なるものがあるのか。これを使えばいけそう」
- tblB に データが INSERT される
- tblB の INSERT 操作を検知してトリガが起動
- (トリガ) inserted から INSERT されたデータを tblC にコピー
- (トリガ) xp_cmdshell で PHP を起動、tblC の id を基に tblA から regid を取得してそのまま GCM サーバに送信
ぼく「完璧じゃん」
[tblB トリガ (on insert)]
CREATE TRIGGER [dbo].[trB] ON [dbo].[tblB] AFTER INSERT AS DECLARE @Id Int; SET @Id = CONVERT(char, (SELECT id FROM inserted)); INSERT INTO tblC id VALUES @Id; BEGIN SET NOCOUNT ON; xp_cmdshell 'C:\hoge\...\tr.php', NO_OUTPUT; END
[PHP]
<?php define('GOOGLE_API_URL','https://android.googleapis.com/gcm/send'); //DeveloperConsoleから取得したAPIキー define('GOOGLE_API_KEY','AIza***********************************'); require_once('config.php'); // DB の設定とか require_once('functions.php'); // connectDb(); $regid = array(); try{ // サーバに接続 $dbh = connectDb(); // regid を取得 $sth = $dbh->prepare('SELECT regid FROM tblA WHERE id IN (SELECT id FROM tblC)'); $sth->execute(); while($row = $sth->fetch(PDO::FETCH_ASSOC)){ $regid[] = $row['regid']; } // 後略
動きませんでした
tblB に insert する処理(VB で作った Web アプリケーションで動いてる)の時点でタイムアウトする。
浅はかな考えに気づき始めたぼく「なんで」
SQL Server の利用状況モニタを確認すると、トリガが起動したコマンドシェルの Execute プロセスと、PHP 内の Select プロセスが2つ(サブクエリ合わせて)、うんともすんとも言わなくなってた。
動かなかった原因(多分)
トリガ内での INSERT 処理は tblC の当該(の追加される)行に対して排他ロックがかかる。このロックはおそらくトリガが終了するまで継続する(クエリの処理完了時点でロックが外れると思ってた)ので、その行を PHP 内の SELECT で参照しようとしてブロックを食らったんじゃないかと推測。
よくよく考えてみると、そもそも「tblC への INSERT」と「PHP での SELECT」が確実にこの順に処理されるわけでもないし、トリガの xp_cmdshell もセキュリティの観点からよろしくない(極論を言えばトリガを使ってなんでも起動できてしまう)。ちょっとまずい実装をしたなと反省。
修正しました
とりあえず現状は一通り動くことを確認したかったので以下のように修正。
- トリガから BEGIN - END の部分を全削除
- PHP はタスクスケジューラを使って定期的に起動することにする
これで一応は問題なく動くようになったけど、トリガとタスクスケジューラが同時に起動した場合にブロッキングが発生する可能性はまだ残ってるので、その場合のエラー処理を ASAP で実装する予定。
