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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.4.0, 2.4.1, 2.4.2, 2.4.3
    • Fix Version/s: 2.4.4
    • Component/s: aurora, Failover
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: