<aside> 💡 On-line Transaction Processing (OLTP)

Short-lived read/write txns.

Small footprint.

Repetitive operations.

</aside>

<aside> 💡 On-line Analytical Processing (OLAP)

Long-running, read-only queries.

Complex joins.

Exploratory queries.

</aside>

Assumption

We can assume that all nodes in a distributed DBMS are well-behaved and under the same administrative domain.

Do not trust the other nodes → Byzantine Fault Tolerant protocol for txns (blockchain).

1. Atomic Commit Protocols

When a multi-node txn finishes, the DBMS needs to ask all the nodes involved whether it is safe to commit

• Two-Phase Commit (Common)

• Three-Phase Commit (Uncommon)

• Paxos (Common)

• Raft (Common)

• ZAB (Apache Zookeeper)

• Viewstamped Replication (first probably correct protocol)

1.1 Two-Phase Commit

Widely used for distributed DBMSs that are local to each other

• Either everyone commits or no one does.
• The coordinator can also be a participant in the system.

Phase 1

1. The client sends a Commit Request to the coordinator.

2. The coordinator sends a Prepare message, asking the participant nodes if the current txn is allowed to commit.

   1. If a given participant verifies that the given txn is valid, they send an OK.

3. If the coordinator receives an OK from all the participants

   1. the system can now go into the second phase in the protocol.

4. If anyone sends an Abort to the coordinator:

   1. the coordinator sends an Abort to the participants, to which they should respond to with an OK.
   2. Then the coordinator sends an Aborted to the client.

Abort

Phase 2

1. The coordinator sends a Commit to all the participants
2. Once the participants respond with an OK, the coordinator can tell the client that the txn is committed.

Success