Transaction Processing: When does a rollback occur?

Question

Recently this question was posted about the definition of what a transaction is in a general context. A common answer to this question was that a transaction should be an atomic unit of work

My question relates to this atomicity (i think) I often see explicit calls to ROLLBACK in SQL stored procedures.

Is it generally a common requirement of transaction processing systems that rollbacks be explicitly called for?

Does a rollback occur automatically if some error occurs when committing?

Answer 1

In some cases, a rollback will occur automatically due to a trigger or a constraint violation. In others (like you've seen), the stored procedure itself does the rollback. Aiden is right that auto-commit varies.

Answer 2

In TP systems, rollback can occur based on:

• an explicit request, like a call to ROLLBACK or similar
• any uncaught exception or error. These might include:
  • loss of communication with a participant (in a distributed transaction)
  • an invalid or out-of-range value or parameter
  • a timeout, due to inability to acquire a lock for example, or user delay.
• in a two-phased commit distributed transaction, a failure of one of the participants to vote to commit

A rollback need not occur as you say "when committing", by which I guess you mean "when attempting to commit." A transaction can rollback at any time after inception.