Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 9090 invoked from network); 6 Mar 2009 16:15:03 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 6 Mar 2009 16:15:03 -0000 Received: (qmail 44879 invoked by uid 500); 6 Mar 2009 16:15:03 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 44863 invoked by uid 500); 6 Mar 2009 16:15:03 -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 44854 invoked by uid 99); 6 Mar 2009 16:15:03 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 06 Mar 2009 08:15:03 -0800 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 06 Mar 2009 16:15:00 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 4DED02388876; Fri, 6 Mar 2009 16:14:39 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r750955 - in /db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests: master/autoincrement.out tests/lang/autoincrement.sql Date: Fri, 06 Mar 2009 16:14:39 -0000 To: derby-commits@db.apache.org From: kmarsden@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20090306161439.4DED02388876@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kmarsden Date: Fri Mar 6 16:14:38 2009 New Revision: 750955 URL: http://svn.apache.org/viewvc?rev=750955&view=rev Log: DERBY-967 lang/autoincrement.sql intermittently fails on SunOS-5.10_i86 backport revision 530516 to 10.2 to avoid intermittent failure in the nightlies Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?rev=750955&r1=750954&r2=750955&view=diff ============================================================================== --- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (original) +++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out Fri Mar 6 16:14:38 2009 @@ -17,8 +17,25 @@ -- Adding new testcases for DB2 syntax "GENERATED ALWAYS AS IDENTITY" -- We don't enhance "ALTER TABLE MODIFY COLUMN" yet: DB2 uses "ALTER TABLE ALTER COLUMN..." -- try generated values with all types. --- Cloudscape specific syntax for the autoincrement clause can be found in store/bug3498.sql -create table ai_zero (i int, a_zero int generated always as identity); +-- use query on lock table that only looks at locks held by user transactions, +-- to avoid picking up locks by background threads. +create view lock_table as +select + cast(username as char(8)) as username, + cast(t.type as char(8)) as trantype, + cast(l.type as char(8)) as type, + cast(lockcount as char(3)) as cnt, + mode, + cast(tablename as char(12)) as tabname, + state, + status +from + syscs_diag.lock_table l right outer join syscs_diag.transaction_table t + on l.xid = t.xid +where + t.type='UserTransaction' and l.lockcount is not null; +0 rows inserted/updated/deleted +ij> create table ai_zero (i int, a_zero int generated always as identity); 0 rows inserted/updated/deleted ij> create table ai_one (i int, a_one smallint generated always as identity); 0 rows inserted/updated/deleted @@ -743,19 +760,19 @@ 1 |0 2 |1 ij> -- should see only locks on t1, no locks on system catalogs. -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; -TYPE |TABLENAME |MODE -------------------------------------------------------------------------------------------------------------------------------------------- -ROW |T1 |X -ROW |T1 |X -TABLE|T1 |IX +select * from lock_table order by tabname, type desc, mode, cnt; +USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS +---------------------------------------------------------------- +APP |UserTran|TABLE |2 |IX |T1 |GRANT|ACTIVE +APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE +APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE ij> delete from t1; 2 rows inserted/updated/deleted ij> commit; ij> -- locks should be gone now. -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; -TYPE |TABLENAME |MODE -------------------------------------------------------------------------------------------------------------------------------------------- +select * from lock_table order by tabname, type desc, mode, cnt; +USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS +---------------------------------------------------------------- ij> set isolation serializable; 0 rows inserted/updated/deleted ij> -- this will get a share lock on syscolumns @@ -764,20 +781,20 @@ COLUMNNAME |AUTOINCREMENTVALUE ----------------------------------------------------------------------------------------------------------------------------------------------------- YYY |2 -ij> select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; -TYPE |TABLENAME |MODE -------------------------------------------------------------------------------------------------------------------------------------------- -TABLE|SYSCOLUMNS |S +ij> select * from lock_table order by tabname, type desc, mode, cnt; +USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS +---------------------------------------------------------------- +APP |UserTran|TABLE |1 |S |SYSCOLUMNS |GRANT|ACTIVE ij> insert into t1 (x) values (3); 1 row inserted/updated/deleted -ij> select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; -TYPE |TABLENAME |MODE -------------------------------------------------------------------------------------------------------------------------------------------- -ROW |SYSCOLUMNS |X -TABLE|SYSCOLUMNS |IX -TABLE|SYSCOLUMNS |S -ROW |T1 |X -TABLE|T1 |IX +ij> select * from lock_table order by tabname, type desc, mode, cnt; +USERNAME|TRANTYPE|TYPE |CNT |MODE|TABNAME |STATE|STATUS +---------------------------------------------------------------- +APP |UserTran|TABLE |1 |IX |SYSCOLUMNS |GRANT|ACTIVE +APP |UserTran|TABLE |1 |S |SYSCOLUMNS |GRANT|ACTIVE +APP |UserTran|ROW |2 |X |SYSCOLUMNS |GRANT|ACTIVE +APP |UserTran|TABLE |1 |IX |T1 |GRANT|ACTIVE +APP |UserTran|ROW |1 |X |T1 |GRANT|ACTIVE ij> commit; ij> -- try using default keyword with ai. drop table t1; Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?rev=750955&r1=750954&r2=750955&view=diff ============================================================================== --- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (original) +++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Fri Mar 6 16:14:38 2009 @@ -17,7 +17,24 @@ -- Adding new testcases for DB2 syntax "GENERATED ALWAYS AS IDENTITY" -- We don't enhance "ALTER TABLE MODIFY COLUMN" yet: DB2 uses "ALTER TABLE ALTER COLUMN..." -- try generated values with all types. --- Cloudscape specific syntax for the autoincrement clause can be found in store/bug3498.sql + +-- use query on lock table that only looks at locks held by user transactions, +-- to avoid picking up locks by background threads. +create view lock_table as +select + cast(username as char(8)) as username, + cast(t.type as char(8)) as trantype, + cast(l.type as char(8)) as type, + cast(lockcount as char(3)) as cnt, + mode, + cast(tablename as char(12)) as tabname, + state, + status +from + syscs_diag.lock_table l right outer join syscs_diag.transaction_table t + on l.xid = t.xid +where + t.type='UserTransaction' and l.lockcount is not null; create table ai_zero (i int, a_zero int generated always as identity); create table ai_one (i int, a_one smallint generated always as identity); @@ -417,24 +434,24 @@ select * from t1; -- should see only locks on t1, no locks on system catalogs. -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; +select * from lock_table order by tabname, type desc, mode, cnt; delete from t1; commit; -- locks should be gone now. -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; +select * from lock_table order by tabname, type desc, mode, cnt; set isolation serializable; -- this will get a share lock on syscolumns select columnname, autoincrementvalue from sys.syscolumns where columnname = 'YYY'; -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; +select * from lock_table order by tabname, type desc, mode, cnt; insert into t1 (x) values (3); -select l.type, l.tablename, l.mode from syscs_diag.lock_table l order by tablename, type; +select * from lock_table order by tabname, type desc, mode, cnt; commit; -- try using default keyword with ai.