Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 1 Sep 2011 18:56:10 +0000 (UTC)
From: "Kristian Waagan (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <339024372.7972.1314903370326.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1080959284.600.1312263927387.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-5367) Stale data retrieved when using new
 collation=TERRITORY_BASED:PRIMARY feature
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-5367?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13095531#comment-13095531 ] 

Kristian Waagan commented on DERBY-5367:
----------------------------------------

Thanks, Mike.

First, I wrote a very simple micro-benchmark. It ran an insert followed by a delete on a table with a primary key in a loop.
With 30 seconds warm up, 60 seconds steady state and the average of three runs per codebase I ended up with a performance degradation of around 3.6% (insane build, using in-memory back end). I'll run this with the disk-based back end later, but first I have another issue to deal with - so these number may be invalid after all...

With the simplest fix possible, which is to replace the updateFieldAtSlot with updateAtSlot, I'm getting a total of 33 errors in suites.All. I didn't see these when I tested the first patch, probably because the failing tests aren't using collations so the new code was never run in these tests. Here's what the core exception looks like, which can be reproduced by running lang.AlterTableTest (need only dropColumn + for instance testJira3175):
java.lang.ArrayIndexOutOfBoundsException: -1
        at org.apache.derby.impl.store.raw.data.BasePage.getHeaderAtSlot(BasePage.java:1881)
        at org.apache.derby.impl.store.raw.data.StoredPage.storeRecordForUpdate(StoredPage.java:7316)
        at org.apache.derby.impl.store.raw.data.StoredPage.storeRecord(StoredPage.java:7185)
        at org.apache.derby.impl.store.raw.data.UpdateOperation.undoMe(UpdateOperation.java:201)
        at org.apache.derby.impl.store.raw.data.PhysicalUndoOperation.doMe(PhysicalUndoOperation.java:147)
        at org.apache.derby.impl.store.raw.log.FileLogger.logAndUndo(FileLogger.java:533)
        at org.apache.derby.impl.store.raw.xact.Xact.logAndUndo(Xact.java:374)
        at org.apache.derby.impl.store.raw.log.FileLogger.undo(FileLogger.java:1015)
        at org.apache.derby.impl.store.raw.xact.Xact.abort(Xact.java:952)
        at org.apache.derby.impl.store.access.RAMTransaction.abort(RAMTransaction.java:1985)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.doRollback(GenericLanguageConnectionContext.java:1767)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.userRollback(GenericLanguageConnectionContext.java:1675)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.rollback(TransactionResourceImpl.java:245)
        at org.apache.derby.impl.jdbc.EmbedConnection.rollback(EmbedConnection.java:1838)
        at org.apache.derbyTesting.junit.JDBC.cleanup(JDBC.java:224)
        at org.apache.derbyTesting.junit.BaseJDBCTestCase.tearDown(BaseJDBCTestCase.java:408)

I do see that there are some differences related to undo-logic for the two method calls, but that may very well be a red herring. The problem does seem to be related to logging and transactions.
So, there's obviously something I don't understand here... I'll continue digging tomorrow - any hints would be helpful :)

> Stale data retrieved when using new collation=TERRITORY_BASED:PRIMARY feature
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-5367
>                 URL: https://issues.apache.org/jira/browse/DERBY-5367
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.8.1.2, 10.9.0.0
>         Environment: Mac OS X, Windows
>            Reporter: Brett Wooldridge
>            Assignee: Kristian Waagan
>            Priority: Critical
>         Attachments: derby-5367-1a-update_row_fully.diff, derby-5367-1b-update_row_fully.diff, derby-5367-1b-update_row_fully.stat, derby-5367-2a-minimal_fix.diff
>
>
> Our product recently upgraded to version 10.8.1.2 in order to take advantage of the new 'case-insensitive' mode offered by Derby in the form of the "collation=TERRITORY_BASED:PRIMARY" connection parameter.
> Unfortunately, we have run into an issue whereby stale data appears to be retrieved from an index, even though the data in the table itself has changed.
> You can see this issue in the IJ session below.  The database in question was created using this Java parameter when invoking IJ:
> -Dij.database=jdbc:derby:test;create=true;collation=TERRITORY_BASED:PRIMARY
> Here is the IJ session:
> CONNECTION0* - 	jdbc:derby:test
> * = current connection
> ij> CREATE TABLE tag (
>     tag_id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>     tag VARCHAR(255) NOT NULL,
>     CONSTRAINT tag_pk PRIMARY KEY (tag_id),
>     CONSTRAINT tag_tag_unique UNIQUE (tag)
> );
> 0 rows inserted/updated/deleted
> ij> -- first insert a value 'Test', note the upper-case 'T' in 'Test'
> ij> INSERT INTO tag (tag) VALUES ('Test');
> 1 row inserted/updated/deleted
> ij> SELECT * FROM tag;
> TAG_ID     |TAG                                                                                                                             
> --------------------------------------------------------------------------------------------------------------------------------------------
> 1          |Test                                                                                                                            
> 1 row selected
> ij> -- Now delete the row
> ij> DELETE FROM tag WHERE tag='Test';
> 1 row inserted/updated/deleted
> ij> -- You could run another SELECT here to verify it is gone, but it is.
> ij> -- Now insert a new value 'test', note the lower-case 't' in 'test'
> ij> INSERT INTO tag (tag) VALUES ('test');
> 1 row inserted/updated/deleted
> ij> -- Now verify that the table contains only the lower-case version: 'test'
> ij> SELECT * FROM tag;
> TAG_ID     |TAG                                                                                                                             
> --------------------------------------------------------------------------------------------------------------------------------------------
> 2          |test                                                                                                                            
> 1 row selected
> ij> -- Now, here is the bug.
> ij> SELECT tag FROM tag;
> TAG                                                                                                                             
> --------------------------------------------------------------------------------------------------------------------------------
> Test                                                                                                                            
> 1 row selected
> ij> 
> Note in the last SELECT we specify the 'tag' column specifically.  When we 'SELECT *', Derby performs a table-scan and the result is correct.  However, when we 'SELECT tag', Derby appears to use the index created for the 'tag_tag_unique' unique constraint.  As an optimization Derby, like many databases, will use values directly from the index in the case where the index covers all requested columns.
> The bigger question is, why doesn't the DELETE action cause the entry in the tag_tag_unique index to be deleted?  Is this a further optimization?  If so, it is imperative that the index at least be updated when the new value is inserted.
> This is rather a severe bug for us that causes stale data to be returned.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira