Oracle SQL - Transaction Transaction Processing


All DML changes ( INSERT, UPDATE, DELETE, and MERGE) initially get a "pending" status.

Your session can see the changed rows, but other database users will see the original data when they query the same table rows.

As long as your changes are in this pending state, other database users cannot change those rows, until you confirm or abandon your pending changes.

The SQL command to confirm pending changes to the database is COMMIT, and the command to abandon them is ROLLBACK.

COMMIT and ROLLBACK end the current transaction and start a new one.

A transaction is a logical unit of work.

A transaction is a set of changes that will succeed or fail as a whole.

For example, account transfer transactions in a banking system normally consist of at least two updates: a debit to account A and a credit to account B.

It makes sense to COMMIT after each debit/credit combination, and not in between each update.