06. Importance Of Undo Log

-

 

Importance Of Undo Log



Question:

In MySQL (InnoDB), when a row is updated inside a transaction, it is loaded into memory (RAM), and undo/redo logs are used.

Suppose we update a row's value but later roll back the transaction — why do we still need the undo log if the change wasn't committed to the database?

Also, how does MySQL use memory (RAM) to avoid reading from disk again?

 

Example:

 Initially, the name in the `Student` table is `"A"`.

 Transaction 1: Updates name to `"B"` but then rolls back.

 Transaction 2: Updates name to `"B"` again and commits.

 

How does MySQL handle undo logs, in-memory changes, and rollback? Why do we need to roll back in-memory data if it’s not yet committed?

 

 

Answer:

  • When you run a query in MySQL (InnoDB), the row is loaded into RAM (called the buffer pool) to speed up access. MySQL prefers working from memory rather than reading from disk repeatedly — even for SELECT queries. 
  • When you update a row inside a transaction, that change is only visible to your transaction unless it's committed. The original version of the row is saved in the undo log. 
  • If you roll back, MySQL uses the undo log to revert the in-memory row back to its original state. This is very important — because if memory wasn’t rolled back and still showed `"B"`, future SELECTs might return the wrong value, even though it was never committed.

 

In your example: 

1. Name starts as `"A"`.

2. Transaction 1 updates to `"B"`, then rolls back.

    Undo log restores name back to `"A"` in memory.

3. Transaction 2 updates name to `"B"` again and commits.

    Now `"B"` is saved to disk and kept in memory.

 

This is why undo logs are essential — not just for rollbacks, but to keep the in-memory view consistent with the actual database state.

 

Why in-memory matters:

  • MySQL tries to avoid disk I/O for performance.
  • When we SELECT a row, it’s usually fetched from RAM, not disk.
  • That’s why it's important that in-memory data always reflects the correct state — especially after rollbacks.

Comments

Post a Comment

Popular posts from this blog

Database - Topics

-
Image
 Topics  01. Steps in SQL Query Execution 02. How does the database create a query plan 03. How Query Plan Caching Works 04. What is the need for an execution plan? 05. How Do Query/ Query Plan Execute (Inside MySQL Server) 06. Importance Of Undo Log 07. How Row Lock Is Handle When Query On The Table Row 08. Technique to recover the committed changes 09. Auditing technique 10. Query cache 11. DB connection pool 12. What is ACID? 13. Optimizing SQL queries
Read more

02. Spring – Creating spring project clone it with GIT step by step.

-
Image
  Creating spring project clone it with GIT step by step. 01. Create an empty GIT repository and select a repository name. 02.   Check to add README.md and technology to add it inside the gitignire file0- it will include all the generated class when we complete the application. 03.      Copy the github repository URL. 04 . Open eclipse and go to,             Window – Show View – Other - Type git on the folder and select git repositories. 05.   Now clone this repository at your desired location in which we are going to keep the project specific file . a)         a)  Step One.             b)  Step  Two .          c)    Step Three. Both projects are generated as bellows           d) Step Four  Open the gitingnore file and ignore the target folder of the project. All the class files which are ...
Read more

01. Steps in SQL Query Execution (MySQL)

-
Image
Steps in SQL Query Execution (MySQL) 1. Client Sends Query to Server The SQL statement is sent to the MySQL server over a connection (e.g., via JDBC or a query tool like MySQL Workbench). 2. Parser / Syntax Check The parser checks the SQL syntax.If there are any syntax errors, it throws an error immediately. 3. Pre-processor Verifies privileges and permissions for the user executing the query. Resolves object names (like table or column aliases). 4. Query Optimization The optimizer evaluates multiple strategies to execute the query. It considers: Which indexes to use. Join order (for multi-table queries). Whether to use temporary tables. Cost of different execution paths. The optimizer chooses select the most efficient query plan (Cost effective -> memory, CPU etc). 5. Query Execution Plan (Most cost effective query plan) The query plan is a blueprint of how MySQL will fetch the data. You can view this with the EXPLAIN keyword in front of your query. 6. Query Execution The storage e...
Read more