A transaction is a logical unit of database work that must be treated as one complete action. It may contain one SQL statement or many SQL statements, but from the DBMS point of view it should either complete fully or leave the database unchanged.
Transactions are needed because real database operations often involve multiple related changes. If one part succeeds and another part fails, the database may enter an incorrect state. Transaction management protects data correctness during failures, concurrent access, and complex updates.
The classic example is a money transfer from Account A to Account B. The database must debit one account and credit the other account. If only one operation is completed, the total money becomes incorrect.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 202;
INSERT INTO transfer_log (from_account, to_account, amount, transfer_time)
VALUES (101, 202, 500, CURRENT_TIMESTAMP);
COMMIT;
-- If any statement fails before COMMIT:
-- ROLLBACK;A reliable transaction must follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations in the transaction are completed, or none of them are applied. | Debit and credit both happen, or both are rolled back. |
| Consistency | The database moves from one valid state to another valid state. | Balance cannot become negative if a CHECK constraint or business rule prevents it. |
| Isolation | Concurrent transactions should not incorrectly interfere with one another. | Two transfers should not overwrite each other's balance update. |
| Durability | Once committed, changes survive crashes, power failures, or restarts. | A committed transfer remains saved after the database server restarts. |
Transaction Control Language commands are used to begin, save, commit, and roll back transaction work. The exact command names can vary slightly across DBMS products, but the ideas are the same.
| Command | Purpose |
|---|---|
START TRANSACTION or BEGIN |
Marks the start of a transaction. |
COMMIT |
Permanently saves all changes made in the transaction. |
ROLLBACK |
Cancels all uncommitted changes made in the transaction. |
SAVEPOINT |
Creates a named point inside a transaction. |
ROLLBACK TO SAVEPOINT |
Cancels changes after a savepoint without cancelling the whole transaction. |
START TRANSACTION;
UPDATE products
SET stock = stock - 2
WHERE product_id = 10;
SAVEPOINT after_stock_update;
INSERT INTO orders (customer_id, order_date)
VALUES (501, CURRENT_DATE);
-- Suppose order item insertion fails.
ROLLBACK TO SAVEPOINT after_stock_update;
-- Continue with corrected statements, then save permanently.
COMMIT;A transaction passes through several states during execution. These states help explain what the DBMS should do after normal completion, statement failure, or system crash.
| Point | COMMIT | ROLLBACK |
|---|---|---|
| Meaning | Accepts and saves the transaction changes. | Cancels uncommitted transaction changes. |
| Effect on data | Changes become visible according to isolation rules. | Database returns to the state before the transaction or savepoint. |
| Recovery | Committed changes may need REDO after a crash. | Uncommitted changes may need UNDO after a crash. |
| Common use | Order placed, payment posted, record approved. | Validation failed, deadlock occurred, user cancelled operation. |
When multiple transactions execute at the same time, their read and write operations may be interleaved. This interleaved order is called a schedule.
| Schedule Type | Meaning |
|---|---|
| Serial Schedule | One transaction completes before the next transaction starts. It is simple and correct but does not use concurrency well. |
| Non-Serial Schedule | Operations of different transactions are interleaved. It improves performance but may cause conflicts. |
| Serializable Schedule | A non-serial schedule that gives the same final result as some serial schedule. |
| Recoverable Schedule | If T2 reads data written by T1, then T2 commits only after T1 commits. |
| Cascadeless Schedule | Transactions read only committed data, so one rollback does not force many other rollbacks. |
In theory, transaction schedules are often described using simple operations such as R1(X) and W2(X).
Serial schedule:
R1(A), W1(A), R1(B), W1(B), C1,
R2(A), W2(A), C2
Non-serial schedule:
R1(A), R2(A), W1(A), W2(A), C1, C2
The non-serial schedule may be unsafe if W2(A) overwrites W1(A).Serializability is the correctness goal for concurrent transactions. A schedule is serializable if its result is equivalent to a serial execution of the same transactions.
| Type | Meaning | Important Point |
|---|---|---|
| Conflict Serializability | A schedule can be converted to a serial schedule by swapping non-conflicting operations. | Tested using a precedence graph. |
| View Serializability | A schedule has the same read-from relationships and final writes as a serial schedule. | More general than conflict serializability but harder to test. |
Two operations conflict if they belong to different transactions, access the same data item, and at least one operation is a write.
| Pair | Conflict? | Reason |
|---|---|---|
R1(X) and R2(X) |
No | Both are reads. |
R1(X) and W2(X) |
Yes | Same item and one write. |
W1(X) and R2(X) |
Yes | Same item and one write. |
W1(X) and W2(X) |
Yes | Same item and both write. |
Without proper transaction isolation, concurrent transactions may produce incorrect results. These problems are also called transaction anomalies.
| Problem | Description | Example |
|---|---|---|
| Lost Update | Two transactions update the same value, and one update overwrites the other. | T1 and T2 both read stock 10. T1 writes 8, T2 writes 7. One change is lost. |
| Dirty Read | A transaction reads data written by another transaction that has not committed. | T2 reads salary changed by T1, but T1 later rolls back. |
| Unrepeatable Read | A transaction reads the same row twice and gets different values because another transaction updated it. | T1 reads balance 5000, T2 updates it to 4500, T1 reads again and sees 4500. |
| Phantom Read | A transaction reruns a condition-based query and sees new rows inserted by another transaction. | T1 counts 5 orders, T2 inserts another matching order, T1 counts 6. |
| Incorrect Summary | A report reads data while another transaction is updating related rows. | A total balance report reads some old rows and some new rows during a transfer. |
Isolation levels control how much one transaction can see of another transaction's work. Higher isolation provides stronger correctness but may reduce concurrency.
| Isolation Level | Dirty Read | Unrepeatable Read | Phantom Read | Use Case |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | Fast reports where temporary inconsistency is acceptable. |
| READ COMMITTED | Prevented | Possible | Possible | Common default in many DBMS products. |
| REPEATABLE READ | Prevented | Prevented | May be possible, depending on DBMS implementation. | Transactions that must re-read the same rows reliably. |
| SERIALIZABLE | Prevented | Prevented | Prevented | Strict financial, inventory, and rule-sensitive operations. |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT balance
FROM accounts
WHERE account_id = 101;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;
COMMIT;Locking is a common way to control concurrent transactions. A transaction must acquire a suitable lock before reading or writing a data item.
| Lock Type | Also Called | Purpose |
|---|---|---|
| Shared Lock | Read lock | Allows multiple transactions to read the same data item. |
| Exclusive Lock | Write lock | Allows one transaction to modify the data item and blocks other access. |
| Intent Lock | Hierarchy lock | Shows that a transaction plans to lock lower-level objects such as rows inside a table. |
Two-Phase Locking (2PL) is a protocol that guarantees conflict serializability. It has two phases.
Not every DBMS relies only on traditional locking. Many systems also use timestamps or multiple row versions to improve concurrency.
| Technique | How it Works | Strength | Limitation |
|---|---|---|---|
| Timestamp Ordering | Each transaction receives a timestamp, and conflicting operations are ordered using those timestamps. | No deadlock because transactions do not wait in cycles. | Older transactions may be aborted and restarted. |
| MVCC | The DBMS keeps multiple versions of rows so readers can see a consistent snapshot while writers continue. | Readers and writers block each other less often. | Old row versions must be cleaned up later. |
| Optimistic Control | Transactions execute first and conflicts are checked before commit. | Works well when conflicts are rare. | High-conflict workloads may cause many rollbacks. |
A deadlock occurs when transactions wait for each other in a cycle. None of them can proceed unless the DBMS breaks the cycle.
T1 locks Account A.
T2 locks Account B.
T1 waits for Account B.
T2 waits for Account A.
Result: circular wait, so a deadlock occurs.| Technique | Meaning |
|---|---|
| Deadlock Prevention | Design rules so deadlocks cannot occur, such as lock ordering or timestamp-based schemes. |
| Deadlock Detection | Build a wait-for graph and look for cycles. |
| Deadlock Recovery | Abort one transaction, release its locks, and allow other transactions to continue. |
| Timeout | Abort a transaction if it waits too long for a lock. |
Recoverability is about whether a schedule can be safely recovered if a transaction fails. This matters when one transaction reads data written by another transaction.
| Schedule Property | Meaning | Quality |
|---|---|---|
| Recoverable | A transaction commits only after the transaction it read from has committed. | Acceptable minimum requirement. |
| Cascadeless | Transactions read only committed data. | Better because rollbacks do not cascade. |
| Strict | A transaction cannot read or write a data item until the last transaction that wrote it has committed or aborted. | Best for easy recovery. |
Recovery ensures that ACID properties are preserved after transaction failure, system crash, or media failure. The DBMS commonly uses logs to recover safely.
<START T1>
<T1, Account101, old=5000, new=4500>
<T1, Account202, old=3000, new=3500>
<COMMIT T1>
If the system crashes after COMMIT, the DBMS can REDO T1.
If the system crashes before COMMIT, the DBMS can UNDO T1.Many DBMS tools run in autocommit mode by default. In autocommit mode, each individual SQL statement is automatically committed if it succeeds. For multi-step business operations, disable autocommit or explicitly start a transaction.
| Mode | Behavior |
|---|---|
| Autocommit ON | Every statement is its own transaction. |
| Autocommit OFF | Changes remain pending until COMMIT or ROLLBACK. |
| Explicit transaction | The developer controls the transaction boundary using START, COMMIT, and ROLLBACK. |
| Mistake | Problem | Better Approach |
|---|---|---|
Forgetting WHERE in an update |
Too many rows may be changed. | Preview with SELECT and use key-based conditions. |
| Keeping transactions open too long | Locks remain active and block other users. | Do only required database work inside the transaction. |
| Using very high isolation everywhere | Concurrency and performance may drop. | Use SERIALIZABLE only where strict correctness requires it. |
| Ignoring rollback paths | Partial changes or locked resources may remain during errors. | Use try-catch or equivalent error handling in application code. |
A transaction is a sequence of database operations treated as one logical unit of work. It should either commit completely or roll back completely.
ACID properties ensure that transactions remain correct, reliable, isolated from harmful interference, and durable after commit.
COMMIT permanently saves transaction changes, while ROLLBACK cancels uncommitted changes and restores the previous consistent state.
A serializable schedule is a concurrent schedule that produces the same final result as some serial order of the transactions.
A dirty read happens when a transaction reads uncommitted data written by another transaction. If the writer rolls back, the reader has used invalid data.
A deadlock happens when transactions wait for each other in a circular chain, so none of them can continue until the DBMS aborts one transaction or resolves the wait.
Explore 500+ free tutorials across 20+ languages and frameworks.