  |
 |
2008年10月7日     |
ありがちなビジネスルールの実装ミスを防ぐ 海外internet.com発の記事はじめにビジネスルールを熟知したSQL Server開発者がトリガや制約を用いてルールを実装しようとしたが、どういうわけか結局は無効なデータがデータベースに紛れ込み、ビジネスに深刻な結果をもたらす羽目になった――そんな事態を目にしたことはないでしょうか。長年にわたって、私はそのような状況を幾度となく目撃してきました。SQL Serverにはデータの整合性を保証する組み込みツール（参照整合性制約など）があるにもかかわらず、経験の浅い開発者は、それを利用せずに一から自分の力でやり遂げようとします。制約を適切に使用するのではなく、独自のビジネスルール実装ソリューションを開発しようとするのです。 そこで、一見隙のないトリガや制約をすり抜けて無効なデータがデータベースに紛れ込むプロセスを理解することが極めて重要となります。 本稿では、ごく単純なビジネスルールをよく知られた複数の方法で実装し、それらのアプローチに含まれる「抜け穴」を具体的に示します。また、スナップショット分離（snapshot isolation）が、ビジネスルールの実装ミスを誘う新たなきっかけになることも示します。 サンプルテーブルとビジネスルール「報告先は契約社員（Contractor）であってはならない」というビジネスルールを実装する必要があるものと仮定します。このビジネスルールで使用するDDLは次のとおりです。 create table employee(employee_id int not null primary key, first_name Varchar(10), last_name Varchar(10), manager_id int, status Varchar(10) check(status in(’Employee’, ’Contractor’))) 以下はサンプルデータです。 insert into employee(employee_id, first_name, last_name, manager_id, status) values(1, ’Jane’, ’Wilson’, null, ’Employee’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(2, ’Sue’, ’Smith’, 1, ’Contractor’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(3, ’Sam’, ’Brown’, 1, ’Employee’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(4, ’Jill’, ’Larsen’, 3, ’Employee’) トリガが起動しないことがあるトリガは非常に強力で柔軟なため、ビジネスルールの実装を求められた開発者は、多くの場合、ためらわずトリガを利用します。しかし、トリガには次の2つの問題があります。
誰かがトリガを削除してから無効なデータを追加し、その後、トリガを作り直したとすると、知らない間に不正なデータが紛れ込むことになります。
サーバーレベルの「ネストされたトリガ」とデータベースレベルのrecursive_triggersにより、トリガの起動が阻止されることがあります。次の簡単なトリガの例は、この問題の完全なソリューションではありませんが（コード内のコメントを参照）、重要なポイントを示しています。
create trigger manager_must_be_employee on employee after insert, update -- this trigger does not handle deletes, just to keep things simple -- a complete solution should prevent -- from deleting of a manager with employee as declare @contractor_has_employees int, @manager_is_contractor int if update(status) begin select @manager_is_contractor = count(*) from inserted, employee where employee.status = ’Contractor’ and employee.employee_id = inserted.manager_id if @manager_is_contractor > 0 begin raiserror(’Cannot insert anyone reporting to a Contractor’, 16, 1) rollback tran return end select @contractor_has_employees = count(*) from inserted, employee where inserted.status = ’Contractor’ and inserted.employee_id = employee.manager_id if @contractor_has_employees > 0 begin raiserror(’Cannot set manager’’s status to Contractor’, 16, 1) rollback tran return end end この簡単なトリガは、あらゆる事態に対応するものではありませんが、これで一部のエラーは回避されます。 update employee set status = ’Contractor’ where employee_id = 3 Server: Msg 50000, Level 16, State 1, Jill Larsenの報告先はSam Brownなので、Samを契約社員にすることはできません。このトリガが起動すれば、適切なエラーが引き起こされ、ロールバックが行われます。しかし、それも「トリガが起動すれば」の話です。ここで、サーバー側で「ネストされたトリガ」がオンに設定されていて、社員の状態が別のトリガによって変更される可能性がある場合を考えてみましょう。この場合も、manager_must_be_employeeトリガはデータを保護します。 create table contract(employee_id int, amount float) go create trigger only_contractors_have_contracts on contract after insert as update employee set status=’Contractor’ from employee, inserted where inserted.employee_id = employee.employee_id go insert into contract values(3, 199.00) go Server: Msg 50000, Level 16, State 1, このアプリケーションは完全にテストされた上で配備が完了しており、「employee」テーブルには無効なデータが存在しないものとします。しかし、もしデータベース管理者が「ネストされたトリガ」をオフにした場合は、同じ挿入ステートメント 次に、「employee」テーブルに無効なデータが存在し、そのことに気づいていないものと仮定します。Jill Larsenの報告先はSam Brownなので、Samが契約社員であることはあり得ません。しかし、Samの状態は次のようになっています。 select * from employee employee_id first_name last_name manager_id status ----------- ---------- ---------- ----------- ---------- 1 Jane Wilson NULL Employee 2 Sue Smith 1 Contractor 3 Sam Brown 1 Contractor 4 Jill Larsen 3 Employee (4 row(s) affected) これまで見てきたように、トリガは起動しないことがあります。「ネストされたトリガ」とrecursive_triggersの設定をテスト環境と本番環境で一致させるようにしてください。 サブクエリでチェック制約が働かないことがあるトリガと違って、チェック制約は変更が行われたときに必ず起動されます。SQL Serverでは、管理者が正社員かどうか検証するサブクエリをチェック制約に実行させることができません。
alter table employee add constraint manager_is_employee check(
manager_id is null or
(select count(*) from employee e where e.employee_id = manager_id
and e.status = ’Employee’) = 1
)
Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are
allowed.
しかし、よく知られた簡単な回避策があります。このサブクエリをユーザー定義関数でラップするという方法です。 create function check_manager_status(@manager_id int) returns tinyint as begin declare @ret tinyint set @ret = (select count(*) from employee e where e.employee_id = @manager_id and e.status = ’Employee’) return @ret end go このユーザー定義関数をチェック制約内で起動します。 alter table employee add constraint manager_is_employee check( manager_id is null or dbo.check_manager_status(manager_id) = 1 ) このチェック制約は問題なく生成されており、正常に動作しているように見えます。次の4行は正常に挿入されます。 delete from employee insert into employee(employee_id, first_name, last_name, manager_id, status) values(1, ’Jane’, ’Wilson’, null, ’Employee’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(2, ’Sue’, ’Smith’, 1, ’Contractor’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(3, ’Sam’, ’Brown’, 1, ’Employee’) insert into employee(employee_id, first_name, last_name, manager_id, status) values(4, ’Jill’, ’Larsen’, 3, ’Employee’) しかし、契約社員を報告先とする人物を挿入しようとすると失敗します。 insert into employee(employee_id, first_name, last_name, manager_id, status) values(5, ’Jack’, ’Hansen’, 2, ’Contractor’) Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "manager_is_employee". The conflict occurred in database "test1", ただし、このありふれたソリューションには抜け穴があります。Sam Brownが正社員ではなくなり、契約社員になったと仮定します。 update employee set status = ’Contractor’ where employee_id = 3 この更新は正常に実行され、契約社員を報告先とする人物（Jill Larsen）がデータベース内に生じます。つまり、知らぬ間にビジネスルールは破られてしまいます。ここで、ひとまずデータの完全性を回復しましょう。 update employee set status = ’Employee’ where employee_id = 3 この問題を何とか解決するために、別のチェック制約を作成します。これは最初の制約によく似ています。 create function number_of_employees(@manager_id int) returns int as begin declare @ret int set @ret = (select count(*) from employee e where e.manager_id = @manager_id and e.status = ’Employee’) return @ret end go alter table employee add constraint contractor_has_no_employees check( status = ’Employee’ or dbo.number_of_employees(employee_id) = 0 ) ここで、Sam Brownの状態を再び契約社員に変更してみます。見たところ、新しい制約によってデータの完全性は守られているようです。 update employee set status = ’Contractor’ where employee_id = 3 Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the CHECK constraint "contractor_has_no_employees". The conflict occurred in SQL Server 2005が事態を複雑にするこれですべてに対応できたかと言えば、必ずしもそうでありません。SQL Server 2005がまだ問題です。この理由を説明するために、スナップショット分離を利用して、無効なデータをテーブルに入力することにします。Management Studioの1つのタブで、次のスナップショット分離レベルを用いてトランザクションを開始します。 SET TRANSACTION ISOLATION LEVEL SNAPSHOT begin transaction insert into employee(employee_id, first_name, last_name, manager_id, status) values(5, ’Jack’, ’Hansen’, 4, ’Contractor’) このトランザクションをまだコミットしていないことに注意してください。 Management Studioの別のタブ（つまり、別の接続）で、同じスナップショット分離レベルを使用することにします。このスナップショット分離レベルのおかげで、最初の接続の未コミット変更は見えません。そのため、それらの未コミット変更が現在の接続からの読み込みをブロックすることはありません。 SET TRANSACTION ISOLATION LEVEL SNAPSHOT select * from employee where manager_id=4 この選択クエリは、最初の接続の未コミット挿入でブロックされないため、すぐに復帰します。また、このクエリからは最初の接続の未コミット変更は見えないため、このクエリは何も返しません。従って、次の挿入ステートメントは成功します。 update employee set status = ’Contractor’ where employee_id = 4 ここで両方のトランザクションをコミットすると、無効なデータがデータベースに取り込まれます。 select * from employee employee_id first_name last_name manager_id status ----------- ---------- ---------- ----------- ---------- 1 Jane Wilson NULL Employee 2 Sue Smith 1 Contractor 3 Sam Brown 1 Employee 4 Jill Setton 3 Contractor 5 Jack Hansen 4 Contractor (5 row(s) affected) つまり、チェック制約内のサブクエリは、スナップショット分離を使用したときには機能しません。この点は、セキュリティ面で誤った印象をもたらしています。同じ理由から、トリガ内またはストアドプロシージャ内でこれらのサブクエリを使用してもスナップショット分離使用時には機能しません。 スナップショット分離に落とし穴があることがわかったので、正常に動作しない制約は削除しておきましょう。 alter table employee drop constraint manager_is_employee alter table employee drop constraint contractor_has_no_employees そして、最後に後片付けをします（データの完全性を回復します）。 update employee set status = ’Employee’ where employee_id = 4 delete from employee where employee_id=5 参照整合性を使うのが正しいやり方 このビジネスルールを実装する正しい方法を紹介しましょう。ここで使う参照整合性のために、さらに列（ alter table employee add manager_status Varchar(10) check(manager_status in(’Employee’)) update employee set manager_status = ’Employee’ where manager_id is not null また、この新しい列に唯一の非Null値（Employee）か、Nullを格納できるようにします。 alter table employee add constraint manager_status_populated check( manager_id is null or manager_status is not null) 次に、参照整合性制約で alter table employee add constraint FKTarget unique(employee_id, status) alter table employee add constraint manager_is_employee foreign key(manager_id, manager_status) references employee(employee_id, Status) これは先に述べたすべてのシナリオで正常に動作します。ただし、managerの状態をContractor（契約社員）に更新すると、多少分かりにくいエラーメッセージが返されます。 Msg 3960, Level 16, State 2, Line 1 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ’dbo.employee’ directly or indirectly in database ’test1’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement. この参照整合性制約は、どのような状況でも正常に動作し、データを常に保護します。それにしても、完璧な整合性を得るためのコストは相当なものです。実際、1つの列と、インデックスを追加する必要があるわけです。 知ることが賢い判断につながるビジネスが違えば、それに応じてニーズもさまざまに変化します。場合によっては、抜け穴があるとわかっているソリューションでも、それを使用しなければならないこともあるでしょう。その場合は、ここで紹介したような、ごくありふれた問題を承知しておくことが何よりも大切です。 著者紹介Alexander Kuznetsov（Alexander Kuznetsov）
データベースの設計、開発、トラブルシューティング、管理に10年以上携わる。1994年以来Sybaseの仕事を担当し、1998年以降はMS SQL Serverにも携わっている。2001年以降は主要なすべてのRBDMS（DB2、Oracle、SQL Server）にかかわっている。MCP 70-229を取得。現在、DRW Tradingと仕事をしており、データベースのパフォーマンス改善に専念。
|
Alexander Kuznetsov
Procedure manager_must_be_employee, Line 32
Cannot set manager’s status to Contractor