Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Typically, DB engine fulfils some request and any modifications must stay persistent (you call it durability). How is the persistence is ensured/assumed? Which requirements are imposed on the HW/OS?


When does the server does the flush? Can it happen that OS flush command propagates into some intermediate buffer and does not propagate down to the HDD plates? Today, many storage manufacturers and interfaces support out-of-order write buffers. What are implications?: Should you take any action to counteract it? Does ram buffer on disk matter? Are any UPS (and which) required to support the RAM buffer?

What should I guarantee if I have my own "DB" - storing my data in plain files? I assume that DB does the best. What is that best so that I could do the same to guarantee that data is not lost? Is there a simple procedure? Are there any courses that teach the safe storage?

share|improve this question

closed as too broad by Mark Storey-Smith, Kin, Shawn Melton, Paul White, Jack Douglas Dec 23 '13 at 10:28

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

Thanks for the question Val, I'm afraid it is too broad for this site which is all about specific expert-level questions that can be answered without writing a whole book :) – Jack Douglas Dec 23 '13 at 10:33
up vote 6 down vote accepted

You didn't mention the Database platform, I can give insight on SQL Server.

SQL Server ensures durability with a concept called WAL. Write ahead logging. This means that all changes are first written to log before the are applied tot the data files.

When a row needs to be altered. The corresponding data (and possibly index) pages get fetched from disk into the buffer pool (in memory).

In memory, the page(s) get updated, the page is then marked as dirty. Done. But what happens when you have a power outage? The memory is cleared right?

Why isn't the page then written to disk immediately? Because this could give major disk contention. Imagine that you have a page with a couple of hundred rows on it. It would create a lot of disk IOs if for every row update the page is written to disk.

Therefore a different mechanism is used. A mechanism where it's no longer necessary to write data pages to disk every time a single update has taken place on that page.

Whenever a row is updated, the change along with the information to undo the change is first written to a log file. Once that is done, the client that issued the change is given an acknowledgement that the change is in fact written to disk (hardened) and that the client can continue with the next statement.

This way, when you have a power outage, after which the database server starts again, all finished (committed) changes can be read from the log file and still be applied and all unfinished or rolled back changes can be undone.

Eventually it would be nice if all those data pages that are dirty and hanging in memory are applied to the data file right? For starters you don’t want days changes in log files that need re-applying after a server restart. Therefore the dirty data pages are occasionally flushed to the data file(s). This is called a checkpoint. The interval on which checkpoints occur is based on the time it would take to recover the database after a restart. (The time it would take to read all the changes in the log file and apply them to the data file (s). But key to understand is that data pages and whether or not they are written to disk are not important for durability.

Back to the log file. To make sure that once SQL Server gives a log file write IO request to the OS and the OS gives back acknowledgement that the IO is handled it is in fact really hardened to disk and not hanging around in OS cache, the log file is created with the following flag: FILE_FLAG_WRITE_THROUGH. This tells the OS NOT to cache any write IOs that are going to this file.

-- update

Atomicity is achieved by the fact that your changes are all wrapped in transactions. (Either implicitly or explicitly). So when you update a record you have a minimum of 3 log records:




Let’s for simplicity sake assume that these 3 records are send to disk in 3 separate IOs. The durability rule dictates that the client can only get a acknowledgment if 1, 2 and 3 are hardened to disk.

Atomicity rules dictates that either the complete transaction is applied or none of the steps of the transaction. So in the above example, if you have a failure after IO #2 has been hardened to disk. After a reboot the database server will have a look in the transaction log. All transaction records that are in the transaction log and have not been applied to the data file AND have a final COMMIT TRAN record will be applied to the data file. However, for the above transaction, the database server will not find a COMMIT TRAN record in the log file. You could have to scenario’s now.

  1. SQL Server looks in the data file to find out if UPDATE RECORD X has been applied to the data file. (1 possibility could be that you had a checkpoint right after log record 2 was generated and just before your crash.) In this case, since the database server can’t find a commit record, it assumes that the whole transaction most be rolled back. It will undo the change. Since your client application never got a acknowledgement that the transaction was committed. Your now back in a consistent state. The application must retry the transaction.

  2. SQL Server looks in the data file and find out that nothing of this transaction was every applied to the data file yet. It will just disregard these log records.

--END Update

Most modern hardware that handles IOs have mechanisms that handle power outage. For example controller card cache is backed up with batteries, SANs have the same principles. The only think to keep in mind is that even if they do have these mechanisms, if any of your hardware allows you to continue operation even if the battery is not present or is failing that you implement proper error event monitoring and take action accordingly.

The transaction Log manager sends the IOs to the OS in guaranteed order. Once a hardware appliance tells the OS that the IO is taken care of, it in fact means that the IO from that moment on is either in battery backed cache or is in fact on disk. In other words: the IO is reproducable after a power outage. The IO hardware appliance might write IOs to physical disk in a different order for optimization purposes. But this is no longer important for us.

IO ordering is something of concern when you implement SAN level mirroring. But that is another topic.

What do you mean with your own DB? You are going to develop your own RDBMS? At a minimum you need these 3 core concepts:

  1. Only acknowledge to the client application that the transaction is in fact commited when either a log records is written to disk that can fully reproduce the change or the change itself is written to disk. (If you don't want to implement something like WAL)

  2. For hardening written to files, implement FILE_FLAG_WRITE_THROUGH flag on file initialization. To make sure the windows OS doesn’t cache.

  3. Use hardware that has battery backed cache. And monitor on battery problems

Detailed SQL Server information can be found in the SQL Server I/O White Papers.

More information about ACID. Atomocity, Consistency, Isolation and Durability here

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.