Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 11 Apr 2012 13:29:16 +0000 (UTC)
From: "Rick Hillegas (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <20154263.12156.1334150956886.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1354085544.6625.1320674331736.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-5493) Same value returned by successive
 calls to a sequence generator.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Rick Hillegas commented on DERBY-5493:
--------------------------------------

Thanks for your comments and your offer to review this patch, Mike. Some comments inline...


...

>Did you understand why this approach fixes DERBY-5494 vs the subtransaction approach?

I don't understand why the subtransaction approach fails. I have attached a revised repro to DERBY-5494. That shows the behavior in the current trunk without this patch. The sequence generator durably commits a change to SYSSEQUENCES, which can be seen by another thread. The system-crash reverses this supposedly durable change. The same behavior can be seen in 10.8.2.2 (except that the size of the pre-allocation range is shorter so the second thread sees a slightly different value before the crash).

I decided to try a dedicated transaction because I knew, from the ghost conglomerate experiment, that that approach fixed DERBY-5494.

The only variable that I can see is the use of a subtransaction vs the use of an ordinary transaction. You are the expert in the implementation and use of subtransactions. Do you have any theories about what is going on?

>In the normal sub transaction case I was thinking that it should wait on locks and return too much contention on a lock timeout, to take care of a expected very short lock waits if 2 threads are trying to update the sequence at same time, but maybe the 1 transaction per sequence makes this impossible to happen. 

The 1 transaction per sequence should make this impossible for the cases which I thought we wanted to support. Waiting for the lock would support the following additional use-cases, but I thought that we agreed they were not worth supporting:

a) Dropping a sequence and then rolling back that transaction.

b) Scans of SYSSEQUENCES by the application.

>What is the expected behavior of creating a sequence, with respect to autocommit and locking? Is the create done in the nested tr nsaction and autocommitted?

Nothing has changed in the CREATE SEQUENCE logic. That work happens in an ordinary user transaction, just as it did in 10.8. At the end of this comment, I attach a script which shows that if you create a sequence and a table but don't commit that work, then another thread will timeout trying to use those objects.

This is the behavior which I thought we agreed: DDL happens in ordinary user transactions. It is just the NEXT VALUE FOR which happens in an unusual transaction. Note that SYSCS_PEEK_AT_SEQUENCE doesn't actually do any transactional work, it just looks at an in-memory value via a synchronized method.

>A comment I meant to make before, is that DERBY-5495 requires crash testing, and now there is a framework to do that in junit tests - see store/OCRecoveryTest.java, basically there is a routine to fork out a process to run a java test procedure and then you just exit which will run through the "unclean" shutdown and next test connects running through whatever recovery will do on that unclean shutdown.

Great! I will look into making a proper JUnit test out of java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_5494.sh.

Thanks,
-Rick

------------------

Here is the script which demonstrates the behavior of uncommitted ddl:

connect 'jdbc:derby:memory:db;create=true' as dbo;

call syscs_util.syscs_set_database_property( 'derby.locks.waitTimeout', '2' );

autocommit off;

create sequence foo;
create table t( a int );

connect 'jdbc:derby:memory:db' as otheruser;

-- times out waiting for the other thread's ddl to commit
values next value for foo;
select * from t;

set connection dbo;

commit;

set connection otheruser;

-- succeeds
values next value for foo;
select * from t;
                
> Same value returned by successive calls to a sequence generator.
> ----------------------------------------------------------------
>
>                 Key: DERBY-5493
>                 URL: https://issues.apache.org/jira/browse/DERBY-5493
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.9.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>              Labels: derby_triage10_9
>         Attachments: derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff, derby-5493-01-ad-simplerApproach.diff
>
>
> The following script shows the same value being returned from a sequence generator by two successive NEXT VALUE FOR calls. Thanks to Knut for finding this:
> connect 'jdbc:derby:memory:db;create=true';
> create table t (x int);
> create sequence s;
> autocommit off;
> select count(*) from sys.syssequences with rs;
> values next value for s;
> drop table t;
> rollback;
> -- same value as previous call
> values next value for s; 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira