GenericDaoBase.persist() leaves the caller's transaction unbalanced when an insert throws

Comience rastreando GenericDaoBase.persist() y el comportamiento de anidamiento y commit de TransactionLegacy en framework/db; después, inspeccione UsageManagerImpl.createHelperRecord() y createVolumeHelperEvent() como ruta de reproducción. Confirme que un fallo de inserción no pueda dejar desequilibrada la transacción del llamador ni hacer que su commit posterior no haga nada silenciosamente, mientras que los llamadores que gestionan EntityExistsException reciban el comportamiento de error previsto.

problem

Split out of #13399 at @DaanHoogland's request.

GenericDaoBase.persist() does not own a transaction, it joins the caller's via TransactionLegacy.currentTxn() and calls txn.start(), which pushes a START_TXN nesting level. On the SQLException path it never reaches txn.commit(), and there is no finally, so that nesting level is leaked:

final TransactionLegacy txn = TransactionLegacy.currentTxn();   // the CALLER's transaction
try {
    txn.start();                    // pushes a START_TXN nesting level
    ...
    pstmt.executeUpdate();          // throws
    ...
    txn.commit();                   // never reached
} catch (final SQLException e) {
    logger.error("DB Exception on: " + pstmt, e);
    handleEntityExistsException(e); // throws EntityExistsException
    throw new CloudRuntimeException("Unable to persist on DB, due to: " + e.getLocalizedMessage());
}
// no finally, the pushed nesting level is never released

Consequence: the caller's own commit() then finds the transaction unbalanced and silently no-ops, logging only:

WARN [db.Transaction.Transaction] txn: Commit called when it is not a transaction:

(TransactionLegacy.commit() — if (!_txn) { LOGGER.warn(...); return false; })

Everything in that transaction is discarded while the caller believes it committed.

Why it matters beyond one call site: callers that deliberately catch EntityExistsException in order to log-and-continue cannot actually continue, because the enclosing transaction is already unrecoverable. UsageManagerImpl.createHelperRecord() is one such caller, and in #13399 this is what converts a single constraint violation into permanent usage-aggregation failure rather than one skipped record.

versions

Observed on CloudStack 4.22.1.0 (EL9 packages), MySQL 8.x / InnoDB.

This is a code-level defect in framework/db rather than an environment-specific one; the code path is not version-specific and hypervisor/storage/network are not relevant.

The steps to reproduce the bug

1. On 4.22.1.0 with the Usage Server enabled, deploy an instance. Its ROOT volume produces a VOLUME.CREATE usage event carrying vm_id.
2. UsageManagerImpl.createVolumeHelperEvent() performs two persist() calls sharing (volume_id, created); the second violates usage_volume's unique key (see #13399).
3. createHelperRecord() catches the resulting EntityExistsException and logs a warning, intending to continue.
4. Observe txn: Commit called when it is not a transaction shortly afterwards, and that the processed flags set for that batch of events in cloud_usage.usage_event were never committed.

Any caller that hits a constraint violation inside a transaction it owns should show the same behaviour, #13399 is simply a case where it happens on every VM deployment.

What to do about it?

Release the nesting level in a finally, and/or mark the transaction rollback-only so callers receive a real failure instead of a silent no-op.

Either way this needs someone familiar with TransactionLegacy's nesting semantics, since GenericDaoBase backs every DAO in the codebase. I'm raising it rather than proposing a patch.