2
$\begingroup$

This is an overall system design and DBMS question:

If the user books a ticket or an item on a website, how do we solve the issue of confirming a ticket is sold to the user, but the main Database System breaks down, and the secondary DMBS doesn't have that information yet?

Is the most common solution just to not commit the transaction until both the master and slave DBMS has confirmed?

$\endgroup$

2 Answers 2

1
$\begingroup$

Generally speaking, things like orders are not confirmed until some trace of them is recorded in the database.

Database disks are usually mirrored at the hardware level (with transactional guarantees of mirroring provided by the disk array controller), and the database may be completely replicated across machines or physical sites.

The process of placing an order like a consumer web order is rarely a one-shot process. There is the establishment of an account, then an order, then payment (often via a third party computer system), before the customer is told that their order is actually lodged.

It's actually quite hard to imagine plausible circumstances in which no recoverable trace is left either of an order itself, or something that can identify customers who may have placed an order immediately prior to a failure (who can then be put on notice that something may have been lost).

Obviously there is the theory that if all database infrastructure is totally wiped out, then the order and all its traces must be lost, even after confirmation occurred. But you're talking about a TV-newsworthy catastrophe, like a nuclear blast with extensive physical destruction that utterly carries away all the machinery in many buildings.

There are far more common risks to orders than this, including erroneous computer logic that erases data, "fat finger" operators, or just a delivery package lost in the post.

The bottom line is that common techniques exist to ensure that records are guaranteed as long as the hardware is guaranteed, and the hardware can easily be guaranteed to a level that overwhelms the reliability of other aspects of any system which distributes goods or services.

$\endgroup$
0
$\begingroup$

There is no single answer that is always used; there is a tradeoff between consistency, availability, and ability to handle failures. Most databases ensure that the data is stored persistently on disk before reporting that the transaction is confirmed, so even if the main database fails, if it has accepted the data, then the data will be there on disk.

Of course you could avoid committing until both replicas have confirmed, but then the system would not be available if either replica cannot be reached; or you could continue as soon as the master has accepted the data, but then the secondary replica could become out of sync. I suggest learning about the CAP theorem and fundamental tradeoffs in database systems.

There will always be some failures that we cannot or do not prevent through technical means. So, they might be addressed by ad-hoc methods (e.g., the customer could call customer support).

$\endgroup$
6
  • $\begingroup$ thank you. about "even if the main database fails, if it has accepted the data, then the data will be there on disk." but what if after the commit, it is a disk failure on the master DBMS, before the slave DBMS can get the data? $\endgroup$ Feb 23, 2021 at 17:15
  • $\begingroup$ @eulerIdentity, "There will always be some failures that..." $\endgroup$
    – D.W.
    Feb 23, 2021 at 22:53
  • $\begingroup$ yes... what if we can make the probability a lot lower (remove the single point of failure)... so if the Master DBMS fails once after 5 years, then if the Secondary can kick in, it can improve the probability of success from failing every 5 years to perhaps failing every 20 or 30 years, if both the Master and Secondary fail at the same time $\endgroup$ Feb 25, 2021 at 6:05
  • $\begingroup$ @eulerIdentity, I'm not sure what to say. There are multiple possible designs, with different tradeoffs - e.g., regarding likelihood of failure and other properties. $\endgroup$
    – D.W.
    Feb 25, 2021 at 6:07
  • $\begingroup$ I mean... that's the total point of having a backup of a computer too. We won't say, "nah, let's not do any backup, because there always can be some failures..." $\endgroup$ Feb 25, 2021 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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