Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-729

Read-only transaction with auto-commit=false not committed when using Master/slave or Aurora configuration

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 2.4.0, 2.4.1, 2.4.2, 2.4.3
    • 2.4.4
    • aurora, Failover
    • None

    Description

      Setup

      • Aurora cluster with
        Master with READ_COMMITTED isolation level
        Read replica (uses REPEATABLE_READ isolation by default in Aurora)
      • We set auto-commit to false on the Hikari connection pool
      • We use Spring with @Transactional(readOnly=true)

        @Transactional(readOnly=true)
        public String getCount() {
            return jdbcTemplate.executeQuery("SELECT count(*) FROM some_table", ...);
        }
        

      Issue:
      When we execute the getCount method multiple times we always get the same count, even though items are added to the some_table between invocations of getCount.

      Findings
      This is caused by not performing a commit after the invocation of getCount.
      In the MariaDbConnection.commit method the protocol.inTransaction() returns false.
      So the st.execute("COMMIT") is never actually invoked, although Spring tries to commit the transaction.

      Since Aurora read instances are always REPEATABLE_READ, and we use auto-commit false, the transaction will be kept alive without a COMMIT, which explains the stale date.

      When we use driver version 2.3.0 we don't see this issue since protocol.inTransaction() correctly indicates the transaction is active, and a commit occurs at the end of the getCount method.
      Starting from version 2.4.0 we see this erroneous behaviour.

      We have currently reverted back to version 2.3.0.

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            lennehendrickx Lenne
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.