Open棟梁 wiki
目次 †
概要 †
データアクセス関連の処理方式についてまとめる。
コネクション管理方式 †
ConnectionのOpen/Close †
ConnectionのOpen/Close処理の実装は、”Open棟梁”のB層の「ベースクラス2」（業務コード親クラス２）上に共通処理として実装できる。
- メソッド毎に共通処理の動作を変更する場合は、「メソッド属性」などを利用すると良い。
Connection Pooling †
コネクション プーリングに関しては、通常データ プロバイダが保有するコネクション プーリング機能をそのまま使用する。
トランザクション制御方式 †
自動トランザクション †
- TransactionのBeginとCommit/Rollback処理の実装は、”Open棟梁”のB層の「業務コード親クラス１、２」によって共通化される。
- このため、APサーバを用いた処理方式の場合、DBMSトランザクションは、B層（Tx Root）開始から終了時までとなる（2層C/S方式対応B層を使用している場合はこの限りではない）。
手動トランザクション †
１回のB層呼び出しに対して、複数のコネクション、トランザクションが必要となる要件が発生した場合は、
手動トランザクションを検討する（”Open棟梁”は手動トランザクションもサポートしている）。
排他制御方式 †
HTTPリクエストなど、通信処理（リクエスト）を跨いだ排他制御方式については、
通信処理（リクエスト）を跨いでDBMSトランザクションを持続できないので、
リクエストを跨いで排他（ロック）を掛けるには、楽観排他方式・悲観排他方式を採用する。
DBMSトランザクションの範囲の排他制御方式 †
DBMSトランザクションの範囲は、DBMSの分離レベルに従った排他制御の動作を理解し、
必要に応じて更新ロック（OracleであればFor Update Wait・NoWait?、SQLServerであればWith (UpdLock?)）など、個別にロックを適用する 。
楽観排他方式 †
全列比較方式 †
.NETでは、DataTable?がサポートするDataRowVersion?を使用した楽観方式
- 具体的には、Where句で全列のAnd = DataRowVersion?.Originalを指定する方式。
が一般的であるが、クエリが無用に長くなる等の問題もあるのでタイムスタンプ方式を推奨する。
タイムスタンプ方式 †
楽観排他方式（タイムスタンプを使用する）は、
- データの取得時に、タイムスタンプを取得し、
- データの更新時に、Where句に主キーとタイムスタンプ列を指定する。
排他方式である。
SQL ServerやOracleにはタイムスタンプ型がある。
- SQL ServerのTimeStamp?型は、Insert・Update時に更新が自動的に行われる。
- Oracleでは、Insert、Update時にOracle埋め込み関数である、systimestamp関数を使用してタイムスタンプ列を更新する。
D層自動生成ツール †
なお、”Open棟梁”のD層自動生成ツールでは、
楽観排他（ロック）対応のDao生成が可能で、
- タイムスタンプ更新の自動化
- タイムスタンプ指定の強制
などが可能である。
悲観排他方式（ロック管理テーブル） †
以下のケースで、ロック管理テーブルなどを使用して悲観排他を実現する。
- DBMSトランザクションを維持できない、サーバアプリケーションにおいて、
タイムスタンプを使用した楽観排他（ロック）ではなく、悲観排他（ロック）を実装する必要がある場合
- 若しくは、業務的に子レコードの更新の際に親レコードの悲観排他（ロック）実装する必要がある場合
行単位の悲観排他（ロック）を必要とするテーブル毎に「行ロック管理テーブル」を用意しておく。
行ロック管理テーブル †
「行ロック管理テーブル」は次のように設計すると良い。
| キー（ユニーク） | ロック ユーザ | ロック取得時間 |
| aaa | User1 | yyyy/MM/dd HH:mm:ss.SSS |
| bbb | User2 | yyyy/MM/dd HH:mm:ss.SSS |
| ccc | User3 | yyyy/MM/dd HH:mm:ss.SSS |
処理シーケンス †
行単位の悲観排他（ロック）方式の処理シーケンスを以下に示す。
- トランザクションを開始して更新先テーブルを参照し、更新ロックをかけた状態で更新対象データ行を取得する。
- 同一トランザクション内で「行ロック管理テーブル」に、上記の更新対象データ行の主キーを追加する。
追加の際、主キーは「キー（ユニーク）」列へ、ユーザ名は「ロック ユーザ」列へセットする。
- 追加に失敗した場合は、悲観排他（ロック）取得に失敗したものとし、処理を中断する。
- 追加に成功した場合は、悲観排他（ロック）取得に成功したものとし、処理を続行する（トランザクションをコミット）。
- 取得した更新対象データ行をクライアントにレスポンス、画面に表示し、編集処理を行う。
- トランザクションを開始して
- 更新先テーブルを参照し、更新ロックをかける。
- 「行ロック管理テーブル」の更新対象データに対応するレコードを参照し、更新ロックをかける。
- 上記の２つのレコードのうち、
- どちらか一方 or 双方が存在しない場合は、処理に失敗したものとし、処理を中断する。
- 双方が存在する場合は、処理に成功したものとし、処理を続行する。
- 「行ロック管理テーブル」に更新ロックをかけた状態で、更新対象データ行の更新を行う。
- 同一トランザクション内で「行ロック管理テーブル」のレコードを削除する（トランザクションをコミット） 。
- テーブル単位に管理テーブルを分割するため、負荷は軽減できている。
- ログアウトの際に「行ロック管理テーブル」のロック ユーザの条件に一致するレコードは削除するようにする。
- 必要であれば、他のロック解除を忘れた場合にもユーザが悲観排他（ロック）を取得できるようにするため、
ロック取得時間にタイムアウトを儲けるか、「行ロック管理テーブル」のレコードを消去するメンテナンス機能を実装するなどする。
テーブル設計 †
コード設計 †
コードには以下の種類がある。
採番コード †
- 非正規化マッピングにてID化したトランザクションデータの主キー。
- 採番方法については、本ドキュメントの「ID採番方式」を参照のこと。
リストコード †
- 可変リストコード
- 変更可能であり、PG埋め込み不可能なコード。
- 非正規化マッピングにてID化したマスタテーブルの主キー。
- 非可変リストコード
- 変更不可能であり、PG埋め込み可能なコード。
- 区分やステータスなど。
削除方式 †
物理削除か？論理削除か？を検討する。
物理削除 †
- DELETEでデータを削除する。
- データが履歴として残らない、
バックアップなどからしか復元できない。
- データ量を削減できる。
論理削除 †
- UPDATEで削除フラグを立てる。
- データが履歴として残る。
削除フラグを変更してデータを復元できる。
- データ量を削減できない。
データのアーカイブ方法 †
増え続けるトランザクションデータは性能やメンテナンス上の問題を引き起こすため、データのアーカイブ方法を検討しておく。
- インサート → デリートの削除バッチでアーカイブ・テーブルに移動する方式もあるが・・・、
- 必要であればパーティション化によってパーティション毎にアーカイブ・テーブルに移動する
スライディング・ウィンドウ方式もある（こちらの方がトランザクション・ログなども生成されず高速である）。
マスタ・データ †
有効期限付きデータを利用する場合 †
- メリット
オン中の自動切り替えが可能であるため、運用が容易。
- デメリット
データ取得に工夫が必要になる
（Where句に有効期限と現在日付の比較を含めるなど）。
- 必要な列
- 「ID（PK）」
- 「業務キー（unique）」
- 「業務キーに対応する情報」
- 「有効期限」
システム共通定数テーブル †
- メリット
テーブルを纏められるため、管理が容易になる。
- デメリット
- データ取得に工夫が必要になる（Where句に定数区分を含めるなど）。
- 外部参照制約は利用できない（主キーで無いため）。
- テーブル名が無く、汎用のテーブル名列名なので仕様が見えない。
例えば、「定数区分キー」に対応する「業務キー」の業務的な意味が不明確。
- 列長等も所謂「十分な大きさ」になるため、テーブル側仕様での歯止めが弱くなる。
- 必要な列
- 「ID（PK）」
- 「定数区分キー」＋「業務キー」（unique）
- 「「定数区分キー」＋「業務キー」に対応する情報」
（有効期限付きデータの併用可能）。
ID採番方式 †
SEQUENCE・IDENTITY †
必ず、連番にならなくても良ければ、
DBMSに実装されているSEQUENCEやIDENTITYを使用する。
以下、主要DBMSの採番機構を表に纏めた。
| 項番 | DBMS | 採番機構 | 確認方法 |
| 1 | Oracle | SEQUENCEオブジェクト | SEQUENCE.CURRVALを確認する。 |
| 2 | SQL Server | オート インクリメント列（IDENTITYプロパティ） | INSERTに続けてSCOPE_IDENTITYを確認する。
INSERT xxx(…) VALUES(…); SELECT SCOPE_IDENTITY ();
※ パラメタライズド・クエリではsp_executesql経由で実行されるので別のスコープで実行される。
このため、上記の様に、INSERTとSELECTを連続して記述する必要がある。 |
| 3 | DB2 | SEQUENCEオブジェクト | ・・・ |
| 4 | MySQL | オート インクリメント列（AUTO_INCREMENT属性） | ・・・ |
| 5 | PostgreSQL | SEQUENCEオブジェクト | ・・・ |
| 6 | HiRDB | SEQUENCEオブジェクト | ・・・ |
Windows Server 2008 R2から、SEQUENCEオブジェクトがサポートされたらしい。
連番採番方式 †
必ず連番にする連番採番方式は、要件に合わせて個別に設計する。
- 連番採番（追い越しを不許可）：
- 採番テーブルをテーブルロック → 同時実行性 低
- 例えば、採番テーブルにはテーブルロックをかけて業務トランザクション内に含める必要があるので、当該トランザクションの同時実行性は低くなる。
- 連番採番（追い越しを許可）：
- 「予約通番」を用いて追い越しを許可 → 同時実行性 高
- ロックが長くなり同時実行性の低下が問題になる場合は、
「予約通番」を用いて追い越しを許可した連番採番を行う
（初めに予約採番し、確定時に連番採番し、予約通番を実際の連番に変更する）。
レコード更新履歴の記録 †
”Open棟梁”では直接サポートしていないが、
「ベースクラス2」（データアクセス親クラス２）上に共通処理を実装できる。
方式は色々あるので、プロジェクト毎に方式を決めること。
履歴情報 †
全テーブル共通項目などを用意して、
- ユーザ名、データ追加日時
- ユーザ名、データ更新日時
- ユーザ名、データ削除日時（論理削除 → 論理削除方式は別途検討）
の情報を保持するなど（このような処理はDaoで共通化できる）。
SQL（DML）の記録 †
プロジェクト対応で、「ベースクラス2」（データアクセス親クラス２）上にSQLトレース ログをDB上に出力する共通処理などを実装したケースはある。
更新前データの記録 †
また、DBMSのトリガを使用することで更新前のデータを別テーブルに記録することもできる。
データアクセス標準化 †
CRUD図 †
- 機能の過不足はCRUD図から読み取ることが出来る（特に削除処理が無い場合など）。
- また、後述のデッドロック防止のためのロックシーケンスの基準に利用できる。
結果セットの肥大化の軽減 †
SQLの「SELECT * FROM］を使用しない。 †
必要なカラムの射影を取得するようにする。
ページングにより結果セットを分割する。
ラウンドトリップの軽減 †
イテレータの様に主キーでのDBアクセスをしない †
- 範囲検索をする。
- SQLのWHERE句でIN句やBETWEEN演算子を使用する。
プログラムでデータを結合しない †
その他、PG <---> DB間のラウンドトリップを防止 †
ストアドプロシージャ、SQL CLRなどを使用する。
- 大量の処理前データ、処理後データをメモリ上に溜めこまない。
- 同時に、ラウンドトリップの軽減も図る必要がある。
DBMS側機能の使用 †
デメリット †
DBMS側機能を使用すると
- ビジネス・ルール実装箇所の分散（業務UP側 ⇔ DBのDDL・DML側）
- 開発スキルセットの増加。
- 上記に伴う保守性の低下。
ストアド・プロシージャ †
データアクセス速度が速いので大量データの処理がある場合は使用を検討する。
トリガ †
トリガのイベントドリブンの問題
イベントドリブンでの頻繁なデータアクセスにより、
パフォーマンス・保守性の低下、トリガ連鎖のリスクがある。
外部参照制約 †
データのインサートやデリートの処理順番を考慮する必要がある。
複数DBMSサポート方式 †
Dam Factory †
「業務コード親クラス２」のコネクション接続メソッド（MyBaseLogic?.UOC_ConnectionOpen）に
GoFデザインパターンのFactory Method パターン的にデータ アクセス制御クラス（Dam） を
DBMS（データプロバイダ）毎に用意された指定の型で選択的に生成・保持する処理を実装する。
なお、このコネクション接続メソッドでは、
の処理を実装する。
以降の処理ではDamをベースの型で扱うようにして
DBMS（データプロバイダ）固有型（機能）の使用を局所化する必要がある。
DBMS排他制御 †
「トランザクション制御方式」の「排他制御方式」を検討しておく。
「レコード更新履歴の記録」の方法を検討しておく。
「ID採番方式」の方法を検討しておく。
「一覧ページ制御方式」の方法を検討しておく。
.NET型とDBMS型のマッピング †
引数と戻り値に既定の型のマッピング（.NET型 <---> DB型）がある。
- 引数は、パラメタに対するDB型を指定することで程度、制御できる。
- 戻り値は、SQL上でのDB型を任意の型にキャストすることで程度、制御できる。
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
