DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions (MCQs)DBMS Lab Programs
A transaction in a database system does not simply start and end — it passes through a well-defined sequence of states during its lifetime. Understanding...
Introduction
A transaction in a database system does not simply start and end — it passes through a well-defined sequence of states during its lifetime. Understanding these states is essential for implementing proper transaction management, recovery mechanisms, and concurrency control. The transaction state diagram describes every possible condition a transaction can be in, from the moment it begins executing until it either successfully completes or is rolled back.
State Transitions in Detail
| From State | To State | Trigger |
|---|---|---|
| Active | Partially Committed | Last statement executed successfully |
| Active | Failed | Error during execution (crash, deadlock, constraint) |
| Partially Committed | Committed | Changes successfully written to stable storage |
| Partially Committed | Failed | Unable to write to stable storage (disk full, crash) |
| Failed | Aborted | Rollback completed successfully |
| Aborted | Active (new transaction) | System decides to restart |
| Aborted | Terminated | System decides to kill |
Practical Example: Bank Transfer
| Transaction T | Transfer 1000 from Account A to Account B |
| READ(A) | A = 5000 |
| A = A - 1000 | A = 4000 |
| WRITE(A) | buffer updated |
| READ(B) | B = 3000 |
| B = B + 1000 | B = 4000 |
| WRITE(B) | buffer updated (last statement) |
| Scenario A | Success |
| All writes successful | State 3: COMMITTED |
| Scenario B | Failure (disk crash during flush) |
| Cannot guarantee persistence | State 4: FAILED |
| UNDO | Restore A = 5000 (from log's before-image) |
| UNDO | Restore B = 3000 (from log's before-image) |
| System decides | restart T (hardware glitch was temporary) |
Important Observations
- Committed and Aborted are terminal states — a transaction reaches one or the other, never both
- Partially Committed is not the same as Committed — the transaction can still fail at this point
- The transition from Partially Committed to Committed is the critical moment — this is when durability is guaranteed (commit record hits stable storage)
- Once committed, a transaction cannot be undone — to reverse its effects, a new compensating transaction must be executed
Comparison with Real-World Analogy
| Transaction State | Online Shopping Analogy |
|---|---|
| Active | Adding items to cart, entering shipping details |
| Partially Committed | Clicked "Place Order" — payment processing |
| Committed | Payment confirmed, order shipped |
| Failed | Payment declined, website error |
| Aborted | Order cancelled, refund issued |
Just as a declined payment cancels the entire order (atomicity), a failed transaction undoes all its database changes.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Transaction States.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, transaction, states, transaction states
Related Database Management Systems (DBMS) Topics
Continue learning this concept
Unit 4Transaction ManagementA transaction is a logical unit of work consisting of one or more database operations (reads and writes) that must execute as a single, indivisible unit....Unit 4Unit 4 — Transaction Management & Concurrency ControlThis unit addresses two fundamental challenges in database systems — ensuring that database operations complete reliably even when failures occur, and ensuring that multiUnit 4ACID PropertiesACID is an acronym for the four key properties that every database transaction must satisfy to ensure data validity even in cases of errors, power failures,...Unit 4CheckpointsA checkpoint is a recovery mechanism where the DBMS periodically forces all modified (dirty) buffer pages to disk and records the state of active...Unit 4Concurrency Control & SerializabilityConcurrency control is the mechanism that ensures correct results when multiple transactions execute simultaneously, without interfering with each other.Unit 4Deadlock in DBMSA deadlock is a situation where two or more transactions are waiting indefinitely for each other to release locks — creating a circular wait that can never...