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 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.
Continue the lesson path
Study next in this module
01Unit 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....02Unit 4Two-Phase Locking (2PL)Two-Phase Locking (2PL) is a concurrency control protocol that guarantees conflict serializability by organizing all lock operations of every transaction...03Unit 4Timestamp-Based ProtocolThe Timestamp Ordering Protocol is a concurrency control mechanism that uses transaction timestamps — rather than locks — to determine the serialization...04Unit 4Unit 4 Summary — Transaction Management & Concurrency ControlThis unit covered the mechanisms that keep databases reliable and correct when multiple users access data simultaneously and when system failures occur. The...