Database transaction

A database transaction combines several steps into one unit. If the changes are committed, the database applies them together; if the transaction is aborted, they are discarded together. An error therefore does not leave only part of the intended change in place.

Change the assignment and its history together

In a fictional device management application, device-4 moves from project P-1 to P-2. The new assignment and a change history entry reside in the same database and must be saved together. The application starts a transaction with BEGIN. If both steps succeed, it confirms them with COMMIT; if a step fails, it discards this transaction’s changes with ROLLBACK.

What concurrent transactions can see and which conflicts can occur also depend on isolation and the accesses performed. Business rules still need to be implemented; the transaction boundary alone cannot prevent every incorrect assignment.

This local rollback cannot recall an email already sent. Coordination with other systems requires a separate solution. A Transactional Inbox specifically uses a shared database transaction to combine message processing with recording its deduplication identifier.

Sources

• PostgreSQL: Transactions