September 4, 2013 at 9:18 am
Good question - thank you. It made me go back "a few" years and think about something that most of us take for granted now.
September 4, 2013 at 12:07 pm
All - Thanks so much 🙂
September 4, 2013 at 12:09 pm
Good Vinay ,
Keep Rocking 😛
SwapLaksh
September 5, 2013 at 1:18 am
Brilliant question. We need more questions like this which tests the basics.
M&M
September 7, 2013 at 4:29 am
martin.whitton (9/4/2013)
For the clearest possible confirmation of this answer, see http://support.microsoft.com/kb/967576 which states:SQL Server maintains the Atomicity, Consistency, Isolation and Durability (ACID) property by using the Write-Ahead Logging (WAL) protocol.
Thank you for a good question which has taught me something new.
That should be the explanation for this question!
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 7, 2013 at 4:56 am
L' Eomot Inversé (9/4/2013)
Excellent question, correct answer and explanation. This is something where people can easily go wrong, as some of the MS documentation is a bit confused.Hany Helmy (9/3/2013)
I have to disagree with this answer as per MSDN:"Transactions Durable: After the database engine acknowledges that a transaction has been committed, its changes are persistent in the database"; so we are talking here about Auto-Commit Transaction.
http://msdn.microsoft.com/en-us/windows/desktop/gg269197(v=exchg.10).aspx
Also this: "Log records are written to disk when the transactions are committed" from your own reference in the answer; so Commit transactions first then Logging.
It doesn't say "the only time log records are flushed to disc is during the commit process"; rather obviously, if that were true the server would be unable to roll back partially complete transactions on restart after a failure (or it wouldn't need to, because all transactions had to keep all their data in RAM until commit time, so there could never be dirty pages on the disc - that would produce some pretty unpleasant effects on system limits).
Log records will be written to disc when any of the following things happen:
1) the lazy writer process asks for a dirty page to be flushed from the cache to disc and there are unflushed log records which contain undo information for that page. (I think SQL server doesn't bother to distinguish undo information from roll forward information so far as deciding to flush log records on page flush is concerned; in theory WAL but doesn't require the distinction to be made for that purpose.)
2) the checkpoint process asks for a dirty page to be flushed from cache to disc and there are unflushed log records which contain undo information for that page (same comment applies about the distinction.)
3) a user issues a commit command or a the server issues one automatically: here a commit record is added to the log cache before the flush; when the flush is complete, locks for the committing transaction are dropped and the commit is complete.
4) a rollback command is issued
5) a rollback is completed (this is not essential for durability, but it helps server startup performance)
6) initial (server startup) recovery is completed (this is not essential for durability, but it can help startup performance)
7) also at some points in backup, I think.
On a system running long transactions under really heavy store pressure, most log records will get to disc before the commit point of the transaction that caused them.
Thank you Tom, I guess I was missing some point there, but I got it later.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 7, 2013 at 5:55 am
Nice to review my memory 🙂
September 8, 2013 at 3:55 pm
Very nice question!
Thanks
Igor Micev,My blog: www.igormicev.com
October 3, 2013 at 3:29 am
nice thanks
January 21, 2014 at 11:58 pm
ACID property can be allowed for TempDB ?
January 22, 2014 at 1:55 am
bandarimohan 57273 (1/21/2014)
ACID property can be allowed for TempDB ?
I don't know where is that property, but D-Durability cannot be guarantied for tempdb.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
January 24, 2014 at 10:41 am
IgorMi (1/22/2014)
bandarimohan 57273 (1/21/2014)
ACID property can be allowed for TempDB ?I don't know where is that property, but D-Durability cannot be guarantied for tempdb.
Regards,
IgorMi
I don't see why not - durability doesn't imply unbounded duration, the values in an ordinary user database are transient, durable only until altered or deleted by a DML statement in a new committed transaction or destroyed by some other action (eg DDL dropping or truncating the table or dropping the database) intended to destroy, delete, or alter them. The only difference for records in user tables in tempdb is that the definition of actions which destroy them includes DBMS startup (or is it DBMS closedown?) - they are still durable up to the point where some action (whose function explicitly includes altering, deleting, or destroying these values) alters, deletes or destroys them.
Tom
January 24, 2014 at 11:59 am
All definitions of the D in ACID that I found specifically mention that the information survives "even in the case of a system crash". (Which makes sense; information not spontaneously disappearing when the system is still up and running is such a basic expectation that there's no need to define a term and abbreviation for it).
Tempdb is specifically designed to ensure that information will NOT survive a system crash.
January 25, 2014 at 3:33 am
L' Eomot Inversé (1/24/2014)
IgorMi (1/22/2014)
bandarimohan 57273 (1/21/2014)
ACID property can be allowed for TempDB ?I don't know where is that property, but D-Durability cannot be guarantied for tempdb.
Regards,
IgorMi
I don't see why not - durability doesn't imply unbounded duration, the values in an ordinary user database are transient, durable only until altered or deleted by a DML statement in a new committed transaction or destroyed by some other action (eg DDL dropping or truncating the table or dropping the database) intended to destroy, delete, or alter them. The only difference for records in user tables in tempdb is that the definition of actions which destroy them includes DBMS startup (or is it DBMS closedown?) - they are still durable up to the point where some action (whose function explicitly includes altering, deleting, or destroying these values) alters, deletes or destroys them.
Good reasoning of you. I agree about the actions which destroy the tempdb objects.
Only up to the point, and as long as the tempdb is alive, it can be said that the durability is guarantied.
Additionally the tempdb supports only Simple recovery model and cannot be backup, if in case someone try to make a workaround (but impossible).
I made my comment based on some knowledge from faculty - which is somehow the following:
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure. - http://technet.microsoft.com/en-us/library/aa213068(v=sql.80).aspx
Igor Micev,My blog: www.igormicev.com
January 25, 2014 at 2:00 pm
Hugo Kornelis (1/24/2014)
Tempdb is specifically designed to ensure that information will NOT survive a system crash.
Yes, it is - and that means that the ability of a temporary table to survive commitment of the transaction in which it was created (and be used in a new transaction) is a violation of the D, as is the ability to place user tables other than temporary tables in tempdb. If tempdb couldn't hold non-temporary tables, and temporary tables were defined to have the scope of the transaction in which they were created, the fact that stuff in tempdb doesn't survive system start would not have any relevance to the D property of transactions. Defining temp tables in tempdb as having the scope of a client session, and allowing non-temp tables in tempdb, are probably bad things (and globally visible temp tables probably makes it even worse).
Tom
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply