1

I was reading http://www.h2database.com/html/advanced.html#durability_problems and i found

Some databases claim they can guarantee durability, but such claims are wrong. A durability test was run against H2, HSQLDB, PostgreSQL, and Derby. All of those databases sometimes lose committed transactions. The test is included in the H2 download, see org.h2.test.poweroff.Test

Also it says

Where losing transactions is not acceptable, a laptop or UPS (uninterruptible power supply) should be used.

So is there any database that is durable. The document says about fsync() command and most hard drives do not obey fsync(). It also talks about no reliable way to flush hard drive buffers

So, is there a time after which a committed transaction becomes durable, so we can buy ups that gives minimum that much backup of power supply.

Also is there a way to know that a transaction committed is durable. Suppose we don't buy ups and after knowing that a transaction is durable we can show success message.

Shreyans jain
  • 559
  • 6
  • 18
  • @Igor You are right but is there a minimum time after which durability is gauranteed. – Shreyans jain Jul 12 '17 at 20:40
  • @Igor According to H2 author all durability claims are false, that is the reason for selecting database platform. – Shreyans jain Jul 12 '17 at 20:42
  • 2
    If you don't have hardware plus drivers that properly support durable writes, then there is no durability, no. And if you do (yes, those setups do exist), then your transaction is durable when your database system says it has been committed. How much time would you need on a UPS if your disk systems don't support durability? As much as you can get, I'd say, because why would you believe any claims of a specific time? Note that you have to take steps to shut down your database if a power loss event occurs, otherwise transactions will just keep piling on. – Jeroen Mostert Jul 12 '17 at 20:51
  • 1
    The H2 author is incorrect, true production DBMSs (SQLServer, Oracle, DB2), correctly configured, offer a very high degree of durability. I have done power-loss tests on SQL Server databases hundreds of times and have never once lost a transaction. – RBarryYoung Jul 12 '17 at 20:53
  • @JeroenMostert Is a normal desktop computer with HDD a setup that can support durable writes? Also time comment is correct and i got that. – Shreyans jain Jul 12 '17 at 20:56
  • 2
    A normal desktop computer with an HDD is geared towards maximum performance with no concern for reliability, so no, obviously that's *not* what you run your durable database system on. There's a reason enterprise disk systems come with a price tag (although that price tag is no guarantee in and of itself, unfortunately). – Jeroen Mostert Jul 12 '17 at 20:58
  • @JeroenMostert Can you tell a setup closet to the value of normal computer with HDD/SDD that can support durable writes. – Shreyans jain Jul 12 '17 at 21:05
  • No, I've never heard of such a strange beast. If durability is really important (as in I-store-my-bank-records-there important) someone will have the cash for durable storage that doesn't lie about having committed things. While you could conceivably build something like that out of commodity hardware (NAS with a UPS is a good bet, as mentioned in the answer) nobody will give you guarantees on just how durable that would be or how much juice your UPS needs. You'd have to do your own testing and sign off on it. – Jeroen Mostert Jul 12 '17 at 21:17
  • @JeroenMostert Thanks a lot :) – Shreyans jain Jul 12 '17 at 21:18

1 Answers1

1

The problem depends on whether or not you can instruct the HDD/SDD to commit transactions to durable media. If the mass storage device does not have the facility to flush to durable media, then no data storage system on top of it can be said to be truely durable.

There are plenty of NAS devices with built in UPS however and these seem to fit the requirement for durable media - if the database on a seperate server commits data to that device and does a checkpoint then the commits are flushed to the media. So long as the media survives a power outage then you can say its durable. The UPS on the NAS should be capable of issuing a controlled shutdown to its associated disk pack, guaranteeing permenance.

Alternatively you could use something like SQL Azure which writes commits to multiple (3) seperate database storage instances on different servers. Although we have no idea if those writes ever reach a permenant storage media, it doesnt actually matter - the measurement of durability is read-repeatability; and this seems to meet that requirement.

PhillipH
  • 6,182
  • 1
  • 15
  • 25