Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 8C1E97C70 for ; Wed, 21 Dec 2011 20:41:30 +0000 (UTC) Received: (qmail 86179 invoked by uid 500); 21 Dec 2011 20:41:30 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 86158 invoked by uid 500); 21 Dec 2011 20:41:30 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 86151 invoked by uid 99); 21 Dec 2011 20:41:30 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 20:41:30 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [65.55.88.12] (HELO TX2EHSOBE004.bigfish.com) (65.55.88.12) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 20:41:25 +0000 Received: from mail84-tx2-R.bigfish.com (10.9.14.249) by TX2EHSOBE004.bigfish.com (10.9.40.24) with Microsoft SMTP Server id 14.1.225.23; Wed, 21 Dec 2011 20:40:54 +0000 Received: from mail84-tx2 (localhost [127.0.0.1]) by mail84-tx2-R.bigfish.com (Postfix) with ESMTP id 5BFCB520510 for ; Wed, 21 Dec 2011 20:40:58 +0000 (UTC) X-SpamScore: -5 X-BigFish: VPS-5(zzbb2dI9371Ic85fh98dKzz1202hzz8275bh8275dhz2dh2a8h668h839h61h) X-Spam-TCS-SCL: 0:0 X-Forefront-Antispam-Report: CIP:74.62.37.82;KIP:(null);UIP:(null);IPV:NLI;H:CPHUB1.canoga.com;RD:rrcs-74-62-37-82.west.biz.rr.com;EFVD:NLI Received: from mail84-tx2 (localhost.localdomain [127.0.0.1]) by mail84-tx2 (MessageSwitch) id 1324500057502035_13910; Wed, 21 Dec 2011 20:40:57 +0000 (UTC) Received: from TX2EHSMHS039.bigfish.com (unknown [10.9.14.235]) by mail84-tx2.bigfish.com (Postfix) with ESMTP id 68B3A1C0065 for ; Wed, 21 Dec 2011 20:40:57 +0000 (UTC) Received: from CPHUB1.canoga.com (74.62.37.82) by TX2EHSMHS039.bigfish.com (10.9.99.139) with Microsoft SMTP Server (TLS) id 14.1.225.23; Wed, 21 Dec 2011 20:40:51 +0000 Received: from CPHUB2.canoga.com (172.16.1.94) by CPHUB1.canoga.com (172.16.1.93) with Microsoft SMTP Server (TLS) id 8.2.213.0; Wed, 21 Dec 2011 12:42:18 -0800 Received: from vserver1.canoga.com ([169.254.2.87]) by CPHUB2.canoga.com ([172.16.1.94]) with mapi; Wed, 21 Dec 2011 12:42:18 -0800 From: "Bergquist, Brett" To: "derby-dev@db.apache.org" Date: Wed, 21 Dec 2011 12:40:56 -0800 Subject: RE: Problem with a deadlock with Derby 10.8.1.2 and Glassfish V2.1.1 Thread-Topic: Problem with a deadlock with Derby 10.8.1.2 and Glassfish V2.1.1 Thread-Index: AczAGWJBUpfxIgQyRX+Lidj1fIAxPAABfaFQAAA95CA= Message-ID: <97EB699F861AD841B5908C7CA9C9565601CC3A3958FC@VSERVER1.canoga.com> References: <97EB699F861AD841B5908C7CA9C9565601CC3A395890@VSERVER1.canoga.com> <4EF205B8.9040100@sbcglobal.net> <97EB699F861AD841B5908C7CA9C9565601CC3A3958A9@VSERVER1.canoga.com> <97EB699F861AD841B5908C7CA9C9565601CC3A3958EB@VSERVER1.canoga.com> <4EF2375C.6000305@sbcglobal.net> <97EB699F861AD841B5908C7CA9C9565601CC3A3958FA@VSERVER1.canoga.com> In-Reply-To: <97EB699F861AD841B5908C7CA9C9565601CC3A3958FA@VSERVER1.canoga.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US X-TM-AS-Product-Ver: SMEX-8.0.0.1307-6.500.1024-18592.006 X-TM-AS-Result: No--7.756300-0.000000-31 X-TM-AS-User-Approved-Sender: Yes X-TM-AS-User-Blocked-Sender: No Content-Type: multipart/alternative; boundary="_000_97EB699F861AD841B5908C7CA9C9565601CC3A3958FCVSERVER1can_" MIME-Version: 1.0 X-OriginatorOrg: canoga.com --_000_97EB699F861AD841B5908C7CA9C9565601CC3A3958FCVSERVER1can_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable This also triggers another problem that I am having in production. If thes= e transactions are left in this state, Derby will build thousands of transa= ction files in the "log" database. If the server is not stopped and restar= ted soon after these start occurring, then it may take days for Derby to bo= ot the database. I assume that these are XA transactions that did not complete. I have Glas= sfish to timeout transactions after 15 minutes but this appears not to be w= orking. I would like to set the "derby.jdbc.xaTransactionTimeout" property= but I am leery because of" https://issues.apache.org/jira/browse/DERBY-4109 From: Bergquist, Brett [mailto:BBergquist@canoga.com] Sent: Wednesday, December 21, 2011 3:33 PM To: derby-dev@db.apache.org Subject: RE: Problem with a deadlock with Derby 10.8.1.2 and Glassfish V2.1= .1 After shutting down the application server but leaving the database engine = running, it still has transactions: 6789036870 (4871251,2b04000025fa2e6273756e332d7369743233372c= 7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c= 00) CSEM UserTransaction ACTIVE (1735812,46= 8154) 6789064593 (4871251,1512000025fa2e6273756e332d7369743233372c= 7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c= 00) CSEM UserTransaction ACTIVE se= lect max(csid) from ( select min(cs.id) as csid, min(cs.configuration_numbe= r) as csnum, cbe.id as cbid from --DERBY-PROPERTIES joinOrder=3DFIXED/n cor= e_v1.configurable_hardware ch join core_v1.configuration_set cs on cs.confi= gurable_hardware_id =3D ch.id join core_v1.configurationset_configurationbu= ndle cscb on cscb.configurationset_id =3D cs.id join core_v1.configuration_= bundle cb on cb.id =3D cscb.configurationbundle_id join pkg_9145e10g.config= uration_bundle_9145e10g cbe on cbe.id =3D cb.id where ch.id =3D ? and cb.bu= ndle_name =3D ? group by cbe.id ) as lcs 6789041545 (4871251,2306000025fa2e6273756e332d7369743233372c= 7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c= 00) CSEM UserTransaction ACTIVE se= lect max(csid) from ( select min(cs.id) as csid, min(cs.configuration_numbe= r) as csnum, cbe.id as cbid from --DERBY-PROPERTIES joinOrder=3DFIXED/n cor= e_v1.configurable_hardware ch join core_v1.configuration_set cs on cs.confi= gurable_hardware_id =3D ch.id join core_v1.configurationset_configurationbu= ndle cscb on cscb.configurationset_id =3D cs.id join core_v1.configuration_= bundle cb on cb.id =3D cscb.configurationbundle_id join pkg_9145e10g.config= uration_bundle_9145e10g cbe on cbe.id =3D cb.id where ch.id =3D ? and cb.bu= ndle_name =3D ? group by cbe.id ) as lcs 6789042080 CSEM UserTransaction ACTIVE SELECT t0.ID, t0.DTYPE, t0.BUNDLE_NAME, t0.OPLOCK, t1.ID, t2.ID, t2.= PM_END_DELAY, t2.PM_BETWEEN_TIME, t2.PM_DIS_OVER_TIME, t2.PM_DIS_END_DELAY,= t2.PM_SCHEDULER_POLICY, t2.PM_SCHEDULER_STATE FROM CORE_V1.CONFIGURATIONSE= T_CONFIGURATIONBUNDLE t4, CORE_V1.CONFIGURATION_SET t3, PKG_9145E10G.PM_SCH= EDULER_CONFIG_BUNDLE t2, PKG_9145E10G.CONFIGURATION_BUNDLE_9145E10G t1, COR= E_V1.CONFIGURATION_BUNDLE t0 WHERE ((((t3.ID =3D CAST (? AS INTEGER )) AND = (t0.BUNDLE_NAME =3D CAST (? AS VARCHAR(32672) ))) AND (((t2.ID =3D t0.ID) A= ND (t1.ID =3D t0.ID)) AND (t0.DTYPE =3D 'PM_SCHEDULER_CONFIG_BUNDLE_9145E10= G'))) AND ((t4.CONFIGURATIONBUNDLE_ID =3D t0.ID) AND (t3.ID =3D t4.CONFIGUR= ATIONSET_ID))) From: Katherine Marsden [mailto:kmarsdenderby@sbcglobal.net] Sent: Wednesday, December 21, 2011 2:46 PM To: derby-dev@db.apache.org Subject: Re: Problem with a deadlock with Derby 10.8.1.2 and Glassfish V2.1= .1 On 12/21/2011 11:20 AM, Bergquist, Brett wrote: I'm having some trouble getting client side tracing to work. The connectio= ns are managed by Glassfish connection pool so I don't know where to set th= e traceDirectory and traceLevel properties. Can these be specified as pro= perties like the password, etc. They can be set on the connection URL or with undocumented system propert= ies, documented here #:) http://wiki.apache.org/db-derby/UndocumentedDerbyBehavior Looking at the info, again I am curious if there are corresponding server s= ide traces in the derby.log. Also it would be interesting to see if there are at this point any XA Trans= actions in need of recovery in the database. Just exit your application and connect with ij and run: select * from SYSCS_DIAG.TRANSACTION_TABLE ; --_000_97EB699F861AD841B5908C7CA9C9565601CC3A3958FCVSERVER1can_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

