読者です 読者をやめる 読者になる 読者になる

ハクチョウノミズウミ

日記やプログラミングの備忘録などを書きます。雑多なごった煮。

備忘録: 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|

で、

  1. tblB に データが INSERT される
  2. INSERT されたデータの id を基に tblA から regid を取得
  3. 取得した regid を GCM サーバに送信(プッシュ通知)

という実装がしたかった。


ダメだったやつ
浅はかな考えのぼく「なるほど、SQL Server のトリガでは挿入された行を参照できる inserted なるものがあるのか。これを使えばいけそう」

  1. tblB に データが INSERT される
  2. tblB の INSERT 操作を検知してトリガが起動
  3. (トリガ) inserted から INSERT されたデータを tblC にコピー
  4. (トリガ) 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 もセキュリティの観点からよろしくない(極論を言えばトリガを使ってなんでも起動できてしまう)。ちょっとまずい実装をしたなと反省。

修正しました
とりあえず現状は一通り動くことを確認したかったので以下のように修正。

  1. トリガから BEGIN - END の部分を全削除
  2. PHP はタスクスケジューラを使って定期的に起動することにする

これで一応は問題なく動くようになったけど、トリガとタスクスケジューラが同時に起動した場合にブロッキングが発生する可能性はまだ残ってるので、その場合のエラー処理を ASAP で実装する予定。

PHP とか SQL はまだ初めて数週間のド素人なので、間違ってるところがあったらご教示くださいませ。