[
https://issues.apache.org/jira/browse/SCB-885?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16602515#comment-16602515
]
ASF GitHub Bot commented on SCB-885:
------------------------------------
WillemJiang closed pull request #287: SCB-885 saga alpha event scanner delete
duplicate events sql optimization
URL: https://github.com/apache/incubator-servicecomb-saga/pull/287
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git
a/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/SpringTxEventRepository.java
b/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/SpringTxEventRepository.java
index 94ace76f..0394f829 100644
---
a/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/SpringTxEventRepository.java
+++
b/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/SpringTxEventRepository.java
@@ -82,6 +82,7 @@ public void save(TxEvent event) {
@Override
public void deleteDuplicateEvents(String type) {
- eventRepo.deleteByType(type);
+ eventRepo.findDuplicateEventsByType(type).forEach((txEvent) ->eventRepo.
+ deleteBySurrogateId(txEvent.id()));
}
}
diff --git
a/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/TxEventEnvelopeRepository.java
b/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/TxEventEnvelopeRepository.java
index fdf920f3..89808c90 100644
---
a/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/TxEventEnvelopeRepository.java
+++
b/alpha/alpha-server/src/main/java/org/apache/servicecomb/saga/alpha/server/TxEventEnvelopeRepository.java
@@ -114,13 +114,18 @@
Optional<TxEvent> findFirstByTypeAndSurrogateIdGreaterThan(String type, long
surrogateId);
+ @Query("SELECT t FROM TxEvent t "
+ + "WHERE t.type = ?1 AND EXISTS ( "
+ + " SELECT t1.surrogateId"
+ + " FROM TxEvent t1 "
+ + " WHERE t1.globalTxId = t.globalTxId "
+ + " AND t1.localTxId = t.localTxId "
+ + " AND t1.type = t.type "
+ + " AND t1.surrogateId > t.surrogateId )")
+ List<TxEvent> findDuplicateEventsByType(String type);
+
@Transactional
@Modifying(clearAutomatically = true)
- @Query("DELETE FROM TxEvent t "
- + "WHERE t.type = ?1 AND t.surrogateId NOT IN ("
- + " SELECT MAX(t1.surrogateId) FROM TxEvent t1 "
- + " WHERE t1.type = ?1 "
- + " GROUP BY t1.globalTxId"
- + ")")
- void deleteByType(String type);
+ @Query("DELETE FROM TxEvent WHERE surrogateId = ?1 ")
+ void deleteBySurrogateId(Long surrogateId);
}
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
> saga alpha event scanner delete duplicate events sql optimization
> -----------------------------------------------------------------
>
> Key: SCB-885
> URL: https://issues.apache.org/jira/browse/SCB-885
> Project: Apache ServiceComb
> Issue Type: Improvement
> Components: Saga
> Affects Versions: saga-0.2.0
> Reporter: FuChenGeng
> Assignee: FuChenGeng
> Priority: Minor
> Fix For: saga-0.3.0
>
>
> The count of txevents is about 100m. and this sql spend too much time so that
> the event scanner is blocked.
> [SQL]DELETE FROM TxEvent t WHERE t.type = 'SagaEndedEvent' AND t.surrogateId
> NOT IN ( SELECT MAX(t1.surrogateId) FROM TxEvent t1 WHERE t1.type =
> 'SagaEndedEvent' GROUP BY t1.globalTxId);
>
> 时间: 1308.376s
>
> 受影响的行: 5
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
