//
When interacting with a database, you rarely execute a single SQL command in isolation. Usually, a business process requires multiple commands to be executed as a group.
A Transaction is a logical unit of processing in a DBMS that entails one or more database access operations. Usually, a transaction is initiated by a user program, where it is demarcated by statements of the form BEGIN TRANSACTION and COMMIT.
Imagine transferring $100 from Account A to Account B. This involves two steps:
UPDATE Accounts SET balance = balance - 100 WHERE id = 'A';UPDATE Accounts SET balance = balance + 100 WHERE id = 'B';What happens if the server loses power exactly after Step 1 finishes, but before Step 2 starts? Account A lost $100, but Account B never got it. The money evaporated.
To prevent this catastrophic failure, the DBMS groups both steps into a single Transaction. The DBMS guarantees that either both steps happen, or neither happens.
To ensure the integrity of the data, the database system maintains the following four properties, famously known by the acronym ACID:
Either all operations of the transaction are reflected properly in the database, or none are. If a transaction fails halfway through, the database must automatically Rollback all changes made so far, returning the system to its previous state.
Execution of a transaction in isolation (with no other transaction executing concurrently) must preserve the consistency of the database. For example, if there is a constraint that balance >= 0, a transaction that attempts to overdraft an account must fail and be rolled back, ensuring the database is never left in an illegal state.
Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions $T_i$ and $T_j$, it appears to $T_i$ that either $T_j$ finished execution before $T_i$ started, or $T_j$ started execution after $T_i$ finished. Transactions should not interfere with each other or see each other's half-finished work.
After a transaction completes successfully (Commits), the changes it has made to the database persist, even if there are system failures (like a total power outage). The DBMS achieves this by writing the changes to a persistent transaction log on the hard drive before confirming the commit to the user.
A transaction goes through several states during its lifetime: