SQL Server IO - Continued... | SansSQL

Sunday, January 6, 2013

SQL Server IO - Continued...

Now that we understand what is "Transaction" and "ACID properties" of transaction... lets get moving on the next set...Write Ahead Log (WAL) :O

What!?... How is WAL related to ACID properties or transaction? Yes, they are related.

WAL is technique which helps to adhere to two of the four ACID properties "Atomicity" and "Durability". Now that we understand what is atomicity and durability... next is to understand "How WAL works" and also we need to peep into the advantages we get from it.

Let me just put my words as per SQL Books online which is very much easier to understand:
Write-ahead log (WAL) guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it is important for us to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification needs to be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

At the time when modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

I hope the above is very much clear in its explanation...

And this is it for now...

No comments:

Post a Comment