Tracing SQL Transactions with Control Center
There are many different types of SQL transactions: pessimistic, optimistic; different isolation levels and so on. But, in general, the longer a transaction lasts, the more it affects the overall performance of the system.
Metrics cannot intercept all problems with your transactions, so you can look into the tracing section.
In the configuration section you can set the “scopes” of transactions you want to track. For example, you can track “cache reads” and “cache writes”, communication between nodes, transactions, and so on.
Each trace contains a group of spans. Span is a particular activity in the distributed system. You can look into each recorded transaction. If your transactions are long and there are big gaps in diagrams, but the actual operation happens at the very end, there is most likely something wrong with your code. To debug it, you can expand the root span and see general information about the trace.
Here are some common cases:
There is a method that parses multiple caches, and a different method for attaching results to an external account;
There is an annotation in the method that slows down the process;
There is a call to an external API, which can respond with delays if it is heavily loaded;
There is a validation that the external account is not null that leads lead to a rollback of the transaction on exceptions.
After you identify the cause for the delay, you can remove its cause and improve transaction speed.