This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

Transaction describes database operations bound by integrity rules, which update database states while maintaining consistency. More specifically, a transaction is a sequence of operations that has to be atomic, consistent, isolated, and durable.¹

Transaction management allows conflict-free simultaneous work by multiple users.

Transaction is a unit of work.

The Standard Work Unit

The standard work unit then states "in order to achieve good and consistent online transaction time, each online transaction running in the system needs to be small and uniform in size."²

Implementations

PostgreSQL

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. See Section 43.8 for information on managing transactions in PL/pgSQL. Also, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 43.6.8.³

In PL/pgSQL in PostgreSQL, whenever a COMMIT occurs in a procedure, the current transaction is ended and a new transaction is started automatically.⁴

Oracle Database

PL/SQL in Oracle is similar to PL/pgSQL in PostgreSQL in this case, however, there is still an observable difference in the way new transactions are created. When a COMMIT is performed, the current transaction is ended and a new transaction begins whenever the first SQL statement (particularly, DML commands like INSERT, UPDATE, DELETE, SELECT) is encountered.⁴

---

1. MEIER, Andreas; KAUFMANN, Michael. (2019) SQL & NoSQL Databases. Models, Languages, Consistency Options and Architectures for Big Data Management. Wiesbaden: Springer Vieweg. ISBN 978-3-658-24548-1 ↩︎

2. W.H. Inmon, Daniel Linstedt, Mary Levins (2019) Data Architecture. A Primer for the Data Scientist. Second Edition. Elsevier. ISBN 978-0-12-816916-2. (p.188) ↩︎

3. Structure of PL/pgSQL (2022) PostgreSQL Documentation. ↩︎

4. Oracle vs PostgreSQL – Transaction control statements (2022) MigOps Blog (defunct). ↩︎