Uploaded image for project: 'Apache ServiceComb'
  1. Apache ServiceComb
  2. SCB-829

An index on TxEvent (globalTxId) should be added

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: pack-0.3.0
    • Fix Version/s: pack-0.3.0
    • Component/s: Saga
    • Labels:
    • Environment:
      postgres and mysql
    • Flags:
      Patch

      Description

      When the amount of data in the TxEvent table is large, the select SQL statement of method(

      findByEventGlobalTxIdAndEventType

      )  is executed very slowly. Here, an index on globalTxId should be added.

       

      Here is the SQL statement. it will be executed 3 times in a transaction with 2 subtransactions.

      @Query("SELECT DISTINCT new org.apache.servicecomb.saga.alpha.core.TxEvent("
      + "t.serviceName, t.instanceId, t.globalTxId, t.localTxId, t.parentTxId, "
      + "t.type, t.compensationMethod, t.payloads "
      + ") FROM TxEvent t "
      + "WHERE t.globalTxId = ?1 AND t.type = ?2 "
      + " AND ( SELECT MIN(t1.retries) FROM TxEvent t1 "
      + " WHERE t1.globalTxId = t.globalTxId "
      + " AND t1.localTxId = t.localTxId "
      + " AND t1.type IN ('TxStartedEvent', 'SagaStartedEvent') ) = 0 ")

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                OLiuGian FuChenGeng
                Reporter:
                OLiuGian FuChenGeng
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 10m
                  10m
                  Remaining:
                  Remaining Estimate - 10m
                  10m
                  Logged:
                  Time Spent - Not Specified
                  Not Specified