Suppose I have a table that saves plane ticket purchases. I need to keep track that I don't oversell so when a user purchases a ticket it needs to be stored in that table and other customers need to be aware of this.

Now suppose that this table may be queried and written to too often due to some promotion I'm running. In order to not overload the connections to the DB and get errors due to timeouts, I choose to put the DB in an elastic SQL server by some provider (e.g. Azure Elastic DB).

But if a user is connected to one instance of this DB and writes to it, how do the rest (copies) of the DB know about it and prevent any other customer from inserting in it violating this constraint I have? Is there some pattern I should be using? Do the copies communicate with each other somehow?

And if this setting actually can't solve this problem, what patterns can be used to solve an issue like this?


  • 1
    keep track that I don't oversell as a side track, most real airlines actually deliberately oversell their seats. The expectation here is that in most flights some passengers will actually be a no-show, and in some high frequency routes, the airline usually can reschedule the passenger to the next flight if they ended up with more flyers than seats for a particular flight.
    – Lie Ryan
    Commented Mar 29, 2020 at 2:19

3 Answers 3


The basic tradeoff of distributed system is that if you increase the number of write replicas that is needed to be updated for commitment, you improve reliability, but increase latency. On the flip side, if you want to reduce latency, you should reduce the amount of write replicas needed to reach consensus.

Distributed databases can be configured in many different ways. The most simplest way is probably single master, multiple slaves configuration. In a single master, multiple slaves configuration, you don't really have a distributed writes, all writes goes to the master, who is the final arbiter. The slaves, also known as read replicas, simply synchronises their data periodically (periodic replication) or sometimes in real time (streaming replication). The slaves only work as a read only replicas to distribute work for read-only queries and so they're usually allowed to be slightly out of date. So as long as all the necessary conditions are rechecked against the master database in the final commitment transaction, it's ok for the read/non commitment operations to work off read replicas. Single master-multiple slaves are very commonly used because they're simple to work with, you can mostly treat it just like non-distributed database.

Another common way is multi-master configuration. In a multi-master configuration, writes need to successfully pass certain threshold of the number of masters that committed the transaction, for the entire distributed transaction to reach a consensus. In a multi master configuration where the number of write replicas is less than the number of masters, reads in a transaction will actually also have to be distributed, each master may have to check with each other to find if another master have a newer copy of the data. In most cases, multi master setup generally degrades latency. Multi master configuration main advantage is to improve availability/reliability in case of data/machine loss, and not performance or scalability. Multi master configuration may also be used when there are multiple parties that want to co-operate but don't fully trust each other to maintain the golden copy, so each parties may hold their own master copy, and distributed consensus is required for the system to proceed with a transaction.

A third technique for distributed database is called sharding. Sharding is a technique where you have multiple master databases, but you only need to write to a single database server to reach commitment. Instead of trying to reach a distributed consensus, a sharded database distributes writes by deciding which database is considered as the master for the current operation by looking at the sharding key attribute of the operation (for example, if you use the flight number as sharding key and you have two masters, you may decide that all writes whose Flight number starts with A-M will be decided by Server A, and all writes for flight numbers that starts with N-Z will be decided by Server B). The drawback of sharded system is that you can't really have an operation that simultaneously requires commitments by data in different shards (that would require a consensus).

It's also possible to combine read replicas, multi-master, and sharding. In some circumstances, it's also possible to have a system that can elect one of the slaves to become a new master if the system lost a master. There are various considerations to make with such hybrid configurations, as they combine both the advantages and disadvantages of the schemes, so they need to be very carefully considered.

Which ones to use for your particular scenario depends on the precise reason you want to scale out.

  • Thank you for that. Considering my scenario, I was thinking if sharding would work: I would divide this "Reservations" table depending on the flight number. Now, in order to know the capacity of the flight, which depends on the plane model in my problem, would I be able to replicate the table that holds that information (call it FlightSeats or whatever) in each shard? Do DB engines allow that? Thank you
    – Heathcliff
    Commented Mar 29, 2020 at 18:41
  • @Heathcliff Most databases support the notion of partitions where you can define criteria to break the table apart based on the contents of a column (maybe more)
    – Kain0_0
    Commented Apr 2, 2020 at 3:56

You can:

  • Distribute your inventory across your nodes — the airlines do this, they give out some seats on each plane to code share partners or travel agents so they can operate somewhat independently

  • Use a compensation strategy — if the flight is oversold, your compensate some travelers to take a later not full flight (airlines have complex models that inform the level of overselling they can tolerate, since overselling costs, but empty seats cost as well)

  • Shard your flights to different nodes/databases — it is unlikely that all your traffic is interested in the exact same flight: if the reason to scale out is to handle the total volume of traffic across all flights being booked

  • Combine some of the above


Cassandra(probably other distributed databases) have configurable consistency levels for both reads and writes that allow you to choose availability versus data accuracy. The idea behind is CAP theorem.

CAP theorem states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees;

  • Consistency: Every read receives the most recent write or an error
  • Availability: Every request receives a (non-error) response, without the guarantee that it contains the most recent write
  • Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes

And you can't we give up partition tolerance under the CAP theorem

You may need to design your data models to prevent eventual consistency. Let's say you can serve your tickets in single instance Redis(supports different data structures such as sets) and keep track of the ticket histories in a more distributed database technology.

Please check the link about CAP theorem

For Cassandra quorum documentation

Your Answer

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

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