not uncommon, and in fact people encounter this delay
between a transaction and their running balance regularly
(e.g., ATM withdrawals and cellphone calls).
How the SQL statements are modified to relax consistency depends upon how the running balances are
defined. If they are simply estimates, meaning that some
transactions can be missed, the changes are quite simple,
as shown in figure 4.
We’ve now decoupled the updates to the user and
transaction tables. Consistency between the tables is not
guaranteed. In fact, a failure between the first and second
transaction will result in the user table being permanently
inconsistent, but if the contract stipulates that the running totals are estimates, this may be adequate.
What if estimates are not acceptable, though? How
can you still decouple the user and transaction updates?
Introducing a persistent
message queue solves the
problem. There are several
choices for implement-
ing persistent messages.
The most critical factor in
implementing the queue,
however, is ensuring that
the backing persistence is
on the same resource as the
database. This is necessary
to allow the queue to be
transactionally committed
without involving a 2PC.
Now the SQL operations
look a bit different, as
shown in figure 5.
This example takes
some liberties with syntax
and oversimplifying the
logic to illustrate the
concept. By queuing a
persistent message within
the same transaction as
the insert, the information
needed to update the run-
ning balances on the user
h as been captured. The
t ransaction is contained on
a single database instance
FIG 5 a nd therefore will not
i mpact system availability.
A separate message-
p rocessing component will
AN ACID ALTERNATIVE
are updated. Using an ACID-style transaction, the SQL
would be as shown in figure 3.
The total bought and sold columns in the user table
can be considered a cache of the transaction table. It is
present for efficiency of the system. Given this, the constraint on consistency could be relaxed. The buyer and
seller expectations can be set so their running balances do
not reflect the result of a transaction immediately. This is
B egin transaction
Insert into transaction(id, seller_id, buyer_id, amount);
E nd transaction
B egin transaction
Update user set amt_sold=amt_sold+$amount where id=$seller_id;
Update user set amt_bought=amount_bought+$amount
where id=$buyer_id;
End transaction
FIG 4
Begin transaction
Insert into transaction(id, seller_id, buyer_id, amount);
Queue message “update user(“seller”, seller_id, amount)”;
Queue message “update user(“buyer”, buyer_id, amount)”;
End transaction
For each message in queue
Begin transaction
Dequeue message
If message.balance == “seller”
Update user set amt_sold=amt_sold + message.amount
where id=message.id;
Else
Update user set amt_bought=amt_bought + message.amount
where id=message.id;
End if
End transaction
