Databases & SQL Practice Question 10: SQL NoSQL Atomic Transfer
Databases & SQL · Test 1 · question 10 of 10
A team is building a wallet service. A transfer debits one account and credits another. If the server crashes between those two steps, the system must never be left with only one of them applied. The finance team also runs ad-hoc reports that join accounts, transfers and customers.
Which design meets both requirements?
Show answer and explanation
Answer:
The crash requirement is atomicity, the A in ACID. A transaction groups several steps into one all-or-nothing unit: if anything fails before COMMIT, none of the steps take effect, and once COMMIT succeeds the result is durable. Ad-hoc reports that join several entities are also the relational model's strength, because SQL can join any tables without the access paths being designed in advance. Option B meets both requirements.
Option A gives up exactly the guarantee you need. Two separate puts are two independent operations, so a crash between them leaves one balance changed. Eventual consistency is a trade-off for availability and scale, and key-value stores are not built for ad-hoc joins.
Option C has the same flaw. Two independent writes are not atomic as a pair. Some document databases do offer multi-document transactions, but this option does not use one, and ad-hoc joins across collections are still harder than in SQL.
Option D is the trap. It uses the right kind of database the wrong way. In autocommit mode each statement is its own transaction, so a crash after the debit commits and before the credit runs loses the money. Choosing a relational database is not enough: the two statements have to be in the same transaction.
Reference: PostgreSQL: Transactions (tutorial)