In simple words, a transaction or a unit of work is a set of database operations that we want to treat as "a whole." It has to either happen completely or not at all.
To ensure the correctness of a transaction, a database must be atomic, consistent, isolated, and durable. These four properties are commonly known under the acronym ACID.
More often, but not always, a transaction is made of multiple SQL statements. The atomic property states that all the statements must either be complete entirely or have no effect whatsoever. No partial execution should be permitted. The idea is that a transaction must always leave the database in a consistent state. This leads us to the second property.
From the previous explanation, we understand that the transaction must only bring the database from one valid state to another. This property ensures that there are no database constraints violations.
The isolation property guarantees that the uncommitted state changes are not visible or do not affect other concurrent transactions.
This property states that a committed transaction must permanently change the state of the database, even in case of a system failure like a power outage or crash. This implies that a successful transaction must always be recorded in non-volatile memory and/or a persisted transaction log.
A typical example to understand transactions is bank transfers.
Let's say we have two clients in our database, Sarah and John. The first one wants to transfer an amount of 20 dollars to the second client. Here's the process so far:
But the problem is that both of these two operations must be fully completed (commit) or not at all (rollback).
Let's make sure our database works properly:
SELECT * FROM client;
Now we'll proceed with our operations above.
The first operation looks like this:
UPDATE client SET amount = amount - 20 WHERE id = 1; SELECT * FROM client
Let's implement the second operation:
UPDATE client SET amount = amount + 20 WHERE id = 2; SELECT * FROM client
The operation is completed. We run two separate operations in this process. The best thing to do is to run both of these as ONE operation. This leads us to transactions.
BEGIN TRANSACTION; -- <-- transaction begins here -- decrease first UPDATE client SET amount = amount - 20 WHERE id = 1; -- than increase UPDATE client SET amount = amount + 20 WHERE id = 2; COMMIT TRANSACTION; -- <-- transaction ends here SELECT * FROM client
View all Courses