= This also triggers another problem that I am = having in production.  If these transactions are left in this state, D= erby will build thousands of transaction files in the “log” dat= abase.  If the server is not stopped and restarted soon after these st= art occurring, then it may take days for Derby to boot the database.

&nbs= p;

I ass= ume that these are XA transactions that did not complete.  I have Glas= sfish to timeout transactions after 15 minutes but this appears not to be w= orking.  I would like to set the “derby.jdbc.xaTransactionTimeou= t” property but I am leery because of”

 

https://issues.apache.org/jira/browse/DERBY-4109

=

 

 

 

From: Bergquist, Brett [mailto= :BBergquist@canoga.com]
Sent: Wednesday, December 21, 2011 3:33 = PM
To: derby-dev@db.apache.org
Subject: RE: Problem wit= h a deadlock with Derby 10.8.1.2 and Glassfish V2.1.1

=

 

= After shutting down the application server bu= t leaving the database engine running, it still has transactions:

 <= /o:p>

67890368= 70            &= nbsp;   (4871251,2b04000025fa2e6273756e332d7369743233372c73657276= 65722c5033373030,73756e332d7369743233372c7365727665722c50333730302c00) = ;            &n= bsp;  CSEM    UserTransaction    &n= bsp;          ACTIVE (1735812,= 468154)           &n= bsp; <null>

