db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brett Wooldridge (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5367) Stale data retrieved when using new collation=TERRITORY_BASED:PRIMARY feature
Date Tue, 20 Sep 2011 01:23:09 GMT

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

Brett Wooldridge commented on DERBY-5367:
-----------------------------------------

Kristian,

With respect to this particular case (collation), the insert-delete-insert pattern is strictly
based on user input.  How this was discovered was that a user created a "tag" on an entity
in our system (eg. 'cisco'), realized they had mistyped, deleted 'cisco', and added 'Cisco'.
 This resulted in the lower-case 'cisco' coming back (because of this bug).  So to answer
you question, with respect to this bug, I would classify it as "low frequency".

However, obviously, insert-delete-insert patterns are fairly common in general.  Any system
that collects information in an automated fashion (news feeds, network data, seismic data,
etc.) might delete a bunch of old rows and insert new rows with greater frequency.

To tell you the truth, because of https://issues.apache.org/jira/browse/DERBY-4279, I've abandoned
this "pattern" almost completely in Derby.  Whenever I need to delete a bunch of rows ('bunch'
being tens of) and insert a new batch in the same transaction, because of 4279, I now have
to instead mark rows deleted (with an UPDATE statement) and then insert new rows.  Deletion
of marked rows is handled later via a scheduled deletion job.

That of course is an aside (4279), but I'm surprised you can actually run your benchmark without
running into it.


> 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, derby-5367-3a-update_field_by_field_preview.diff,
derby-5367-4a-fix_with_optimization_improved.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

        

Mime
View raw message