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