What Does Redone Mean?
Recently, I was honored by having a fellow DBA I’ve known for many years ask me to be his mentor. Earlier this week, he was reading a TechNet article by Paul Randal (blog | @PaulRandal) on Understanding Logging and Recovery in SQL Server. He didn’t quite understand what Paul meant by the term redone.
His Questions via Email
I was reading recovery by Paul and just came across the below sentence:
For a log record from a committed transaction where the database page has an LSN less than the LSN of the log record, the log record must be redone to ensure the transaction effects are persisted.
What does Redone mean? Is it the same as replaying the transaction? So if the database page on disk has LSN less than the LSN of the Log record that means the transaction has to be replayed again so that the LSN of the database page now matches the LSN of the log record? Can you please clarify?
I had a thought what if the number of transactions that have to be replayed are a lot, won’t there be a performance impact while they are being replayed? Also I assume the replay is handled by checkpoint. Please correct me if I am wrong.
My Response
The term “redo” is used to refer to the log records that are hardened to the log that have not been written to the date file yet. The term “redone” or “redoing” simply means applying the redo part of the log to the data file. It is generally very quick as SQL Server attempts to keep the redo as small as possible. This is where the Checkpoint operation comes into play.
The result of the transaction (data changes) have been written to memory as part of the process. For all transactions, there are accompanying log records in the transaction log that describe exactly what was performed. Under normal operations, the CheckPoint process writes dirty pages from memory to disk. When it does this, it stamps the Checkpoint LSN to the boot page of the database. This gives SQL Server a point guaranteed to be consistent from which to start redoing log records. So the more log records there are that need to be applied, the longer recovery will take. Any time SQL Server is shut down gracefully or a database is shut down, a Checkpoint is executed to ensure that recovery will be as quick as possible.
Followup Questions
Thanks Robert for the clarification. So is there something actually like “Replaying Transactions.” I believe that transactions never get replayed. Only Redo is what happens. Correct?
My Followup Response
The only thing that actually replays the transactions is if you replicate the execution of a stored procedure.
It’s often referred to as replaying the transactions, but it’s not actually replaying it. It’s replaying the log records. If you understand how transactions are logged, you’ll understand how it handles redo. If I perform a transaction that updates 10 rows, then there will be 10 log entries, 1 for each row that gets updated stating what the update is. You can think of it as breaking the update into small updates that each update a single row. So when it is redone, instead of re-executing the update query, it performs the resulting changes to the specific rows.
This is a simplistic description of what actually gets logged. More than just the changes is logged, but this is for explaining how redo works.
To explain it out a little further:
Sample Query:
Update dbo.Employees
Set PayRate = PayRate * 1.5
Where JobRole = ‘Manager’
Let’s assume this updates 4 rows as follows:
EmployeeID (PK) | Original PayRate | New PayRate |
4 | 100000 | 150000 |
27 | 80000 | 120000 |
55 | 75000 | 112500 |
87 | 70000 | 105000 |
This will log 4 log records each explicitly indicating what was updated. Again, this is a simplistic description and more than just these 4 records will be logged.
Here is some code to demonstrate what I’m explaining:
Use master; Go -- Create test database Create Database TestUpdate; Go -- Set recovery model to simple Alter Database TestUpdate Set Recovery Simple; Go --Switch to new database Use TestUpdate; Go -- Create table for test Create Table dbo.Employees ( EmployeeID int not null primary key, PayRate int not null, JobRole varchar(10) not null) Go -- Insert sample data Insert Into dbo.Employees Values (4, 100000, 'Manager'), (27, 80000, 'Manager'), (55, 75000, 'Manager'), (87, 70000, 'Manager'), (5, 50000, 'Drone'), (28, 50000, 'Drone'), (56, 50000, 'Drone'), (88, 50000, 'Drone'); Go -- Checkpoint the database to clear active portion of log since db is in simple recovery CheckPoint; -- Check the entries in the log -- Should show just entries for the Checkpoint DBCC Log('TestUpdate'); Go -- Execute update query that updates 4 records Update dbo.Employees Set PayRate = PayRate * 1.5 Where JobRole = 'Manager'; Go -- Check the entries in the log -- Number of entries may vary due to things that occur in the background -- Find the block of entries that begin with a LOP_BEGIN_XACT operation -- followed by 4 LOP_MODIFY_ROW operations and finally ending with a -- LOP_COMMIT_XACT operation, all with the same Transaction ID DBCC Log('TestUpdate'); /* Entries will look something like this: Current LSN Operation Context Transaction ID LogBlockGeneration ----------------------- ---------------- ---------------- -------------- -------------------- 0000001e:0000008f:0001 LOP_BEGIN_XACT LCX_NULL 0000:000002fe 0 0000001e:0000008f:0002 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000002fe 0 0000001e:0000008f:0003 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000002fe 0 0000001e:0000008f:0004 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000002fe 0 0000001e:0000008f:0005 LOP_MODIFY_ROW LCX_CLUSTERED 0000:000002fe 0 0000001e:0000008f:0006 LOP_COMMIT_XACT LCX_NULL 0000:000002fe 0 */ -- A little more detail of the log entries DBCC Log('TestUpdate', 3); Go -- Cleanup (drop) the test database Use master; Drop Database TestUpdate; Go
Final Reply
Thank you very much.
You made it clear when you mentioned “So when it is redone, instead of re-executing the update query, it performs the resulting changes to the specific rows”. This is the part I was looking for since the literature or BOL dont specifically spell it out and the meaning is hidden between the lines.
I also tested your Script and looked at log entries. They all make sense now.
I assume both Mirroring and Log shipping also replay log records.
Thanks again
SQLSoldier
Thanks to Paul White for catching a mistake in the post. Post corrected.
Does a Mirroring Failover Clear the Procedure Cache? | SQLSoldier
[…] understanding how mirroring processes the transactions. I addressed some of this in a post called What Does Redone Mean? back in December. Crash recovery and database mirroring work very similar in this case. Mirroring […]
DanP
Excellent post
Pingback
http://madebysql.blogspot.com/2011/07/is-of-index-fragmentation-in-log.html
Thanks,
DanP
http://madebysql.blogspot.com/
SQLSoldier
Thanks Dan!