March 14, 2008 at 4:10 am
Hi all,
Not a problem I am having as such, more of a question that's bugging me.
When SQL Server restarts after a shutdown, it rolls back any transactions open at the time of the shutdown. This is what is documented, and this is what you would see in the SS error log.
But what I am struggling with is ..... what is SQL Server rolling back?? Uncommitted transactions are never written to the data files, are they? A CHECKPOINT only forces completed transactions to disk as I have always understood it. Surely uncomitted transactions are only ever held in the buffer cache, so if there are any of them at the time of SQL Server shutting down, surely these are just lost. When SQL Server restarts, it doesn't have to go throw the transaction log and do the opposite action in the database, because the original action didn't actually get done in the database (data files).
So is the "roll back" a kind of logical operation just to inform the administrator (via the error log) that there were uncommitted transactions in database X at the time of shutdown and they have been lost?
If anyone can help clarify my understanding here I would be grateful.
Many thanks.
James Manly
SQL Server DBA
March 14, 2008 at 5:55 am
Rollback rolls back uncommitted modifications - undo in the database. Even if whole transaction was help in a buffer, the whole buffer must be written, which does not happen instantly.
It works something like this:
1. updates go to log file
2. updates go to database file
3. transaction is marked as committed in log file
COMMIT can happen between #1 and #2 or #2 and #3, depending on isolation mode, size of transaction,... but data is really committed after #3.
On dirty read isolation level, the other connections may read uncommitted data, so this data is either in buffers or in data file.
So a rollback involves:
1.check log file
2.undo changes in data file if needed
3.drop transaction data.
Pure guesswork, I don't know how it really works.
March 14, 2008 at 7:20 am
Its possible for the database to be in the middle of transaction when the server restarts.
All actions of a single transaction are logged to the log file but to conform with ACID criteria
Unless the whole transaction completes and the transaction is committed every statement has to rolled back. Which is what you see when SQL restarts Uncommited or incomplete transaction being rolled back.
Gethyn Elliswww.gethynellis.com
March 14, 2008 at 7:30 am
With large updates - the "committing to disk" part of the process can actually take some time.
Also some operations like truncate happen on the disk and those data pages just stay locked until the transaction is committed.
So - there may be occasion to have to reverse stuff that has in fact happened on disk.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 11:30 am
jmanly (3/14/2008)
Uncommitted transactions are never written to the data files, are they?
They can be. Imagine an update that affects 2 GB of data on a server where SQL's limited to 1 GB of memory.
SQL enforced that log records are written before data gets flushed to disk, but those log records are the ones for the operation, not the commit. Checkpoint writes dirty pages to disk, possibly including ones that are still in the middle of a transaction. The pages will have the LSN of the operation that changed them, so it's possible to know that a page was affected by a transaction that didn't complete.
The roll back reads through the log from (I believe the beginning of the active portion of the log) and identifies transactions that didn't complete and noted the page IDs of the pages affected by those operations. Then SQL will go and see if the page was written to disk after the tran began. If so, it uses the log records to undo the changes.
I've had a case where a recovery rollback to 2 hours to roll back 7 transactions. There was significant disk, memory and CPU activity during that time, so, no, it isn't just a logical operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2008 at 3:38 am
Many thanks for this. It makes sense now, especially when considering the limited memory example.
Cheers.
James
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply