Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 5498 invoked from network); 20 Jan 2008 18:35:00 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 20 Jan 2008 18:35:00 -0000 Received: (qmail 36279 invoked by uid 500); 20 Jan 2008 18:34:47 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 36246 invoked by uid 500); 20 Jan 2008 18:34:47 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 36156 invoked by uid 99); 20 Jan 2008 18:34:47 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 20 Jan 2008 10:34:47 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.130] (HELO eos.apache.org) (140.211.11.130) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 20 Jan 2008 18:34:27 +0000 Received: from eos.apache.org (localhost [127.0.0.1]) by eos.apache.org (Postfix) with ESMTP id CC9E5D2DB for ; Sun, 20 Jan 2008 18:34:33 +0000 (GMT) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: Apache Wiki To: derby-commits@db.apache.org Date: Sun, 20 Jan 2008 18:34:33 -0000 Message-ID: <20080120183433.19554.76960@eos.apache.org> Subject: [Db-derby Wiki] Update of "Derby3192Writeup" by DyreTjeldvoll X-Virus-Checked: Checked by ClamAV on apache.org Dear Wiki user, You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification. The following page has been changed by DyreTjeldvoll: http://wiki.apache.org/db-derby/Derby3192Writeup ------------------------------------------------------------------------------ - #pragma section-numbers 2 + #pragma section-numbers 1 - = Changes introduced in DERBY-3198 = + '''Table of Contents''' + [[TableOfContents(2)]] + + - == Goal == + = Goal = The patch implements caching of the isolation level in the client driver. The caching lets the client driver execute @@ -18, +21 @@ number of tests expecting the old behavior would fail. - == Motivation == + = Motivation = - One would ordinaryily not expect the performance of + One would ordinarily not expect the performance of - getTransactionIsolation() to be critical. However, the use of + `getTransactionIsolation()` to be critical. However, the use of connection pools (commonly found in application server environments), invalidates this assumption. @@ -43, +46 @@ server each time a connection enters (or leaves) the connection pool. - == The Stale Cache Problem == + = The Stale Cache Problem = Caching the isolation level in the context of JDBC is simple, and prior to [http://issues.apache.org/jira/browse/DERBY-1148 DERBY-1148] the client driver would cache the isolation level and only update its - cache whenever setTransactionIsolation() was called. As explained in + cache whenever `setTransactionIsolation()` was called. As explained in [http://issues.apache.org/jira/browse/DERBY-1148 DERBY-1148] this is not sufficient, as the isolation level can change without - setTransactionIsolation being called (XA, SQL, stored procedures, and + `setTransactionIsolation()` being called (XA, SQL, stored procedures, and functions). In all of theses cases the cached isolation level becomes stale (different from the actual isolation level in the embedded connection used by the NetworkServer. - == Solutions to the Stale Cache Problem == + = Solutions to the Stale Cache Problem = One approach to solving the problem would be to scan all activity on the connection (including statements and prepared statements) to @@ -79, +82 @@ attach/detach situation. - === Implementing Piggy-backing === + == Implementing Piggy-backing == The simplest and least intrusive way of implementing piggy-backing would be to create a call-back mechanism that would let - EmbedConnection (through the EngineConnection interface) inform its + `EmbedConnection` (through the `EngineConnection` interface) inform its `DRDAConnThread` that some information that the client is interested in (in this case the isolation level) has changed. The `DRDAConnThread` could then append this information to the next message being sent to the client. There are two problems with this approach that caused it not to be chosen for this patch. + 1. AFAICT DRDA does not allow the server to add replies to a message that do not correspond to a request from the client. - 1. AFAICT DRDA does not allow the server to add replies to a message - that do not correspond to a request from the client. + 1. The server would need some way of verifying that the client is capable of interpreting the piggy-backed information. - 1. The server would need some way of verifying that the client is - capable of interpreting the piggy-backed information. Another approach is to let the client "poll" the server for cachable values that have changed. This can be implemented using the @@ -116, +117 @@ incompatible with DRDA. - === Piggy-backing in DRDA === + == Piggy-backing in DRDA == - DRDA provedes the EXCSQLSET command to "batch" (for lack of a better + DRDA provides the EXCSQLSET command to "batch" (for lack of a better term) SET statements from a client to the server. This command is currently used to implement the `setQueryTimeout()` functionality. Patch v1 also uses this DRDA command to implement polling for a changed @@ -131, +132 @@ changed lines in the patch can be attributed to the fact that the signature for this family of methods needed to be changed, (as well as the fact that javadoc was added for these methods, including the - interface deaclarations). + interface declarations). `setQueryTimeout()` implements its functionality by letting this command send a special statement which also encodes the timeout value to @@ -161, +162 @@ update the relevant attributes (currently only `am.Connection.isolation_`). - ==== Use of the RTNSETSTT instance variable ==== + === Use of the RTNSETSTT instance variable === - The DRDA spec specifies (REF) that EXCSQLSET can have an optional + The DRDA spec specifies that EXCSQLSET can have an optional instance variable RTNSETSTT, that essentially is a boolean flag which allows the requester (the client) to tell the server that it expects (and can handle) SQLSTT codepoints in the reply. Since v1 is using - such SQLSTT codepoints to transmit the cached attributes "diff", it + such SQLSTT code points to transmit the cached attributes "diff", it also needs to set the RTNSETSTT instance variable when sending the request. Note that this is essentially "redundant" information since the server knows exactly what to do as soon as it has identified the @@ -180, +181 @@ actually execute a SET statement that can fail. - === Overhead Introduced by Piggy-backing === + == Overhead Introduced by Piggy-backing == As mentioned previously the polling approach will necessarily introduce some overhead both on requests and replies. - ==== Request ==== + === Request === One EXCSQLSET command is added to every message containing OPNQRY and CNTQRY commands. Most of the overhead seems to come @@ -193, +194 @@ 59% of the total overhead: || DRDA VARIABLE || SIZE (BYTES) || - DSS header 6 - EXCSQLSET 4 - PKGNAMCSN 76 - RTNSETSTT 4 - HEXSTRDR 5 + || DSS header || 6 || + || EXCSQLSET || 4 || + || PKGNAMCSN || 76 || + || RTNSETSTT || 4 || + || HEXSTRDR || 5 || - SQLSTT 4 + 1 + 4 + 23(strlen) + 1 + || SQLSTT || 4 + 1 + 4 + 23(strlen) + 1 || - || TOTAL || 128 || + || TOTAL || 128 || This gives a total overhead of (a whopping) 128 bytes. It can be reduced by shortening the name used for the "special @@ -209, +210 @@ further reduced to 96 bytes per request. Which is still substantial. - ==== Reply ==== + === Reply === The only mandatory variable in an EXCSQLSET reply is SQLCARD. When used to poll for modified session data, this variable has no real @@ -221, +222 @@ its own DSS): || DRDA VARIABLE || SIZE (BYTES) || - DSS header 6 - SQLCARD 65 + || DSS header || 6 || + || SQLCARD 65 || || - SQLSTT 4 + 1 + 4 + (4+1)(strlen) + 1 (15) + || SQLSTT 4 + 1 + 4 + (4+1)(strlen) + 1 (15) || - ... + || ... || ... || || TOTAL || 71/86 || When no session parameter has changed (the common case) only the 71 bytes of the DSS header and the SQLCARD will be added to the reply. If one or more session parameters have changed, one SQLSTT object will be - added for each changed paramter. The size of the SQLSTT obviously depends on the + added for each changed parameter. The size of the SQLSTT obviously depends on the value of the parameter, but since SQLSTTs are strings the byte count will also depend on whether single-byte or multi-byte encoding is used for the string representing the parameter value. - == Multiple Round-trip Replies == + = Multiple Round-trip Replies = Following the example of `setQueryTimeout()`, calls to `writeSetSpecialRegister()` and `readSetSpecialRegister()` are made @@ -251, +252 @@ reply is the first received and parsed after `...flow()` returns. When polling for changed session data the situation is the exact - oposite; the poll request needs to be the last thing added to the + opposite; the poll request needs to be the last thing added to the outgoing message to ensure that the poll picks up any changes in the session state that has happened as a consequence of the other commands in this message. As a result, the reply must be received and parsed at @@ -267, +268 @@ `parseScrollableRowset()` is made: {{{#!java - if (resultSet_ != null) { + if (resultSet_ != null) { - resultSet_.parseScrollableRowset(); + resultSet_.parseScrollableRowset(); }}} This happens irrespective of the type of the result set, but the call @@ -277, +278 @@ {{{#!java if (cursor_.dataBufferHasUnprocessedData() && scrollable_) { - parseRowset_(); + parseRowset_(); }}} `net.NetResultSet.parseRowSet_()` _may_ call @@ -287, +288 @@ {{{#!java void flowFetchToCompleteRowset() throws DisconnectException { + try { - try { - - agent_.beginWriteChain(statement_); + agent_.beginWriteChain(statement_); - - writeScrollableFetch_((generatedSection_ == null) ? statement_.section_ : generatedSection_, + writeScrollableFetch_((generatedSection_ == null) ? statement_.section_ : generatedSection_, - fetchSize_ - rowsReceivedInCurrentRowset_, + fetchSize_ - rowsReceivedInCurrentRowset_, - scrollOrientation_relative__, + scrollOrientation_relative__, - 1, + 1, - false); // false means do not disard pending + false); // false means do not disard pending - // partial row and pending query blocks + // partial row and pending query blocks - agent_.flow(statement_); + agent_.flow(statement_); - readScrollableFetch_(); + readScrollableFetch_(); - agent_.endReadChain(); + agent_.endReadChain(); }}} @@ -318, +317 @@ The problem is limited to *read only* result sets since *updatable* result sets always have a fetchsize equal to 1, regardless of what the - suggested fetchsize is. + suggested fetch size is. === Possible Solutions === @@ -338, +337 @@ At this point the best solutions seem to be + 1. Add the piggy-back request command to every CNTQRY and parse it in every reply (also those handled by `flowFetchToCompleteRowSet()`). - 1. Add the piggy-back request command to every CNTQRY and parse it in - every reply (also those handled by `flowFetchToCompleteRowSet()`). + 1. Implement some kind of bookkeeping mechanism that tracks if a command was included in the last request, and if the return value has been handled in the latest reply. This is more complicated but reduces the number of unnecessary piggy-back commands. - 1. Implement some kind of bookkeeping mechanism that tracks if a - command was included in the last request, and if the return value has - been handled in the latest reply. This is more complicated but reduces - the number of unnecessary piggy-back commands.