Computer Science Stack Exchange is a question and answer site for students, researchers and practitioners of computer science. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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).
