{"id":5440,"date":"2026-02-15T16:29:03","date_gmt":"2026-02-15T10:59:03","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5440"},"modified":"2026-02-15T16:29:53","modified_gmt":"2026-02-15T10:59:53","slug":"oracle-commit-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-commit-statement-behind-the-scenes\/","title":{"rendered":"Oracle COMMIT Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute COMMIT;<\/code>, Oracle makes all your DML changes permanent. This seemingly simple statement triggers a complex series of operations involving redo logs, SCN allocation, lock releases, and checkpoint coordination. Understanding COMMIT is fundamental for database administrators and developers.<\/p>\n\n\n\n

In this guide, we’ll explore what happens when you commit a transaction in Oracle Database.<\/p>\n\n\n\n

The Complete COMMIT Execution Flow<\/h2>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    USER EXECUTES: COMMIT;                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 1: GENERATE COMMIT SCN                   \u2502\n\u2502  \u2022 Allocate unique System Change Number                         \u2502\n\u2502  \u2022 SCN marks point of transaction consistency                   \u2502\n\u2502  \u2022 Incrementing sequence number                                 \u2502\n\u2502  \u2022 Used for recovery and read consistency                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 2: WRITE REDO TO DISK                    \u2502\n\u2502  \u2022 Force Redo Log Buffer flush to disk                          \u2502\n\u2502  \u2022 LGWR (Log Writer) writes redo entries                        \u2502\n\u2502  \u2022 Write COMMIT record to redo log files                        \u2502\n\u2502  \u2022 This is the COMMIT POINT - point of no return                \u2502\n\u2502  \u2022 Wait for disk write confirmation                             \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 3: MARK TRANSACTION COMPLETE             \u2502\n\u2502  \u2022 Update transaction table in undo segment                     \u2502\n\u2502  \u2022 Record commit SCN                                            \u2502\n\u2502  \u2022 Mark transaction as committed                                \u2502\n\u2502  \u2022 Free transaction slot                                        \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 4: RELEASE ALL LOCKS                     \u2502\n\u2502  \u2022 Release TX (transaction) locks on rows                       \u2502\n\u2502  \u2022 Release TM (table) locks                                     \u2502\n\u2502  \u2022 Wake up waiting sessions                                     \u2502\n\u2502  \u2022 Other users can now modify same rows                         \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 5: MARK UNDO INACTIVE                    \u2502\n\u2502  \u2022 Undo segment marked as \"inactive\"                            \u2502\n\u2502  \u2022 Space available for overwrite                                \u2502\n\u2502  \u2022 Retained based on undo_retention                             \u2502\n\u2502  \u2022 Used for read consistency and flashback                      \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 6: MAKE CHANGES VISIBLE                  \u2502\n\u2502  \u2022 Changes visible to ALL sessions                              \u2502\n\u2502  \u2022 Other users see committed data                               \u2502\n\u2502  \u2022 Read consistency maintained via SCN                          \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 7: RETURN SUCCESS                        \u2502\n\u2502  \u2022 Return \"Commit complete\" message                             \u2502\n\u2502  \u2022 Transaction officially closed                                \u2502\n\u2502  \u2022 Changes are PERMANENT                                        \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502            BACKGROUND PROCESS (ASYNCHRONOUS)                    \u2502\n\u2502  \u2022 DBWR eventually writes dirty blocks to datafiles             \u2502\n\u2502  \u2022 Happens independently of COMMIT                              \u2502\n\u2502  \u2022 Changes already recoverable via redo logs                    \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Key Concept: What COMMIT Does and Doesn’t Do<\/h2>\n\n\n\n
What COMMIT DOES:<\/h3>\n\n\n\n
\u2713 Writes redo log buffer to disk (LGWR)\n\u2713 Generates and assigns commit SCN\n\u2713 Releases all locks\n\u2713 Makes changes visible to other sessions\n\u2713 Marks transaction as complete\n\u2713 Makes changes permanent and recoverable<\/code><\/pre>\n\n\n\n
What COMMIT DOES NOT DO:<\/h3>\n\n\n\n
\u2717 Does NOT write data blocks to datafiles immediately\n\u2717 Does NOT write undo to disk immediately\n\u2717 Does NOT clear buffer cache\n\u2717 Does NOT wait for DBWR (Database Writer)\n\u2717 Does NOT physically update table files right away<\/code><\/pre>\n\n\n\n
Critical Understanding:<\/strong><\/p>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502              COMMIT GUARANTEES                         \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                        \u2502\n\u2502  After COMMIT returns successfully:                    \u2502\n\u2502                                                        \u2502\n\u2502  1. Changes are PERMANENT                              \u2502\n\u2502  2. Changes are RECOVERABLE (via redo)                 \u2502\n\u2502  3. Changes are VISIBLE to all users                   \u2502\n\u2502                                                        \u2502\n\u2502  Even if:                                              \u2502\n\u2502  \u2022 Database crashes immediately after                  \u2502\n\u2502  \u2022 Power failure occurs                                \u2502\n\u2502  \u2022 Dirty blocks not yet written to disk                \u2502\n\u2502                                                        \u2502\n\u2502  Redo logs guarantee recovery!                         \u2502\n\u2502                                                        \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Detailed Step-by-Step Breakdown<\/h2>\n\n\n\n
Step 1: Generate Commit SCN<\/h3>\n\n\n\n
Oracle assigns a unique System Change Number to mark the transaction’s commit point.<\/p>\n\n\n\n
SCN (System Change Number):<\/strong><\/p>\n\n\n\n
What is SCN?\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502  \u2022 Ever-increasing sequence number     \u2502\n\u2502  \u2022 Unique identifier for every change  \u2502\n\u2502  \u2022 Used for:                           \u2502\n\u2502    - Transaction ordering              \u2502\n\u2502    - Read consistency                  \u2502\n\u2502    - Recovery operations               \u2502\n\u2502    - Flashback queries                 \u2502\n\u2502    - Replication                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\nExample SCN Timeline:\nSCN: 1234567 \u2192 INSERT executed\nSCN: 1234568 \u2192 UPDATE executed\nSCN: 1234569 \u2192 DELETE executed\nSCN: 1234570 \u2192 COMMIT (all changes at this SCN)<\/code><\/pre>\n\n\n\n
View Current SCN:<\/strong><\/p>\n\n\n\n
-- Get current database SCN\nSELECT CURRENT_SCN FROM V$DATABASE;\n\n-- Get SCN from timestamp\nSELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;\n\n-- Get timestamp from SCN\nSELECT SCN_TO_TIMESTAMP(1234567) FROM DUAL;<\/code><\/pre>\n\n\n\n
Transaction Example:<\/strong><\/p>\n\n\n\n
-- Session 1\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\nUPDATE employees SET salary = 55000 WHERE employee_id = 100;\nDELETE FROM employees WHERE employee_id = 99;\n\n-- Before COMMIT: Changes at SCN (not committed)\nSELECT CURRENT_SCN FROM V$DATABASE;\n-- Result: 1234569 (example)\n\nCOMMIT;\n\n-- After COMMIT: All changes permanently at SCN 1234570\nSELECT CURRENT_SCN FROM V$DATABASE;\n-- Result: 1234570<\/code><\/pre>\n\n\n\n
Step 2: Write Redo to Disk (THE CRITICAL STEP)<\/h3>\n\n\n\n
This is the actual COMMIT point – once redo is on disk, changes are permanent.<\/p>\n\n\n\n
Redo Log Buffer to Redo Log File:<\/p>\n\n\n\n
BEFORE COMMIT:\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502    Redo Log Buffer (Memory)        \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 \u2022 INSERT redo entry                \u2502\n\u2502 \u2022 UPDATE redo entry                \u2502\n\u2502 \u2022 DELETE redo entry                \u2502\n\u2502 \u2022 NOT YET ON DISK                  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502\n            \u2502 COMMIT issued\n            \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502    LGWR Process Activated          \u2502\n\u2502  \u2022 Writes all redo to disk         \u2502\n\u2502  \u2022 Writes COMMIT record            \u2502\n\u2502  \u2022 Synchronous operation           \u2502\n\u2502  \u2022 MUST complete before returning  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502\n            \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502    Redo Log Files (Disk)           \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 \u2022 All transaction redo entries     \u2502\n\u2502 \u2022 COMMIT record written            \u2502\n\u2502 \u2022 Changes now RECOVERABLE          \u2502\n\u2502 \u2022 COMMIT POINT reached             \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
LGWR (Log Writer) Process:<\/strong><\/p>\n\n\n\n
-- View LGWR statistics\nSELECT name, value\nFROM v$sysstat\nWHERE name IN (\n    'redo writes',\n    'redo write time',\n    'redo blocks written',\n    'user commits'\n)\nORDER BY name;<\/code><\/pre>\n\n\n\n
Redo Log Files:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN member FORMAT A60\n\n-- View redo log files\nSELECT l.group#,\n       l.thread#,\n       l.sequence#,\n       l.bytes\/1024\/1024 size_mb,\n       l.status,\n       l.archived,\n       lf.member\nFROM v$log l\nJOIN v$logfile lf ON l.group# = lf.group#\nORDER BY l.group#;\n\n-- Check current redo log\nSELECT group#, \n       sequence#,\n       bytes\/1024\/1024 size_mb,\n       status\nFROM v$log\nWHERE status = 'CURRENT';<\/code><\/pre>\n\n\n\n
Why Redo Write is Critical:<\/strong><\/p>\n\n\n\n
-- Scenario: Crash immediately after COMMIT\n\n-- Session executes:\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\nCOMMIT;\n-- \"Commit complete\" returned to user\n\n-- Database crashes 1 second later\n-- Dirty blocks NOT yet written to datafiles\n\n-- On restart:\n-- Oracle uses redo logs to replay the UPDATE\n-- Data is recovered - change is permanent!\n\n-- This is possible because:\n-- 1. Redo was written to disk BEFORE COMMIT returned\n-- 2. Redo contains all necessary information\n-- 3. Oracle replays redo during crash recovery<\/code><\/pre>\n\n\n\n
Step 3: Mark Transaction Complete<\/h3>\n\n\n\n
Transaction table in undo segment header is updated.<\/p>\n\n\n\n
-- View active transactions\nSELECT s.username,\n       s.sid,\n       s.serial#,\n       t.start_time,\n       t.status,\n       t.used_ublk,\n       t.log_io,\n       t.phy_io\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr;\n\n-- Before COMMIT: Transaction appears in v$transaction\n-- After COMMIT: Transaction disappears from v$transaction<\/code><\/pre>\n\n\n\n
Step 4: Release All Locks<\/h3>\n\n\n\n
All locks held by the transaction are immediately released.<\/p>\n\n\n\n
Lock Release Process:<\/p>\n\n\n\n
BEFORE COMMIT:\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     Locked Resources               \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 \u2022 Row lock on employee_id=101      \u2502\n\u2502 \u2022 Row lock on employee_id=102      \u2502\n\u2502 \u2022 Table lock (RX mode)             \u2502\n\u2502 \u2022 Other sessions WAITING           \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\nCOMMIT EXECUTED:\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     Lock Release                   \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 \u2022 All row locks released           \u2502\n\u2502 \u2022 Table locks released             \u2502\n\u2502 \u2022 Waiting sessions awakened        \u2502\n\u2502 \u2022 Resources available immediately  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Example – Lock Release:<\/strong><\/p>\n\n\n\n
-- Session 1\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- Lock acquired, not committed yet\n\n-- Session 2 (waiting)\nUPDATE employees SET department_id = 20 WHERE employee_id = 101;\n-- WAITS for Session 1\n\n-- Session 1\nCOMMIT;\n-- Lock released immediately\n\n-- Session 2\n-- Immediately proceeds with UPDATE\n-- No longer waiting<\/code><\/pre>\n\n\n\n
Monitor Lock Releases:<\/strong><\/p>\n\n\n\n
SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN object_name FORMAT A30\n\n-- View locks BEFORE commit\nSELECT s.username,\n       s.sid,\n       o.object_name,\n       DECODE(l.lmode,\n              0, 'None',\n              1, 'Null',\n              2, 'Row Share',\n              3, 'Row Exclusive',\n              4, 'Share',\n              5, 'Share Row Excl',\n              6, 'Exclusive') lock_mode\nFROM v$lock l\nJOIN v$session s ON l.sid = s.sid\nJOIN dba_objects o ON l.id1 = o.object_id\nWHERE s.username IS NOT NULL;\n\n-- After COMMIT: Locks disappear<\/code><\/pre>\n\n\n\n
Step 5: Mark Undo Inactive<\/h3>\n\n\n\n
Undo segments are marked as inactive but retained for read consistency.<\/p>\n\n\n\n
Undo Lifecycle:<\/p>\n\n\n\n
ACTIVE TRANSACTION:\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     Undo Segment                   \u2502\n\u2502  Status: ACTIVE                    \u2502\n\u2502  \u2022 Contains before images          \u2502\n\u2502  \u2022 Used for ROLLBACK               \u2502\n\u2502  \u2022 Cannot be overwritten           \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502\n            \u2502 COMMIT\n            \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     Undo Segment                   \u2502\n\u2502  Status: INACTIVE                  \u2502\n\u2502  \u2022 Still contains data             \u2502\n\u2502  \u2022 Used for read consistency       \u2502\n\u2502  \u2022 Used for flashback              \u2502\n\u2502  \u2022 Can be overwritten after        \u2502\n\u2502    undo_retention period           \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Undo Retention:<\/strong><\/p>\n\n\n\n
-- Check undo retention setting\nSHOW PARAMETER undo_retention;\n-- Typically 900 seconds (15 minutes)\n\n-- View undo usage\nSELECT tablespace_name,\n       status,\n       ROUND(SUM(bytes)\/1024\/1024, 2) mb\nFROM dba_undo_extents\nGROUP BY tablespace_name, status\nORDER BY tablespace_name, status;\n\n-- Active: Current transactions\n-- Unexpired: Within undo_retention, available for read consistency\n-- Expired: Beyond undo_retention, can be overwritten<\/code><\/pre>\n\n\n\n
Step 6: Make Changes Visible<\/h3>\n\n\n\n
After COMMIT, all other sessions can see the changes.<\/p>\n\n\n\n
Read Consistency Example:<\/strong><\/p>\n\n\n\n
-- Session 1 (Time: 10:00:00)\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- Not committed, SCN = 1234567\n\n-- Session 2 (Time: 10:00:05)\nSELECT salary FROM employees WHERE employee_id = 101;\n-- Result: 50000 (old value, Session 1 not committed)\n\n-- Session 1 (Time: 10:00:10)\nCOMMIT;\n-- Commit SCN = 1234570\n\n-- Session 2 (Time: 10:00:15)\nSELECT salary FROM employees WHERE employee_id = 101;\n-- Result: 60000 (new value, now visible after commit)<\/code><\/pre>\n\n\n\n
SCN-based Read Consistency:<\/strong><\/p>\n\n\n\n
-- Query as of specific SCN (before commit)\nSELECT salary \nFROM employees AS OF SCN 1234567\nWHERE employee_id = 101;\n-- Result: 50000 (before update)\n\n-- Query current data (after commit)\nSELECT salary \nFROM employees \nWHERE employee_id = 101;\n-- Result: 60000 (after update and commit)\n\n-- Query as of timestamp\nSELECT salary\nFROM employees AS OF TIMESTAMP (SYSDATE - 1\/24)\nWHERE employee_id = 101;\n-- Result: Old value from 1 hour ago<\/code><\/pre>\n\n\n\n
Step 7: Return Success & Background Write<\/h3>\n\n\n\n
COMMIT returns success immediately after redo write. Data blocks written later.<\/p>\n\n\n\n
Timeline:<\/p>\n\n\n\n
Time 0ms:  User executes COMMIT\nTime 5ms:  LGWR writes redo to disk\nTime 6ms:  \"Commit complete\" returned to user\n           \u2191 COMMIT FINISHES HERE\n           \nTime 100ms: DBWR writes dirty blocks to datafiles (background)\nTime 500ms: More dirty blocks written\nTime 2s:    Checkpoint occurs\n           \nNote: User doesn't wait for DBWR!<\/code><\/pre>\n\n\n\n
Why COMMIT is Fast:<\/strong><\/p>\n\n\n\n
-- COMMIT only waits for redo write (small, sequential)\n-- Does NOT wait for data block writes (large, random I\/O)\n\n-- Typical COMMIT times:\n-- \u2022 Redo write: 1-10 milliseconds\n-- \u2022 Data block write: 100-1000 milliseconds (but async!)\n\n-- This is why COMMIT is fast even for large transactions<\/code><\/pre>\n\n\n\n
COMMIT Variations<\/h2>\n\n\n\n
1. Explicit COMMIT<\/h3>\n\n\n\n
-- Manual commit\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\nUPDATE employees SET salary = 55000 WHERE employee_id = 100;\nCOMMIT;\n-- Explicit, controlled by user<\/code><\/pre>\n\n\n\n
2. Auto-COMMIT (DDL Statements)<\/h3>\n\n\n\n
-- DDL auto-commits\nCREATE TABLE test_table (id NUMBER);\n-- Automatically commits\n-- Any pending DML is also committed!\n\n-- Example:\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n-- Not committed yet\n\nCREATE INDEX emp_idx ON employees(last_name);\n-- DDL auto-commits\n-- INSERT is now also committed!<\/code><\/pre>\n\n\n\n
DDL Statements that Auto-COMMIT:<\/strong><\/p>\n\n\n\n
CREATE TABLE ...    -- Auto-commits\nALTER TABLE ...     -- Auto-commits\nDROP TABLE ...      -- Auto-commits\nTRUNCATE TABLE ...  -- Auto-commits\nCREATE INDEX ...    -- Auto-commits\nGRANT ...           -- Auto-commits<\/code><\/pre>\n\n\n\n
3. COMMIT WRITE Options<\/h3>\n\n\n\n
-- Default COMMIT (wait for redo write)\nCOMMIT;\n\n-- COMMIT IMMEDIATE (wait for redo write) - default\nCOMMIT WRITE IMMEDIATE;\n\n-- COMMIT BATCH (group redo writes for performance)\nCOMMIT WRITE BATCH;\n\n-- COMMIT NOWAIT (don't wait for redo write confirmation)\n-- Dangerous! Not recommended\nCOMMIT WRITE NOWAIT;\n\n-- COMMIT WAIT (wait for redo write) - safest, default\nCOMMIT WRITE WAIT;<\/code><\/pre>\n\n\n\n
Performance Tuning:<\/strong><\/p>\n\n\n\n
-- For high-volume OLTP with acceptable risk:\nALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';\n-- Groups commits, doesn't wait for disk confirmation\n-- Faster but risk of data loss on crash\n\n-- For critical transactions:\nALTER SESSION SET COMMIT_WRITE = 'IMMEDIATE,WAIT';\n-- Default, safest option<\/code><\/pre>\n\n\n\n
4. COMMIT FORCE (Distributed Transactions)<\/h3>\n\n\n\n
-- For distributed transactions in doubt\nCOMMIT FORCE 'transaction_id';\n-- Used in distributed database scenarios\n-- Requires DBA privileges<\/code><\/pre>\n\n\n\n
COMMIT Best Practices<\/h2>\n\n\n\n
1. OLTP: Frequent Commits<\/h3>\n\n\n\n
-- Web application: Commit after each user action\nBEGIN\n    INSERT INTO orders VALUES (...);\n    INSERT INTO order_items VALUES (...);\n    UPDATE inventory SET quantity = quantity - 1;\n    COMMIT;  -- Commit each order\nEND;\n\/<\/code><\/pre>\n\n\n\n
2. Batch Processing: Periodic Commits<\/h3>\n\n\n\n
-- Batch job: Commit every N rows\nBEGIN\n    FOR i IN 1..1000000 LOOP\n        INSERT INTO staging VALUES (...);\n        \n        IF MOD(i, 10000) = 0 THEN\n            COMMIT;  -- Commit every 10,000 rows\n        END IF;\n    END LOOP;\n    COMMIT;  -- Final commit\nEND;\n\/<\/code><\/pre>\n\n\n\n
3. Never COMMIT Inside Loops (Usually)<\/h3>\n\n\n\n
-- Bad: Commit every row\nFOR emp IN (SELECT * FROM employees) LOOP\n    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp.employee_id;\n    COMMIT;  -- Too many commits! Slow!\nEND LOOP;\n\n-- Good: Batch commits\nDECLARE\n    v_count NUMBER := 0;\nBEGIN\n    FOR emp IN (SELECT * FROM employees) LOOP\n        UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp.employee_id;\n        v_count := v_count + 1;\n        \n        IF MOD(v_count, 1000) = 0 THEN\n            COMMIT;  -- Commit every 1000 rows\n        END IF;\n    END LOOP;\n    COMMIT;\nEND;\n\/<\/code><\/pre>\n\n\n\n
4. Consider Transaction Size<\/h3>\n\n\n\n
-- Too small: Excessive commits (overhead)\nUPDATE employees SET salary = salary + 100;\nCOMMIT;\nUPDATE employees SET bonus = 1000;\nCOMMIT;\nUPDATE employees SET commission = 500;\nCOMMIT;\n\n-- Better: Group related changes\nUPDATE employees SET salary = salary + 100;\nUPDATE employees SET bonus = 1000;\nUPDATE employees SET commission = 500;\nCOMMIT;  -- Single commit for all\n\n-- Too large: Huge undo, long locks\nUPDATE large_table SET status = 'PROCESSED';  -- 10 million rows\nCOMMIT;\n\n-- Better: Batch\nLOOP\n    UPDATE large_table SET status = 'PROCESSED' WHERE ROWNUM <= 100000;\n    EXIT WHEN SQL%ROWCOUNT = 0;\n    COMMIT;\nEND LOOP;<\/code><\/pre>\n\n\n\n
5. Always COMMIT or ROLLBACK<\/h3>\n\n\n\n
-- Bad: Session ends without commit\nBEGIN\n    UPDATE employees SET salary = 60000;\n    -- Session disconnects here\n    -- Implicit ROLLBACK occurs!\nEND;\n\n-- Good: Explicit commit\nBEGIN\n    UPDATE employees SET salary = 60000;\n    COMMIT;  -- Changes saved\nEND;<\/code><\/pre>\n\n\n\n
Monitoring COMMIT Performance<\/h2>\n\n\n\n
COMMIT Statistics<\/h3>\n\n\n\n
SET LINESIZE 150\nCOLUMN name FORMAT A40\nCOLUMN value FORMAT 999,999,999\n\n-- View commit statistics\nSELECT name, value\nFROM v$sysstat\nWHERE name IN (\n    'user commits',\n    'transaction rollbacks',\n    'redo writes',\n    'redo write time',\n    'redo sync time'\n)\nORDER BY name;\n\n-- Calculate average redo write time per commit\nSELECT ROUND(redo_write_time.value \/ redo_writes.value, 2) avg_write_time_ms\nFROM (SELECT value FROM v$sysstat WHERE name = 'redo write time') redo_write_time,\n     (SELECT value FROM v$sysstat WHERE name = 'redo writes') redo_writes;<\/code><\/pre>\n\n\n\n
Wait Events Related to COMMIT<\/h3>\n\n\n\n
SET LINESIZE 180\nCOLUMN event FORMAT A40\nCOLUMN total_waits FORMAT 999,999,999\n\nSELECT event,\n       total_waits,\n       ROUND(time_waited\/100, 2) time_waited_sec,\n       ROUND(average_wait\/100, 4) avg_wait_sec\nFROM v$system_event\nWHERE event LIKE '%log%'\n   OR event LIKE '%commit%'\nORDER BY time_waited DESC;\n\n-- Key events:\n-- log file sync: Wait for LGWR to write redo (COMMIT wait)\n-- log file parallel write: LGWR writing to disk\n-- log buffer space: Waiting for log buffer space<\/code><\/pre>\n\n\n\n
Session COMMIT Activity<\/h3>\n\n\n\n
SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN commits FORMAT 999,999\n\nSELECT s.username,\n       s.sid,\n       s.serial#,\n       st.value commits\nFROM v$session s\nJOIN v$sesstat st ON s.sid = st.sid\nJOIN v$statname sn ON st.statistic# = sn.statistic#\nWHERE sn.name = 'user commits'\n  AND s.username IS NOT NULL\nORDER BY commits DESC;<\/code><\/pre>\n\n\n\n
Common COMMIT Scenarios<\/h2>\n\n\n\n
Scenario 1: Multi-Table Transaction<\/h3>\n\n\n\n
-- All or nothing: COMMIT makes all permanent\nBEGIN\n    INSERT INTO orders VALUES (1001, SYSDATE, 'Customer A');\n    INSERT INTO order_items VALUES (1, 1001, 'Product X', 10);\n    INSERT INTO order_items VALUES (2, 1001, 'Product Y', 5);\n    UPDATE inventory SET quantity = quantity - 10 WHERE product = 'Product X';\n    UPDATE inventory SET quantity = quantity - 5 WHERE product = 'Product Y';\n    \n    COMMIT;  -- All changes permanent together\nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;  -- All changes discarded together\nEND;\n\/<\/code><\/pre>\n\n\n\n
Scenario 2: Long-Running Job<\/h3>\n\n\n\n
-- Batch processing with periodic commits\nDECLARE\n    v_batch_size NUMBER := 10000;\n    v_total NUMBER := 0;\nBEGIN\n    FOR rec IN (SELECT * FROM source_table) LOOP\n        INSERT INTO target_table VALUES (rec.col1, rec.col2, rec.col3);\n        v_total := v_total + 1;\n        \n        IF MOD(v_total, v_batch_size) = 0 THEN\n            COMMIT;\n            DBMS_OUTPUT.PUT_LINE('Committed ' || v_total || ' rows');\n        END IF;\n    END LOOP;\n    \n    COMMIT;  -- Final commit for remaining rows\n    DBMS_OUTPUT.PUT_LINE('Total rows: ' || v_total);\nEND;\n\/<\/code><\/pre>\n\n\n\n
Scenario 3: Error Handling<\/h3>\n\n\n\n
-- Proper error handling with COMMIT\/ROLLBACK\nBEGIN\n    -- Start transaction\n    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;\n    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;\n    \n    -- Validation\n    IF (SELECT balance FROM accounts WHERE account_id = 101) < 0 THEN\n        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');\n    END IF;\n    \n    COMMIT;  -- Success\n    DBMS_OUTPUT.PUT_LINE('Transfer successful');\n    \nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;  -- Failure\n        DBMS_OUTPUT.PUT_LINE('Transfer failed: ' || SQLERRM);\n        RAISE;\nEND;\n\/<\/code><\/pre>\n\n\n\n
COMMIT vs ROLLBACK Comparison<\/h2>\n\n\n\n
Aspect<\/th>COMMIT<\/th>ROLLBACK<\/th><\/tr><\/thead>
Purpose<\/strong><\/td>Make changes permanent<\/td>Discard changes<\/td><\/tr>
Redo<\/strong><\/td>Writes to disk<\/td>Generates redo for undo<\/td><\/tr>
Locks<\/strong><\/td>Releases all locks<\/td>Releases all locks<\/td><\/tr>
Undo<\/strong><\/td>Marks inactive<\/td>Applies undo to restore<\/td><\/tr>
Visibility<\/strong><\/td>Changes visible to all<\/td>Changes never visible<\/td><\/tr>
Recovery<\/strong><\/td>Changes recoverable<\/td>Changes discarded<\/td><\/tr>
Speed<\/strong><\/td>Fast (redo write)<\/td>Can be slow (undo apply)<\/td><\/tr>
When<\/strong><\/td>Transaction successful<\/td>Transaction failed\/cancelled<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Quick Reference<\/h2>\n\n\n\n
Basic COMMIT Syntax<\/h3>\n\n\n\n
-- Simple commit\nCOMMIT;\n\n-- Commit with comment (for distributed transactions)\nCOMMIT COMMENT 'Order processing completed';\n\n-- Commit with write options\nCOMMIT WRITE IMMEDIATE;\nCOMMIT WRITE BATCH;\nCOMMIT WRITE WAIT;\nCOMMIT WRITE NOWAIT;<\/code><\/pre>\n\n\n\n
Key COMMIT Queries<\/h3>\n\n\n\n
-- Check if uncommitted changes exist\nSELECT COUNT(*) FROM v$transaction;\n-- If > 0, uncommitted transactions exist\n\n-- View current SCN\nSELECT CURRENT_SCN FROM V$DATABASE;\n\n-- View redo log status\nSELECT group#, status, archived FROM v$log;\n\n-- View commit performance\nSELECT name, value FROM v$sysstat \nWHERE name LIKE '%commit%' OR name LIKE '%redo%';<\/code><\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
COMMIT is the most critical statement in transaction management. It guarantees durability by writing redo to disk, releases locks to improve concurrency, and makes changes visible to all users.<\/p>\n\n\n\n
Key Takeaways:<\/strong><\/p>\n\n\n\n
    \n
  1. COMMIT writes redo, not data<\/strong> – Data blocks written asynchronously<\/li>\n\n\n\n
  2. Redo on disk = changes permanent<\/strong> – Even if crash occurs immediately<\/li>\n\n\n\n
  3. SCN marks consistency point<\/strong> – Used for recovery and read consistency<\/li>\n\n\n\n
  4. Locks released immediately<\/strong> – Improves concurrency<\/li>\n\n\n\n
  5. Fast operation<\/strong> – Only waits for redo write (sequential I\/O)<\/li>\n\n\n\n
  6. Undo marked inactive<\/strong> – Retained for read consistency<\/li>\n\n\n\n
  7. DDL auto-commits<\/strong> – Be careful with pending DML<\/li>\n\n\n\n
  8. Batch commits for performance<\/strong> – Balance between safety and efficiency<\/li>\n<\/ol>\n\n\n\n
    Understanding COMMIT execution is essential for:<\/p>\n\n\n\n
      \n
    • Transaction management<\/li>\n\n\n\n
    • Performance tuning<\/li>\n\n\n\n
    • Concurrency optimization<\/li>\n\n\n\n
    • Recovery planning<\/li>\n\n\n\n
    • Database design<\/li>\n<\/ul>\n\n\n\n
      Remember:<\/strong> Once COMMIT completes successfully, your changes are permanent and recoverable, even if the database crashes immediately afterward. This is the power of Oracle’s write-ahead logging!<\/p>\n","protected":false},"excerpt":{"rendered":"
      When you execute COMMIT;, Oracle makes all your DML changes permanent. This seemingly simple statement triggers a complex series of operations involving redo logs, SCN allocation, lock releases, and checkpoint coordination. Understanding COMMIT is fundamental for database administrators and developers. In this guide, we’ll explore what happens when you commit a transaction in Oracle Database. […]<\/p>\n","protected":false},"author":1,"featured_media":5442,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5440","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5440"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5440\/revisions"}],"predecessor-version":[{"id":5443,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5440\/revisions\/5443"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5442"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}