第 3 章 SQL Server 2000データベース管理とアプリケーション開発～ MCA をめざそう!! MCA データベース Microsoft SQL Server 2000 対応 編 ～
熊倉 克己
2002 年 5 月 30 日
7. トランザクション管理
この章では、トランザクションの役割と機能について説明します。
7.1. トランザクションの特性
トランザクションは、論理的な １ つの作業単位として実行される一連の操作です。トランザクションが有効であるためには、ACID と呼ばれる ４ つの属性を備えている必要があります。
属性 |
説明 |
|---|---|
Atomiccity（原子性） |
トランザクションは、分離できない １ つの単位であり、そのデータはすべて実行されるか、どれも実行されないかのどちらかです。 |
Consistency（一貫性） |
トランザクションが完了時にすべてのデータが一貫した状態になければなりません。 |
Isolation（分離性） |
同時実行のトランザクションによってなされる変更は、ほかのすべての同時実行のトランザクションの変更とお互い独立している必要があります。 |
Durability（持続性） |
トランザクションの完了後、その結果はシステム内で持続します。システム障害が発生しても、変更結果は有効です。 |
トランザクションの定義
アプリケーションはトランザクションの開始タイミングと終了タイミングを指定してトランザクションを制御します。BEGIN TRANSACTION と COMMIT TRANSACTION を使用します。
BEGIN TRANSACTION
INSERT UPDATE COMMIT TRANSACTION
7.2. 同時実行制御
同時実行制御は １ 人のユーザが行った変更がほかのユーザによる変更に悪影響を及ぼさないことを保証するものです。同時実行制御には、以下の 2 種類の制御があります。
種類 |
説明 |
|---|---|
ペシミスティック |
更新の準備としてデータが読み取られたときにデータがロックされます。ロックを適用したユーザがデータ操作を完了するまで、ほかのユーザはデータを変更する操作を実行できません。 |
オプティミスティック |
データが最初に読み取られた時点では、データはロックされません。更新が実行されたときに、最初に読み取られたデータが変更されたかどうかがチェックされます。データが変更されている場合は、ユーザにエラーが返されトランザクションはロールバックされます。 |
同時実行制御を行うことにより、以下の問題点が解決されます。
問題点 |
説明 |
|---|---|
更新の損失 |
トランザクションによる変更がほかのトランザクションによって上書きされると、更新が失われることがあります。 |
ダーティリード |
トランザクションがほかのトランザクションから、コミットされていないデータを読み取ったときに発生します。不正確なデータを基にして変更を行う可能性があります。 |
反復不能読み取り |
トランザクションによって同じ行が複数回読み取られ、そのたびにほかのトランザクションによってその行が変更されたときに発生します。 |
ファントム読み取り |
トランザクションがお互いに分離されていない場合に発生します。例えば、ある範囲の行をUPDATEしている際にほかのトランザクションがその範囲に新しい行をINSERTすることができます。次にトランザクションからデータを読み取ると追加の行が存在してしまいます。 |
SQL Server では、トランザクション分離レベルを指定することで、セッション レベルでロック オプションを制御できます。
オプション |
説明 |
|---|---|
READ UNCOMMITTED |
共有ロックを発行しないようにします。ダーティリードが発生する可能性があります。 |
READ COMMITTED |
共有ロックを発行するようにします。ダーティリードは発生しません。 |
REPEATABLE READ |
ダーティリードと反復不能読み取りが発生しません。読み取りロックは、トランザクションの終了まで保持されます。 |
SERIALIZABLE |
トランザクションの WHERE 句に含まれる条件に一致する行を更新したり、新規に挿入することをほかのユーザに対し禁止します。ファントム読み取りは発生しません。 |
- ロック制御
SQL Server には、主に 2 種類のロックがあります。
種類 |
説明 |
|---|---|
共有ロック |
データの変更も更新も行わない操作は、共有（読み取り）ロックを使用します。 |
排他ロック |
データを変更する （INSERT,UPDATE,DELETE） ステートメントに対しては排他ロックを使用します。 |
SQL Serverによってロックされるリソースには主に以下のリソースがあります。
リソース |
説明 |
|---|---|
RID |
行識別子。1 行をロックするために使用します。 |
キー |
インデックス内の行ロック、キーの範囲を保護するために使用します。 |
テーブル |
すべてのデータとインデックスを含むテーブル全体 |
データーベース |
データベースの復元時に使用するデータベース全体 |
最大のパフォーマンスを得るために、ロックのコストを最小にするため、自動的に適切なレベルでリソースをロックします。
SQL Server は、動的ロックアーキテクチャーを使用してクライアントに最適なロックを選択しますが、テーブルレベルのロックオプションを指定することもできます。テーブルロックの例を示します。
USE pubs BEGIN TRAN SELECT COUNT(*) FROM authors WITH (TABLOCK, HOLDLOCK)
デッドロック
デッドロックは、２ つのトランザクションが別々のオブジェクトについてのロックを持ち、両方のトランザクションが相手のロックしているオブジェクトへのロックを要求したときに発生します。どちらのトランザクションも、相手がロックを開放するのを待ちます。SQL Server は、トランザクションのいずれかを自動的に終了することでデッドロックを終了します。
7.3. 障害回復処理
データ変更が発生すると、トランザクションログにその変更が記憶されます。チェックポイントのタイミングで、コミット済みのトランザクションがディスク上のデータベースファイルに書き込まれます。
トランザクションログにはすべての変更が記録されるため、電源障害、システム ソフトウエア障害、クライアント障害、トランザクション取り消し要求 （ROLLBACK TRANSACTION） が発生した場合に、SQL Server は自動的にデータを回復できます。 SQL Server の復旧プロセスは、トランザクションログを調べて、データベースの一貫性を保証します。
.jpg)
図4. リカバリープロセス
前回のチェックポイントから、障害が発生した時点もしくはシャットダウンした時点までのトランザクションログを調べます。
コミット済みトランザクションはロールフォワードされ、データベースに書き込まれます。
コミットされていないトランザクションはロールバックされ、データベースには書き込まれません。
SQL Server の障害には、システム障害とデータベース障害があります。
種類 |
復旧処理 |
|---|---|
システム障害 |
システム障害時にシステムを再起動すると、SQL Server はデータの一貫性を保証するために、自動復旧プロセスを開始します。このプロセスは自動的に実行されるため、手動で開始する必要はありません。 |
データーベース障害 |
データベース障害後にバックアップから復元操作を行う際に、復旧プロセスを手動で開始できます。 |
- データベース復旧モデル
SQL Serverには、3 種類のデータベース復旧モデルがあります。どのモデルも障害が発生した場合のデータ損失を保護します。
復旧モデル |
説明 |
|---|---|
完全復旧モデル |
すべての変更はトランザクションログに記憶されます。 現在のトランザクションログが障害を起こしていない限りデータベースを完全に復旧できます。 |
一括ログ復旧モデル |
一部の操作に対しては、ログ領域の使用をより少なくします。 現在のトランザクションログが障害を起こしていない限りデータベースを完全に復旧できます。 |
単純復旧モデル |
チェックポイント時にログが切り捨てられます。 バックアップした時点にしか復旧できません。 |
- データベースのバックアップ
SQL Server は、いくつかの異なるバックアップ方法を提供します。
バックアップの種類 |
説明 |
|---|---|
フルデータベースバックアップ |
オリジナルデータベースファイルをバックアップします。 |
差分バックアップ |
前回のフルデータベースバックアップ以降のデータベース変更をバックアップします。 |
トランザクションログバックアップ |
トランザクションログをバックアップします。 トランザクションログを切り捨てます。 |
データベースの復元
例えば、Northwind データベースは以下のバックアップを定期的に行っていたとします。
週 1 回フルデータベースバックアップを行っています。
毎日差分バックアップを行っています。
8 時間ごとにトランザクションログバックアップを行っています。
データﾍベース障害が発生した場合、データベースの復元を行う必要が発生します。
.jpg)
図 5. バックアップと復元
データベース復元は、以下の手順で復元を行います
フルデータベースバックアップからの復元
RESTORE DATABASE Northwind FROM F1
WITH NORECOVERY
差分バックアップからの復元
RESTORE DATABASE Northwind FROM S1
WITH NORECOVERY
最初のトランザクションログからの復元
RESTORE DATABASE Northwind FROM L1
WITH NORECOVERY
２番目のトランザクションログからの復元
RESTORE DATABASE Northwind FROM L2
WITH RECOVERY
8. セキュリティ管理
この章では、データベースのセキュリティ管理について説明します。
8.1. SQL Server のセキュリティ
SQL Server のセキュリティは２つのレベルがあります。ログイン認証と、データベースユーザアカウント権限の妥当性検査です。
ログイン認証では、ログインアカウントを識別し、SQL Server に接続できるかどうかだけを検査します。認証が成功すると、SQL Server に接続できます。ユーザが認証されSQL Serverへの接続が許可された後、データベースにアクセスします。データベースにアクセスするには、データベース毎にデータベースユーザが必要です。データベース ユーザは SQL Server に登録されているログインアカウントに関連付けて登録します。
.jpg)
図 6. ログインアカウントとデータベースユーザ
- ログイン認証
SQL Server に接続するには、ユーザはログイン アカウントを持っていなければなりません。Windows 認証、SQL Server 認証の ２ つの認証メカニズムを使用します。Windows 認証では、既存の Windows ユーザまたはグループを SQL Server ログインアカウントに関連付けて登録します。SQL Server 認証では、ログインアカウントとパスワードを作成します。
- 権限の管理
データベース ユーザがデータベースを操作するには権限が必要です。データベース ユーザが何らかの操作を実行すると、そのデータベースユーザに適用されている権限を確認します。例えば、テーブルをのデータを取得するにはSELECT権限が必要です。
- ロール
ロールとは、ユーザをまとめるためのグループです。ロールに追加されたユーザは、ロールに適用された権限を継承します。ユーザ数が多い場合や、セキュリティの割り当てが複雑な場合、ロールを利用するとセキュリティ管理が容易になります。以下の ３ 種類のロールがあります。
種類 |
説明 |
|---|---|
固定サーバーロール |
サーバレベルでのグループ管理権限。 |
固定データベースロール |
データベースレベルでの管理権限 |
ユーザ定義ロール |
グループごとに実行できる権限 |
9. 分散データベース
分散データベース環境とは、複数サーバーに同じ情報が複数コピーされている環境です。
データをユーザの近くに配置
サイトに自律性を保つ
OLTPとデータウエアハウスの分離
競合の削減
データを配布するには、以下の ２ つの方法があります。
レプリケーション
分散トランザクション
9.1. レプリケーション
転送元データベースから、通常は別のサーバーにある転送先データベースへ最新のデータを複製します。サイト自律性がサポートされ、サイトを断続的にオンラインにすることができます。
SQL Server では、３ 種類のレプリケーションを提供します。同じデータベースで、複数のレプリケーションの種類を併用できます。
マージレプリケーションレプリケーションされたデータを変更でき、その後、すべてのサイトからの変更内容は、マージされます。同一の結果セットに収束することが保証されます。複数のサーバーで更新が行われるため、同じデータが更新される場合があります。このため、競合が発生する可能性があります。
種類 |
説明 |
|---|---|
スナップショット |
データの新しいスナップショットを定期的に一括転送します。。 |
トランザクション |
変更された部分のレプリケーションを行います。データの遅延が最小になります。 |
マージ |
レプリケーションされたデータを変更でき、その後、すべてのサイトからの変更内容は、マージされます。 |
9.2. 分散トランザクション
データのすべてのコピーが、同時に同じ値を持つことを保証します。分散トランザクションに含まれている各サーバーは必ず常時オンラインになっている必要があります。各サーバーでトランザクションを完了できなければなりません。常にデータの同期を取る必要があります。
MS DTC（Microsoft 分散トランザクションコーディネ－タ）は ２ フェーズコミットを使用して、参加しているすべてのサイトでトランザクションが同時に完了することを保証し、分散トランザクションを可能にします。
10. クライアントサーバ型 ＤＢ
データベースを利用するには、モデリングするアプリケーションアーキテクチャーの概念の必要となります。
10.1. クライアントサーバ型 ＤＢ
SQL Server はクライアントサーバー型のデータベースです。複数のアーキテクチャーを使用してアプリケーションを実装できます。アプリケーションは、以下の ３ つの論理層に分割できます。
論理層 |
説明 |
|---|---|
プレゼンテーション層 |
データとアプリケーションをユーザに提示するためのロジックが含まれる。 |
ビジネス層 |
アプリケーションロジックとビジネスルールが含まれる |
データ層 |
データ定義、データ整合性ロジック、ストアドプロシージャ、およびデータと密接に関連付けられた操作。SQL Server はこの層に含まれます。 |
これらの論理層は、複数のサーバーに配置することができます。以下の組み合わせがあります。
.jpg)
図 7. アプリケーション・アーキテクチャー
10.2. 共通データベースアクセス
ユーザはデータベース API やをデータオブジェクトを使用して SQLServer にアクセスします。SQL Server は、以下の API をサポートしています。
.jpg)
図 8. データベース API とデータベースオブジェクト
- データベース API
データベース API は、データベースに接続し、コマンドを渡すインタフェースを提供します。
以下の API があります。
API |
機能 |
|---|---|
ODBC |
ODBC はコールレベルインターフェースです。ODBC は SQLServer やその他のRDBにアクセスできますが、その他のデータソースにアクセスすることはできません。 |
ＯＬＥ ＤＢ |
コンポーネントオジュケトモデル（COM）ベースの API です。OLE DB は、SQLServer やその他の RDB や、その他のデータソースにアクセスできます。 |
- データオブジェクトインターフェース
データオブジェクトインタフェースを利用するとデータベース API を使用するより簡単にデーターベースにアクセスできます。
データオブジェクト |
内容 |
|---|---|
RDO （リモート データ オブジェクト） |
RDO は ODBC API をカプセル化します。RDO は Visual Basic や VBA から使用できます。 |
ADO （ActiveX データオブジェクト） |
ADO は OLE DB API をオブジェクトモデルにカプセル化しています。ADO は、Visual Basic や VBA から使用できます。また、ASP などからも使用できます。 |
10.3. ストアド プロシージャ
ストアド プロシージャは、SQL ステートメントの集まりをカプセル化し、名前をつけてサーバーに格納します。一度作成したストアド プロシジャーはクライアントから何度でも繰り返し実行できます。
ストアド プロシージャはサーバー上で実行され、クライアントには、実行結果のみを返します。
.jpg)
図 9. ストアド プロシージャの作成と実行
- ストアド プロシージャの作成
ストアド プロシージャを作成するときは、SQL ステートメントの構文解析が行われます。 SQL Server は、ストアド プロシージャ名を現在のデータベースのシステムテーブルに格納します。構文エラーが見つかるとストアド プロシージャは作成されません。
- ストアド プロシージャの初回実行
ストアド プロシージャが初めて実行されるとき、クエリ プロセッサでオブジェクト解決処理を行います。ストアド プロシージャで参照しているテーブルが存在しないと実行時エラーとなります。ストアド プロシージャが正常に解決されると、SQL Server クエリオプティマイザが最適化を行い実行プランを作成します。実行プランをコンパイルして、プロシージャ キャッシュに格納します。2 回目以降に呼出された場合は、プロシージャ キャッシュ内の実行プランを再利用するので効率的に実行されます。
10.4. トリガ
トリガは、トリガの作成されたテーブルを変更するたびに実行される特殊なストアド プロシージャです。トリガはユーザテーブルと関連付けられており、テーブルでの挿入、更新、削除が行われると、登録されたトリガが自動的に起動されます。トリガを直接起動することはできません。トリガとトリガを起動するステートメントは単一のトランザクションとして扱われます。トリガ内の任意の場所からロールバックすることができます。トリガを作成するには、CREATE TRIGGER ステートメントを使用します。トリガを定義するテーブル、トリガを実行するイベント、トリガによって実行される SQL ステートメントを指定します。トリガは、複雑なデータ整合性の適用や、データベース内の関連テーブル全体での変更の連鎖を行います。
11. データウェアハウスと OLAP
SQL Server が提供するデータベースは、トランザクション処理を主体とする基幹系データベースとして利用から、企業分析を行う情報系データベースでの利用へと拡大しています。データウエア ハウスについて説明します。
11.1. データウェアハウスの特徴
データ ウエアハウスとは、企業の情報を集約し、企業分析を行うためのデータのことです。データ ウエアハウスに保存されているデータは、OLTP システムのデータとは異なる性質を持っています。
サブジェクト指向 併合性 不揮発性 履歴性
データ ウエアハウスは企業全体のデータを保存しますが、データマートは特定の部門や特定業務だけのデータを保存します。
データ ウエアハウジングを構成するコンポーネントは以下のとおりです。
.jpg)
図 10. データウエア ハウジングを構成するコンポーネント
コンポーネント |
機能 |
|---|---|
データ変換サービス |
データの移動および変換を行います。 |
データ ウエアハウスストレージ |
SQL Server Analysis Services が分析を行うためのデータストレージです。 |
Analysis Services |
リレーショナルデータベースからデータを取り出し、分析アプリケーションのために、キューブを作成します。 |
クライアントアプリケーション |
Excel2000 は Microsoft が開発した OLAP クラアントです。Pivot Table が分析サーバーと連携して動作します。 |
11.2. 多次元モデル
スター スキーマ
データ ウエアハウスでは、データをスター スキーマと呼ばれる構造に編成します。スター スキーマの特徴は、中心にファクト テーブルがあり、それを取り囲む様に、数多くのディメンションテーブルがあります。
キューブ（多次元モデル）
キューブは、OLAP データベース用の論理的なストレージ構造です。キューブは、ディメンションとメジャーを統合しています。ユーザはこのキューブを操作してクエリーを行います。キューブの各セルには、スプレッドシートのようにそれぞれ １ つの値を保持しています。各セルの値は、ディメンションが交差した場所の値です。例えば、商品、地域、期間ごとの売上が格納されています。
.jpg)
図 11. キューブとスライス
スライス
スライスは、キューブのどのディメンションのどの項目を他のディメンションの中で抽出するか指定します。
このキューブの例では、特定の製品（Cherries）の 4 半期ごとのすべての地域を売上を抽出しています。
ドリルダウン／ドリルアップ
すべてのディメンションには、階層が含まれています。例えば、期間は、年、四半期、月、日のレベルがあります。階層を構成する複数のレベルに対して、ドリルダウン／ドリルアップが可能です。
処理 |
説明 |
|---|---|
ドリルダウン |
階層の下の階層を確認できます。例えば、季節変動を確認するには、四半期から月へドリルダウンします。 |
ドリルアップ |
詳細情報は隠され、上の階層の要約情報だけが表示されます。例えば、都市から州へドリルアップします。 |
11.3. データ マイニング
多くの企業では、データの量が多すぎて、多量のデータから意味のある情報を識別することは困難です。
データ マイニングは、多量のデータから意味のあるパターンやルールを導き出す自動的な処理です。導き出されたパターンから重要な洞察を行い、従来の手作業の分析方法では見落としがちだった情報を得ることができます。
SQL Server Analysis Servicesには、以下のデータ マイニング技法があります。
技法 |
説明 |
|---|---|
クラスタリング |
お互いに類似したデータをグループ化するときに使用します。マーケットや顧客分析として使用されます。 |
ディシジョン ツリー |
分類、予測の両方に使用されます。一連の質問およびルールを使用してデータのケースを分類します。一定タイプのケースに特有の結果が現れる可能性を予測できます。 |
12. Web コンピューティング
Web コンピューティングによるデータ公開やデータ交換が一般化されています。 Webコンピューティングの現状について説明します。
12.1. Ｗｅｂ サーバと ＤＢ サーバの連携
WWW の普及により、インターネットベースの Web システムの需要が高まりました。従来、Web コンテンツは静的な HTML 文書を公開するだけでしたがが、動的なコンテンツに変化しました。さらに、取り扱っているデータもファイルから、データベースへと変化しています。クライアントにはWebブラウザのみを配置します。業務処理は Web サーバーのアプリケーションプログラムで実行されます。IIS (Internet Information Server) は WindowsNT/2000 で動作する Web サーバーです。IIS では、下記のゲートウェイインターフェースを使用して Web アプリケーションを開発できます。
インターフェース |
説明 |
|---|---|
CGI CommonGateway Interface |
Perlなどのスクリプト言語を利用します。インタープリタを使用して実行されます。アプリケーションが起動されるたびに、独自プロセスが実行されます。そのため、トラフィックの多いWebサーバーには不向きです |
ＩＳＡＰＩ Internet Server Application Program Interface |
ISAPI を利用したアプリケーションは、IIS のインプロセス (DLL) として実行されるため、高速に動作します ASP (Active Server Pages) は ISAPI のモジュールとして提供されるスクリプトエンジンです。VBScript や Jscript が使用出来ます。 |
- Web アプリケーションのセッション管理
クライアント／サーバーアプリケーションは、１ つのセッション（接続）でデータ交換を行いますが、Web アプリケーションを作成した場合、Web ブラウザと Web サーバー間のやり取りは １ 度のデータ交換でセッションは切断されます。そのため、連続したデータ交換を行うためには、接続情報を維持しておく必要があります。
12.2. ＸＭＬ
マークアップランゲージは、テキスト文書の中に記号をつけて、文書の構造や表現するための言語です。具体的な言語には HTML、XML があります。ＸＭＬ（eＸtensible Markup Language）は HTML と同様に SGML （Standard Generalized Markup Language） から派生しました。 HTML では、文書のレイアウトや、構造をタグを使用して記述出来ますが、定義済みのタグしか使用することが出来ません。そのため、HTML 文書中のデータに対して意味付けを行うことが出来ません。XML では、文書中のデータに独自のタグ付けを行うため、データに意味付けを行うことが出来ます。
XML は拡張可能なマークアップ言語です。以下の特徴があります。タグの意味を独自に定義できるテキストファイルとして保存できる
XML は、企業間の電子データの交換において重要な言語です。今後広く使用されます。
SQL Server の XML 対応
SQL Server は、XML に対応しています。SELECT ステートメントで FOR XML AUTO 句を使用すると、以下の処理を行います。
クエリ結果尾を文字列で返す
データ属性をタグとして返す
結果は、すべての列が １ つの文字列に結合して返されます。クライアントブラウザはタグを使用して、返されたデータをフォーマットします。
- SQL Server と IIS の連携
HTTP プロトコルを使用し、URL を記述することで、SQL Server にアクセスすることができます。テーブルに直接アクセスできます。
SQL Server と IIS の連携を行って HTTP を使用して、SQL Server にアクセスできるようになります。SQL Server と IIS の連携を行うには、IIS の仮想ディレクトリーの設定を行う必要があります。
SQL Server の IIS 仮想ディレクトリー管理ツールを使用します。
.jpg)
図12. WebブラウザからのXMLの利用