Hello, and welcome back to The Coder Cafe! The theme of the week is database fundamentals. In #16: ACID, we discussed transactions; let’s make sure the concept is crystal clear, as it’s one of the pillars in the domain of databases.
A transaction is an indivisible unit of work, allowing multiple operations to be treated as a single step.
In the ACID issue, we explored the following properties related to transactions:
Atomic: A transaction must be indivisible.
Consistent: A transaction must adhere to all data integrity rules1.
Isolated: A transaction must not be affected by another ongoing transaction.
Durable: Once a transaction is committed, its changes must be permanent and will not be lost.
It’s important to note that not all the transactions are fully ACID-compliant transactions. That’s the role of isolation levels, which determine how strictly the isolation property is enforced2. Only transactions with the highest isolation level are ACID-compliant transactions. Also, let’s note that when we refer to a transactional database, it means a database supports ACID-compliant transactions.
A typical transaction goes through the following steps:
Begin: The transaction starts.
Perform operations: Insert, update, delete, read—all these possible operations are done within the scope of the transaction.
Commit or rollback:
Commit: The transaction is committed, and the changes are permanently applied to the database (durable).
Rollback: The transaction is rolled back, undoing all the changes made during the transaction.
Any transaction (ACID or non-ACID transactions) must be atomic, meaning changes cannot be partially applied. However, in databases like PostgreSQL that support the concept of savepoint, there is some flexibility within a transaction. A savepoint acts as a checkpoint within a transaction, allowing us to rollback to a specific savepoint rather than the beginning of the transaction, which can be useful in the context of complex operations.
Why do we need transactions? Three main reasons:
Data integrity: Transactions should help protect the integrity of data (data integrity is the opposite of data corruption)—again, to some extent. Indeed, transactions do not guarantee rules that must be handled at the application level (e.g., the sum of customer balances should always be positive).
Concurrency control: In an environment with multiple users, transactions may be executed simultaneously. Tweaking the isolation level allows us to reason about what kind of undesired behaviors are allowed.
Error handling: We can ensure that in the event of an error, the database can revert to a previous stable state, preventing partial updates (atomicity).
Database transactions are a fundamental concept, especially in systems with a large number of concurrent users.
Tomorrow, we will explore the concept of isolation level.
Remember that the C in ACID isn’t solely a property of transactions but also about the application itself.
Tomorrow is about isolation levels.