6789064593        &n= bsp;       (4871251,1512000025fa2e6273756e332= d7369743233372c7365727665722c5033373030,73756e332d7369743233372c73657276657= 22c50333730302c00)         &nb= sp;      CSEM    UserTransaction&nb= sp;            =   ACTIVE <null>   select max(csid) from ( select min(c= s.id) as csid, min(cs.configuration_number) as csnum, cbe.id as cbid from -= -DERBY-PROPERTIES joinOrder=3DFIXED/n core_v1.configurable_hardware ch join= core_v1.configuration_set cs on cs.configurable_hardware_id =3D ch.id join= core_v1.configurationset_configurationbundle cscb on cscb.configurationset= _id =3D cs.id join core_v1.configuration_bundle cb on cb.id =3D cscb.config= urationbundle_id join pkg_9145e10g.configuration_bundle_9145e10g cbe on cbe= .id =3D cb.id where ch.id =3D ? and cb.bundle_name =3D ? group by cbe.id ) = as lcs

6789041545          &= nbsp;     (4871251,2306000025fa2e6273756e332d7369743233= 372c7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730= 302c00)           &n= bsp;    CSEM    UserTransaction  &n= bsp;            ACTI= VE <null>   select max(csid) from ( select min(cs.id) as cs= id, min(cs.configuration_number) as csnum, cbe.id as cbid from --DERBY-PROP= ERTIES joinOrder=3DFIXED/n core_v1.configurable_hardware ch join core_v1.co= nfiguration_set cs on cs.configurable_hardware_id =3D ch.id join core_v1.co= nfigurationset_configurationbundle cscb on cscb.configurationset_id =3D cs.= id join core_v1.configuration_bundle cb on cb.id =3D cscb.configurationbund= le_id join pkg_9145e10g.configuration_bundle_9145e10g cbe on cbe.id =3D cb.= id where ch.id =3D ? and cb.bundle_name =3D ? group by cbe.id ) as lcs=

6789042= 080         <null>  = ; CSEM    UserTransaction      = ;         ACTIVE <null> =   SELECT t0.ID, t0.DTYPE, t0.BUNDLE_NAME, t0.OPLOCK, t1.ID, t2.ID, t2.= PM_END_DELAY, t2.PM_BETWEEN_TIME, t2.PM_DIS_OVER_TIME, t2.PM_DIS_END_DELAY,= t2.PM_SCHEDULER_POLICY, t2.PM_SCHEDULER_STATE FROM CORE_V1.CONFIGURATIONSE= T_CONFIGURATIONBUNDLE t4, CORE_V1.CONFIGURATION_SET t3, PKG_9145E10G.PM_SCH= EDULER_CONFIG_BUNDLE t2, PKG_9145E10G.CONFIGURATION_BUNDLE_9145E10G t1, COR= E_V1.CONFIGURATION_BUNDLE t0 WHERE ((((t3.ID =3D CAST (? AS INTEGER )) AND = (t0.BUNDLE_NAME =3D CAST (? AS VARCHAR(32672) ))) AND (((t2.ID =3D t0.ID) A= ND (t1.ID =3D t0.ID)) AND (t0.DTYPE =3D 'PM_SCHEDULER_CONFIG_BUNDLE_9145E10= G'))) AND ((t4.CONFIGURATIONBUNDLE_ID =3D t0.ID) AND (t3.ID =3D t4.CONFIGUR= ATIONSET_ID)))

 

 

 

 

From: Katherine Marsden [mailt= o:kmarsdenderby@sbcglobal.net]
Sent: Wednesday, December 21, 201= 1 2:46 PM
To: derby-dev@db.apache.org
Subject: Re: Prob= lem with a deadlock with Derby 10.8.1.2 and Glassfish V2.1.1

 

On 12/21/2011 11:20 AM, Bergquist, Brett wrote:

I’m having some trouble= getting client side tracing to work.  The connections are managed by = Glassfish connection pool so I don’t know where to set the traceDirec= tory and traceLevel properties.   Can these be specified as prope= rties like the password, etc.

They  can be set on the connection URL or = ; with undocumented system properties, documented here #:)
http://wiki.apache= .org/db-derby/UndocumentedDerbyBehavior

Looking at the info, aga= in I am curious if there are corresponding server side traces in the derby.= log.
Also it would be interesting to see if there are at this point any = XA Transactions in need of recovery in the database.
Just  exit you= r application and connect  with ij and run:

select * from SYSCS= _DIAG.TRANSACTION_TABLE ;

= --_000_97EB699F861AD841B5908C7CA9C9565601CC3A3958FCVSERVER1can_--