In this lesson, you will learn how a database engine handles data changes that are still sitting in memory when you initiate a graceful shutdown.
When you send a SHUTDOWN command to your database, it doesn't just pull the plug. Because the database prioritizes integrity, it treats the shutdown as a signal to transition into a consistent, stable state. But what happens to the transactions that are currently running, specifically those that haven't reached a COMMIT?
The Graceful Termination Process
During a graceful shutdown, the engine stops accepting new connections and enters a "quiesce" mode. It then identifies all uncommitted transactions currently residing in memory—the "pending memory-only updates." Since these updates were never finalized, they cannot be permanently written to the data files, as that would violate the Atomicity property of ACID.
Instead of writing them, the engine performs a rollback of these transactions. It uses the Undo Log (which you learned about in lesson 15) to revert the changes in the buffer pool. Essentially, the engine undoes every modification these uncommitted transactions made to the data pages in memory, restoring the data to its last known consistent state.
Why Rollback is Necessary
Consider this scenario:
-- Transaction started, but not committed
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Shutdown signal received here!
If the engine didn't roll this back, the balance would remain decreased in the memory buffer. If the system then shut down, that "dangling" update would be lost or, worse, potentially written to disk during the final checkpoint, leading to an inconsistent database. By rolling back during shutdown, the engine ensures that the data on disk remains in its most recent "Committed" state.
Once all uncommitted work is cleared, the engine flushes the remaining dirty pages to disk, finalizes the logs, and safely closes the files. This ensures that when the system starts up again, it doesn't have to perform a complex recovery process for those transactions.
Key Takeaway: During a graceful shutdown, the database engine automatically rolls back all uncommitted transactions using the undo logs to ensure the data files remain in a consistent, committed state before the process terminates.