ACID Properties Question

  • vincyf1

    Say Hey Kid

    Points: 694

    Comments posted to this topic are about the item ACID Properties Question

  • Hany Helmy

    SSChampion

    Points: 13488

    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.

  • Sean Pearce

    SSCoach

    Points: 15750

    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.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Carlo Romagnano

    SSC-Insane

    Points: 22025

    Fantastic!

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sqlnaive

    SSCoach

    Points: 17435

    Very good question. 🙂

  • baabhu

    SSCertifiable

    Points: 6202

    Good Question.

  • martin.whitton

    SSCrazy

    Points: 2577

    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

  • Nick Doyle

    SSC Eights!

    Points: 926

    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. 🙂

  • Bangla

    Hall of Fame

    Points: 3137

    Nice one.....thnx

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • Ken Wymore

    SSCoach

    Points: 16668

    Nice question about fundamentals. Thanks!

  • SQLRNNR

    SSC Guru

    Points: 281252

    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 35 total)

You must be logged in to reply to this topic. Login to reply