[SCB-829] An index on TxEvent (globalTxId) should be added

• 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: easyfix
• Environment: postgres and mysql
• Flags: Patch
• Assignee: FuChenGeng
• Reporter: FuChenGeng
• Created: 09/Aug/18 16:01
• Updated: 10/Aug/18 02:22
• Resolved: 10/Aug/18 02:22

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 ")

 

Issue Links

• links to: GitHub Pull Request #246