Rollbacks in the trace

A transaction that ends in a rollback undoes what it did. Sometimes this is expected, such as the routine of a connection pool; sometimes it reveals work lost after an error. The trace itself tells which of the two cases it is.

Symptoms

In the Diagnosis, the finding n rollbacks (ROLLBACK), or 1 rollback, appears, with the text Rolled-back work also costs time; check whether it is expected. and the View summary link. The severity is low, with no percentage of the total time.

The finding appears when the trace has at least one XCTEND line with rlbk=1, which marks the end of a transaction by rollback. In the Summary, the Distinct statements card shows, at the bottom, how many commits and rollbacks the session did.

Cause

• The application rolls back the transaction after an error.
• The connection pool or the framework does a rollback when returning the connection or at the end of each request, often without having changed anything.
• A routine simulates or tests an operation and discards the result.

The cost depends on what the transaction changed: the database undoes each change with the undo. A transaction that only read ends with nothing to undo, and its XCTEND line has rd_only=1.

Resolution

1. Compare commits and rollbacks in the Summary.
2. Count in the trace the rollbacks of transactions that changed data (rd_only=0) and those of transactions that only read (rd_only=1):

   grep -c "XCTEND rlbk=1, rd_only=0" arquivo.trc
   grep -c "XCTEND rlbk=1, rd_only=1" arquivo.trc

   Result: added together, the two numbers give the finding's total of rollbacks.

3. List the lines of the rollbacks that undid changes; the number before the colon is the trace line:

   grep -n "XCTEND rlbk=1, rd_only=0" arquivo.trc | head

4. Read the excerpt of the trace before each one. Replace 1000 with the line of the rollback:

   head -n 1000 arquivo.trc | tail -n 40

5. If there is an ERROR line before the rollback, fix the error: see Oracle errors in the trace.
6. If there is no error, confirm with whoever maintains the application whether the rollback is part of the business rule.

Rollbacks with only rd_only=1 are usually a pool or framework routine and cost little. Each one is still a request to the database: if the round trips weigh on the time, see session waiting for the application.

How to confirm

After the fix, generate the trace of the same operation and compare the number of rollbacks in the Summary and the count of rd_only=0 in the trace. When the cause was an error, it also disappears from the Errors section.

In the report

• Summary: the Distinct statements card, with commits and rollbacks at the bottom. In the sample, the session has no rollback (summary).
• Diagnosis: the finding and the View summary link (Diagnosis).
• Errors: the errors that usually come before the rollback. The section only appears when the trace has an error (errors).
• Response time profile: the rollback of a transaction that changed data also waits on log file sync (redo write on commit).