[DB][DB2] V8.2.2/ Concurrency enhancements

□Read scanners can skip non-committed INSERTs
today, many applications must only consider and process committed data - which eliminates the possibility of using the UR isolation level.
the RR isolation level is very strict and is not typically required.
this leaves the CS and RS isolation levels, which are the most widely used when only committed data is requred for processing.

when DB2 performs an index or table scan, DB2 will block on a row lock when it comes across an uncommitted row which another transaction has exclusively (X) locked.
if this row lock is protecting an uncommitted UPDATE or DELETE action, DB2 cannot process or ignore the row until the outcome of the change is known.
結果、
分離レベル CS と RS が一番求められる。
そのCS/RSは、コミット済みデータにのみアクセスでき、
未コミットデータの読み飛ばしは出来ない。
DB2_SKIPDELETEDDB2_EVALUNCOMMITTED
 等のレジストリ変数を設定すれば可能だけど。
 どちらも、仮削除と仮更新状態を実現し、
 ロールバックせん限り、変更なしと見なされる。

while this behavior is correct, when a row is locked due to an uncommitted INSERT --there are situations where application oqners would like DB2 to skip the inserted row that is awaiting a commit as if it where not there.

DB2 V8.2.2 introduces the ability for read-only scanners, from applications using the CR and RS isolation level, to skip inserted records that have not yet been committed by other transactions.
this new capability adds more flexibility to concurrency which will help the scalability of some applications.
to enable this new feature in DB2 V8.2.2, you use DB2_SKIPINSERTED registry variable.
although DB2 has the ability to change the isolation level on a per statement basis, the ability to skip uncommitted INSERTs is not available on a per statement level and this setting is considered at compile/bind time.

DB2_SKIPINSERTED=OFF がデフォルト(@DB2 V8.2.2)。
this makes DB2 befave as one would expect: the scanner waits until the INSERTtransaction commits or rolls back and then return the data business as usual.
depending on your applications and the data integrity characteristics associated with the business function, this may or may not be appropriate.

DB2_SKIPINSERTED=ON にすれば、
DB2 will treat the uncommitted INSERTs (for CS and RS isolation levels only) as if they had not yet been inserted.
this feature provides increased concurrency without sacrificing isolation semantics.
DB2 implements the ability for scanners to skip uncommitted inserted rows versus waiting when in conflict through lock attributes and feedback on lock requests.
you can see the INSERT lock attribute in the snapshot output.

□Enhanced evaluate uncommitted
DB2 V8.1.4 introduced the DB2_EVALUNCOMMITTED DB2 registry variable.
when enabled ( = TRUE | ON | YES | 1 ), it would modigy the begavior of read-only queries in DB2 such that it would allow locking to be deferred on an index scan (must be a Type 2 index, this feature is not supported with Type1 indexes) or table access (this feature is not supported on Range Clustered Tables) until all the predicates that qalified the statement where known.
this new registry variable was introduced to optionally improve the concurrency of some applications by essentially allowingread scans to defer or avoid row locking until a data record is known to qualify a particular query. inparticular, this feature was driven by SAP application, but the work is generally applicable to a lock aboidance schemes for a much greater customer base.

before DB2 V8.1.4 (and w/o setting this registry variable), DB2 would perform pessimistic locking: it would lock each row visited before validating if the row satisfied the exclusion predicates of the query.
this locking was done for both inex scans and table acess whether or not the row of data was committed or whether it would have been excluded based on the statement's predicates.

when the evaluate uncommitted behavior is enabled for your DB2 environment, you should be aware that predicate envaluation may occur on uncommitted data.
furthermore, deleted rows are skipped unconditionally on table scan access, while deleted keys are not skipped for type-2 index scans (unless you also set the DB2_SKIPdELETED registry variable).
if you were to solely set the DB2_SKIPDELETED registry variable in your environment, DB2 would allow unconditional skipping of deleted records on table scan access and skipping of pseudo-deleted index keys for type-2 index scan access.


the implementation in DB2 V8.1.4 however wasnt fully complete.
when evaluate uncommitted was first introduced in DB2 V8.1.4, it came with the following restrictions:
- this feature was only available for the CS and RS isolation levels.
- sargable predicates must exist for the evaluation.
- lock avoidance was not applicable to scans on the catalog tables.
- when scanning an MDC table, bloc locking could be deferred for an index scan. however, block locking was not be deferred for table scans.
-deferred locking did not occur on a table which was executing an online table reorganization.
- it was not possible for the Index Manager to call back to the Data Manager to fetch a data record w/o the row being locked first within the Index Manager. this meant that ISCAN-Fetch plans were not capable of deferred locking in the Data Manager.


DB2 V8.2.2 addresses these shortcomings by removing most of the restrictions in the first phase of Evaluate Uncommitted in DB2 V8.1.4. DB2 V8.2.2 introduces as new setting for the DB2_EVALUNCOMMITTED DB2 registry variable called DEFERISCANFETCH. when set, the lock avoidance afforded by this feature in DB2 V8.1.4 will work with ISCAN-FETCH data plans.



ref:
What's new in DB2 V8.2.2: Performance and concurrency enhancements in DB2 UDB V8.2.2
Part 2 of a series on the new features, functions, and benefits of DB2 UDB V8.2.2, 07 Jul 2005
http://www.ibm.com/developerworks/data/library/techarticle/dm-0507zikopoulos/index.html

tag : DB2 DB2_SKIPDELETED DB2_DB2_EVALUNCOMMITTED SKIPINSERTED

2009-05-19 06:31 : 開発 : コメント : 0 : トラックバック : 0 :
コメントの投稿
非公開コメント

« next  ホーム  prev »

search

ad



counter


tag cloud

category cloud