Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-72688

Reindexing in jira + Postgres frequently fails with : "Cannot change transaction isolation level in the middle of a transaction"

XMLWordPrintable

    • 8.13
    • 59
    • Severity 2 - Major
    • 27
    • Hide
      Atlassian Update – 5 November 2024

      Dear Customers,

      We're happy to announce that this issue has been fixed in the 10.2.0 release. At this point, we cannot say if it will be possible to backport this fix to the LTS (9.4.x and 9.12.x). Please continue watching this ticket for future updates on the possibility of backporting.

      Summary of the problem

      The root cause of the issue was tracked to the commons-dbcp2 library. The details of the issue can be found at https://issues.apache.org/jira/browse/DBCP-585. In short, the library permitted concurrent access to a database connection, which was supported in the past by database vendors. However, that support was dropped at various points in time and can lead to potential complications. For Postgres specifically, the issue presented itself in Jira as JRASERVER-72688.

      We implemented a fix in the library that allows disabling of Connection instance-level JMX tracking while still maintaining other types of tracking, including tracking at the Connections level (see https://issues.apache.org/jira/browse/DBCP-585?focusedCommentId=17516575&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-17516575 for details). Consequently, we have utilized that flag in Jira to disable the problematic functionality.

      As a result, the issue is fixed and we plan to close the ticket with the release of version 10.2.0.

      New behavior after the change

      For the reasons described above, we no longer expose Connection instance-level metrics through JMX Exporter in Jira while still maintaining other types of tracking, including tracking at the Connections level.

      To envision the difference, compare the screenshots linked below from JavaMissionControl. Up until now, we exposed Connection-level metrics together with the Connection instance-level metrics. We no longer expose the latter while still maintaining all the information from Connection-level metrics.

      If you need to restore the previous behavior, you can use the parameter -Ddbcp.registerConnectionMBean=true to override those settings. However, it's important to note that this workaround is not recommended, as it may bring back symptoms fixed in JRASERVER-72688 or cause other unknown problems resulting from the thread races in the underlying library (see link for details).

      if the absence of the Connection instance-level metrics breaks your monitoring solution, please consider raising a separate ticket.

      Best regards

      Jacek Foremski

      Jira DC Principal Software Engineer

      Show
      Atlassian Update – 5 November 2024 Dear Customers, We're happy to announce that this issue has been fixed in the 10.2.0  release. At this point, we cannot say if it will be possible to backport this fix to the LTS (9.4.x and 9.12.x). Please continue watching this ticket for future updates on the possibility of backporting. Summary of the problem The root cause of the issue was tracked to the commons-dbcp2 library. The details of the issue can be found at https://issues.apache.org/jira/browse/DBCP-585 . In short, the library permitted concurrent access to a database connection, which was supported in the past by database vendors. However, that support was dropped at various points in time and can lead to potential complications. For Postgres specifically, the issue presented itself in Jira as JRASERVER-72688 . We implemented a fix in the library that allows disabling of Connection instance-level JMX tracking while still maintaining other types of tracking, including tracking at the Connections level (see https://issues.apache.org/jira/browse/DBCP-585?focusedCommentId=17516575&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-17516575 for details). Consequently, we have utilized that flag in Jira to disable the problematic functionality. As a result, the issue is fixed and we plan to close the ticket with the release of version 10.2.0 . New behavior after the change For the reasons described above, we no longer expose Connection instance-level metrics through JMX Exporter in Jira while still maintaining other types of tracking, including tracking at the Connections level. To envision the difference, compare the screenshots linked below from JavaMissionControl. Up until now, we exposed Connection-level metrics together with the Connection instance-level metrics . We no longer expose the latter while still maintaining all the information from Connection-level metrics . If you need to restore the previous behavior, you can use the parameter -Ddbcp.registerConnectionMBean=true to override those settings. However, it's important to note that this workaround is not recommended, as it may bring back symptoms fixed in JRASERVER-72688 or cause other unknown problems resulting from the thread races in the underlying library (see link  for details). if the absence of the Connection instance-level metrics breaks your monitoring solution, please consider raising a separate ticket. Best regards Jacek Foremski Jira DC Principal Software Engineer

      Issue Summary

      While reindexing Jira(which is configured with Postgres mainly), it fails with : "Cannot change transaction isolation level in the middle of a transaction", somewhere at the end of whole process.

      Steps to Reproduce

      • Install Jira 8.13 and above with Postgres.
      • Populate set of data on this environment or restore back up from existing reproducible environment.
      • Re-index.

      Expected Results

      Reindex should succeed.

      Actual Results

      It fails with following error:

      Caused by: com.atlassian.jira.transaction.TransactionRuntimeException: org.ofbiz.core.entity.GenericTransactionException: Error occurred while starting transaction. (Cannot change transaction isolation level in the middle of a transaction.)
	at com.atlassian.jira.transaction.TransactionSupportImpl.beginTxn(TransactionSupportImpl.java:54)
	at com.atlassian.jira.transaction.TransactionSupportImpl.startedTransaction(TransactionSupportImpl.java:46)
	at com.atlassian.jira.transaction.TransactionSupportImpl.begin(TransactionSupportImpl.java:31)
[....clipped....]
	at com.atlassian.jira.versioning.EntityVersioningManagerImpl.getRelatedCommentVersions(EntityVersioningManagerImpl.java:137)
[....clipped....]
Caused by: org.ofbiz.core.entity.GenericTransactionException: Error occurred while starting transaction. (Cannot change transaction isolation level in the middle of a transaction.)
	at org.ofbiz.core.entity.TransactionUtil.beginLocalTransaction(TransactionUtil.java:296)
	at com.atlassian.core.ofbiz.util.CoreTransactionUtil.begin(CoreTransactionUtil.java:54)
	at com.atlassian.jira.transaction.TransactionSupportImpl.beginTxn(TransactionSupportImpl.java:52)
	... 22 more
Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
	at org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:573)
	at org.apache.commons.dbcp2.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:573)
	at org.ofbiz.core.entity.jdbc.interceptors.connection.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:93)
	at com.atlassian.jira.ofbiz.sql.ConnectionWrapper.setTransactionIsolation(ConnectionWrapper.java:112)
	at org.ofbiz.core.entity.TransactionUtil.beginLocalTransaction(TransactionUtil.java:289)

      Workaround

      The issues is triggered by JMX queries to MBeans with naming pattern "com.atlassian.jira:connectionpool=connections,connection=X,name=BasicDataSource", where "X" changes with each new connection creation. This behaviour is due to DBCP-585. In majority of cases, we notice that this query is initiated via "Prometheus JMX Exporter" as it queries all MBeans with default configuration. This exporter may be configured via command line, or may be shipped inside the plugin Prometheus Experter for Jira. Removing exporter and restarting the server resolves the issue.

      If unable to remove Prometheus another work around would be adding the startup parameter -Ddbcp.jmx=false to the setenv.sh script then restart Jira.

          Form Name

            [JRASERVER-72688] Reindexing in jira + Postgres frequently fails with : "Cannot change transaction isolation level in the middle of a transaction"

            No work has yet been logged on this issue.

              38063008c24c Jacek Foremski
              vpandey2@atlassian.com Vikas Pandey (Inactive)
              Affected customers:
              12 This affects my team
              Watchers:
              34 Start watching this issue

                Created:
                Updated:
                Resolved: