「Open棟梁 wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
目次 †
概要 †
データアクセス関連の処理方式についてまとめる。
コネクション & トランザクション管理 †
コネクション管理方式 †
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棟梁”は手動トランザクションもサポートしている）。
サンプル・コード †
- 例えば、
- 手動のトランザクション管理や、
- 2層C/Sで２本目のコネクションを
必要とする場合など、
- データアクセス制御クラス（Dam）を手動で生成して、
コネクション、トランザクションの手動管理をする事でこれを実現できる。
- 以下は、B層のサンプル コードである。
/// <summary>業務処理を実装</summary>
/// <param name="testParameter">引数クラス</param>
private void UOC_メソッド名(TestParameterValue testParameter)
{ //メソッド引数にBaseParameterValueの派生の型を定義可能。
// 戻り値クラスを生成して、事前に戻り地に設定しておく。
TestReturnValue testReturn = new TestReturnValue();
this.ReturnValue = testReturn;
// ↓業務処理-----------------------------------------------------
// データアクセス制御クラス（Dam）
// SQL Server / SQL Client用のDamを手動で生成
BaseDam dam = new DamSqlSvr();
// 接続文字列をロード
string connstring = GetConfigParameter.GetConnectionString("ConnectionString_SQL");
// コネクションをオープンする。
dam.ConnectionOpen(connstring);
// 手動トランザクション（規定の分離レベル）
dam.BeginTransaction(DbEnum.IsolationLevelEnum.ReadCommitted);
// 個別Dao
LayerD myDao = new LayerD(dam);
//myDao.xxxx(testParameter, ref testReturn);
// 汎用Dao
CmnDao cmnDao = new CmnDao(dam);
cmnDao.ExecSelectScalar();
// トランザクション、コネクションの解放
dam.CommitTransaction();
dam.ConnectionClose();
// ↑業務処理-----------------------------------------------------
}
トランザクション、コネクションの解放処理（CommitTransaction、ConnectionCloseメソッド）には、try...catch...finallyを使用しても良いが、
通常GCにより解放される場合、トランザクションはロールバックされ、コネクションも切断される（若しくは接続プールに戻される）。
排他制御方式 †
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図から読み取ることが出来る（特に削除処理が無い場合など）。
- また、後述のデッドロック防止のためのロックシーケンスの基準に利用できる。
DBMS側機能の使用 †
デメリット †
DBMS側機能を使用すると
- ビジネス・ルール実装箇所の分散（業務UP側 ⇔ DBのDDL・DML側）
- 開発スキルセットの増加。
- 上記に伴う保守性の低下。
データアクセス速度が速いので大量データの処理がある場合は使用を検討する。
トリガ †
トリガのイベントドリブンの問題
イベントドリブンでの頻繁なデータアクセスにより、
パフォーマンス・保守性の低下、トリガ連鎖のリスクがある。
外部参照制約 †
データのインサートやデリートの処理順番を考慮する必要がある。
大量データの処理 †
参考：大量データの処理
結果セットの肥大化の軽減 †
SQLの「SELECT * FROM］を使用しない。 †
必要なカラムの射影を取得するようにする。
ページングにより結果セットを分割する。
ラウンドトリップの軽減 †
イテレータの様に主キーでのDBアクセスをしない †
- 範囲検索をする。
- SQLのWHERE句でIN句やBETWEEN演算子を使用する。
プログラムでデータを結合しない †
その他、PG <---> DB間のラウンドトリップを防止 †
前述の
を考慮した実装を行う。
複数DBMSサポート方式 †
Dam Factory †
「業務コード親クラス２」のコネクション接続メソッド（MyBaseLogic?.UOC_ConnectionOpen）に
GoFデザインパターンのFactory Method パターン的にデータ アクセス制御クラス（Dam） を
DBMS（データプロバイダ）毎に用意された指定の型で選択的に生成・保持する処理を実装する。
なお、このコネクション接続メソッドでは、
の処理を実装する。
以降の処理ではDamをベースの型で扱うようにして
DBMS（データプロバイダ）固有型（機能）の使用を局所化する必要がある。
DBMS排他制御 †
「トランザクション制御方式」の「排他制御方式」を検討しておく。
「レコード更新履歴の記録」の方法を検討しておく。
「ID採番方式」の方法を検討しておく。
「一覧ページ制御方式」の方法を検討しておく。
.NET型とDBMS型のマッピング †
引数と戻り値に既定の型のマッピング（.NET型 <---> DB型）がある。
- 引数は、パラメタに対するDB型を指定することで程度、制御できる。
- 戻り値は、SQL上でのDB型を任意の型にキャストすることで程度、制御できる。
トラブルシュート †
SQLで暗黙の型変換が発生し、検索処理性能が大幅に劣化する。 †
概要 †
- 検索条件に指定した「パラメタのデータ型」と「DBの列のデータ型」が不一致となるとき、列のデータがパラメタのデータ型にキャストされて検索される。
- これを「暗黙の型変換」と言い、この「暗黙の型変換」が発生すると、インデックスが正しく使われず、性能が大きく劣化するという問題が発生する。
- Open棟梁の動的パラメタライズド・クエリの実行時、
- 「SetParameter」メソッドからパラメタのデータ型を指定できるが、データ型の明示が必要になるのは、「暗黙の型変換」が発生した場合のみである。
- このため、通常は、データ型を明示せず、性能劣化が確認された場合、外出しのSQLファイルを編集（パラメタのデータ型をSQL内でキャスト）するという方法を推奨するベターユースとする。
対策例 †
以下に、パラメタライズド・クエリを使用して、AAAテーブルのBBBカラム（varchar型、インデックス作成済み）に対して、
@P1パラメタの検索条件を指定した際に発生した「暗黙の型変換」による性能劣化 ～ 対策の事例を示す（SQL Serverでの事例）。
- 暗黙の型変換の発生例
.NETから、@P1パラメタにSystem.Stringのデータを設定した場合、@P1パラメタのデータ型は、nvarchar型になる。
この場合、「パラメタのデータ型」と「DBの列のデータ型」が不一致（varchar型 ⇔ nvarchar型のアンマッチ）となるため、「暗黙の型変換」により、
BBB列の全データが「varchar ⇒ nvarchar」と変換されるため、インデックスが使用されない（インデックス シークではなく、インデックス スキャンが発生する）。
- 暗黙の型変換の抑止
前述のとおり、@P1パラメタのデータ型はnvarchar型であるが、
@P1パラメタは、その後、SQL ServerのTransact-SQLの変換関数 により「varchar ⇒ nvarchar」と変換される。
このため、「パラメタのデータ型」と「DBの列のデータ型」は一致し、「暗黙の型変換」は発生しないため、インデックスも正しく使用される。
補足 †
- なお、動的パラメタライズド・クエリの「LISTタグ」を使用した場合、
ArrayList?に指定したパラメタの数に合わせて、パラメタが自動的にパラメタ リストに展開されるため、
上記の外出しのSQLファイルを編集するという方法で対策することができない。
- この場合は、
- APIからパラメタのデータ型を明示する方法を採るか、
- 「LISTタグ」ではなく「VALタグ」を使い、
各DBMSの変換関数を仕掛けたパラメタを必要な数だけ埋め込むようにして対処するようにする。
その他 †
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
