Getting a coherent view of the state of the data

Question

Here is a theoretical problem. It could apply to many systems. Let's assume RESTful Web services for example. You want to find out something but as it happens you can't get it in a single query. You can make multiple queries. For example, you have a joint bank account (or a company bank account) and you want to find out how much money is in there. Meanwhile someone else transfers money from checking to savings.

Checking:  $5,000   Savings:  $3,000

Me: Query Checking. Response: $5,000

 

Joe: Transfer $4,000 from Checking to Savings

Checking: $1,000   Savings:  $7,000

Me: Query Savings. Response: $7,000

Total $12,000.

How do you avoid anomalies like this? The example shows two accounts at one bank, but it can also happen with two accounts at two different banks.

Answer 1

You need some form of concurrency control to deal with problems like this.

Some possible solutions in your example:

1. Ensure that the service can return both Savings and Checking figures in a single query, perhaps as a Total.
2. Implement some kind of session so that the user requesting values can lock the data until she has finished reading all the values in which she is interested. This approach is often called "pessimistic concurrency control".
3. Design the service so that you can pass your previous Checking figure back when you request a Savings figure. If the Checking figure no longer matches its previous value, the service should indicate an error rather than return a value for Savings. This approach is a variation of "optomistic concurrency control".

Answer 2

Assuming this is a secure API you could only allow one user to view/edit an account at any given time. So the credentials might look like this:

    User: Joe
    Password: dog
    Account: Z123456X

Once the user logs in a timed lock is started that is reset with every query to the API. If they log out the lock is completely removed. If this is impossible then there really isn't much you can do. There is always going to be race conditions when dealing with data like this. The best you can do when someone queries the API is return the actual value as well as the pending value based of any pending transactions. This minimizes the chance of stale data.

    Actual: $5,000 
    Pending: $1,000

Answer 3

This is why there are processes to indicated cleared and pending activity. Just like you would have both the withdrawal and the deposit in the same transaction, the balances have to be coorinated and indicate that there are pending transfers, so all balances may not be available.