ACID Properties Question
-
Comments posted to this topic are about the item ACID Properties Question
-
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.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
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.
No, logs are written to disk first and then the modifications are applied. This provides durability.
In computer science, write-ahead logging (WAL) is a family of techniques for providing atomicity and durability (two of the ACID properties) in database systems.
In a system using WAL, all modifications are written to a log before they are applied.
Good question for the day.
-
Fantastic!
-
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Very good question. 🙂
-
Good Question.
-
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.
-
This was removed by the editor as SPAM
-
Hany Helmy (9/3/2013)
I have to disagree with this answer as per MSDN:"After the database engine acknowledges that a transaction has been committed, its changes are persistent in the database";
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.
The log has to be written first to ensure durability. A transaction cannot complete before it's logged, or it's changes wouldn't necessarily be "durable" if the system crashed. The change could still be dirty and only in the buffer cache, which would be lost in a system crash. Transactions committing before logging would also create issues for crash recovery when it replays the log and rolls back transactions that aren't logged as committed.
Good question for early morning. 🙂
-
Nice one.....thnx
-
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.
Tom
-
Thanks for the ACID question. It's basic, but very important. With questions like this, I wonder if people miss it because they are "programmers" that have been pulled over to writing SQL code, never having the benefit of training in basic DBMS principles. I've seen too many people without DBMS knowledge turned loose to write SQL procedures that wouldn't know an entity from an attribute, or a log from a lock.
SET SOAPBOX_MODE OFF;
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Nice question about fundamentals. Thanks!
-
Thomas Abraham (9/4/2013)
Thanks for the ACID question. It's basic, but very important. With questions like this, I wonder if people miss it because they are "programmers" that have been pulled over to writing SQL code, never having the benefit of training in basic DBMS principles. I've seen too many people without DBMS knowledge turned loose to write SQL procedures that wouldn't know an entity from an attribute, or a log from a lock.SET SOAPBOX_MODE OFF;
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply