This also triggers another problem that I am having in production. If these transactions are left in this state, Derby will build thousands of transaction files in the "log" database. If the server is not stopped and restarted soon after these start occurring, then it may take days for Derby to boot the database.
I assume that these are XA transactions that did not complete. I have Glassfish to timeout transactions after 15 minutes but this appears not to be working. 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:[email protected]] Sent: Wednesday, December 21, 2011 3:33 PM To: [email protected] 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,2b04000025fa2e6273756e332d7369743233372c7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c00) CSEM UserTransaction ACTIVE (1735812,468154) <null> 6789064593 (4871251,1512000025fa2e6273756e332d7369743233372c7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c00) CSEM UserTransaction ACTIVE <null> select max(csid) from ( select min(cs.id) as csid, min(cs.configuration_number) as csnum, cbe.id as cbid from --DERBY-PROPERTIES joinOrder=FIXED/n core_v1.configurable_hardware ch join core_v1.configuration_set cs on cs.configurable_hardware_id = ch.id join core_v1.configurationset_configurationbundle cscb on cscb.configurationset_id = cs.id join core_v1.configuration_bundle cb on cb.id = cscb.configurationbundle_id join pkg_9145e10g.configuration_bundle_9145e10g cbe on cbe.id = cb.id where ch.id = ? and cb.bundle_name = ? group by cbe.id ) as lcs 6789041545 (4871251,2306000025fa2e6273756e332d7369743233372c7365727665722c5033373030,73756e332d7369743233372c7365727665722c50333730302c00) CSEM UserTransaction ACTIVE <null> select max(csid) from ( select min(cs.id) as csid, min(cs.configuration_number) as csnum, cbe.id as cbid from --DERBY-PROPERTIES joinOrder=FIXED/n core_v1.configurable_hardware ch join core_v1.configuration_set cs on cs.configurable_hardware_id = ch.id join core_v1.configurationset_configurationbundle cscb on cscb.configurationset_id = cs.id join core_v1.configuration_bundle cb on cb.id = cscb.configurationbundle_id join pkg_9145e10g.configuration_bundle_9145e10g cbe on cbe.id = cb.id where ch.id = ? and cb.bundle_name = ? group by cbe.id ) as lcs 6789042080 <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.CONFIGURATIONSET_CONFIGURATIONBUNDLE t4, CORE_V1.CONFIGURATION_SET t3, PKG_9145E10G.PM_SCHEDULER_CONFIG_BUNDLE t2, PKG_9145E10G.CONFIGURATION_BUNDLE_9145E10G t1, CORE_V1.CONFIGURATION_BUNDLE t0 WHERE ((((t3.ID = CAST (? AS INTEGER )) AND (t0.BUNDLE_NAME = CAST (? AS VARCHAR(32672) ))) AND (((t2.ID = t0.ID) AND (t1.ID = t0.ID)) AND (t0.DTYPE = 'PM_SCHEDULER_CONFIG_BUNDLE_9145E10G'))) AND ((t4.CONFIGURATIONBUNDLE_ID = t0.ID) AND (t3.ID = t4.CONFIGURATIONSET_ID))) From: Katherine Marsden [mailto:[email protected]] Sent: Wednesday, December 21, 2011 2:46 PM To: [email protected] 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 connections are managed by Glassfish connection pool so I don't know where to set the traceDirectory and traceLevel properties. Can these be specified as properties 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, again 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 your application and connect with ij and run: select * from SYSCS_DIAG.TRANSACTION_TABLE ;
