What are some approaches used in financial applications to prevent deposit/withdrawal sync problems?

Question

I am currently facing a problem with an application that has deposit/withdraw functionality.

The underlying database (Cassandra) offers no read/write locks.

Now suppose user A has deposited 100$ in his account, He can use this credit to send 100 emails.

Imagine the following scenario:

1. User A opens two different tabs, t1 and t2, and points them the page for sending emails.

2. He fills in the fields on both tabs to send 100 emails and hits submit at almost the same time.

Now this happens in the background.

Time 0: t1 checks if the user has enough credit for sending 100 emails? True. He does.

Time 0.01: t2 checks if the user has enough credit for sending 100 emails? True. He does. (t1 has not updated the value of user's credit) .

Time 0.02: t1 updates the credit to 0 and goes on to sending 100 emails.

Time 0.03: t2 updates the credit to 0 and goes on to sending 100 emails.

The malicious user has now sent 200 emails while he had enough credits for sending only 100.

One approach would be to do a subtraction action instead of updating, in that case t2 would set the user's credit field to -100, but the user has already overspent his credit.

So, Here are my questions:

1. What is the academic or more technical name of this problem? Race condition? Syncing problem?
2. What are some approaches to prevent this from happening? This could happen anywhere where the database is distributed, like Amazon product inventories, or Visa Card.
   How do they manage this situation that in peak times when two customers may purchase an item at the same time they don't oversell? or in the case of Visa, how do they prevent someone from withdrawing more than account's balance by copying his physical card and using it on two very distant ATMs? (thus increasing the probability that he'll hit two different database nodes that are not synced in real time because of the geographical distance.)

I'm very confused on this one,
Any help is so much appreciated.

Answer 1

Transactions

A transaction wraps all of the required steps for a particular business operation and guarantees that either all of the steps succeed or they all rollback to the original state in the database before the transaction was started.

Further Reading
How are Cassandra transactions different from RDBMS transactions?

Answer 2

Locks and transactions are not really necessary to deal with such simple cases.

You can combine the checking and the update in a single step (or statement in the SQL jargon), guaranteeing an atomic operation:

update customer set credit = credit - 100 
where idcustomer = {put the customer id here} 
and credit >= 100

When the above operation fails you know the state is inconsistent (the customer is invalid or he doesn't have enough credit) so you can act accordingly.

Answer 3

Consistency

With Cassandra or generally and distributed database this is a problem of 'consistency' and 'eventual consistency'. With eventual consistency the accuracy, or reads going out of your db, will 'eventually' be consistent (accurate) with the transactions that go into it, but it is not guaranteed.

Consistency is part of the CAP Theorem of Consistency, Availability, and Partitioning. With Cassandra DB you are essentially trading some consistency for high availability. It's a trade off when trying to get fast writes/reads between multiple distributes stores of the same data, and also reaching consensus to some degree.

Cassandra doesn't have the concept of transactions with rollbacks but instead it does offer you two different modes of consistency. 1. tuneable 'strong' consistency. 2. linearizable consistency using 'lightweight transactions'. [1] [2]

You could also mix this with web-session level checks and guarantees to complement the nature of your database consistency model. i.e. only allow one user session at a time. Use session-level semaphores to your db. But it comes with it's own considerations.

Answer 4

The term you're looking for is "consistency" (or lack of).

For real enterprise SW, you would need several patterns to fix this

• Transactions (plain old transactions).

• Use either Atomic Updates, or Append-Only database.

  • By atomic updates, I refer going beyond of what a transaction can do already: you could read and update (or update and read) the data atomically within the same operation. Some DBMS already support transaction isolation levels that sort of support this.

  • By append-only, that's a whole different animal. Your DB should only insert operations such as "withdraw" or "deposit" and, everytime you need to get the current balance, you must sum all records to obtain it. This relies also in the assumption you have good transactions.

• At last, you should check into Compensating Transaction Pattern. That pattern is precisely meant to handle distributed operations that cannot afford or cannot use consistently distributed transactions. In general, the pattern requires that, for every operation, you also append the "undo operation" in a log. If the bulk operation fails, you would need to run all the "undo log" to reestablish the state.

  • This is what banks use in case a race-condition causes a fault state (such a negative balance). As a matter of fact, savings accounts always have an implicit credit (unknown to most people) that is used in case an user could "beat the system" and was planning to get away with free money.