Database concurrency as simple as possible

In this article I'll show basic concepts for handling concurrent requests by building a banking web application. When coding there are some traps we need to pay attention specially because it's not a scenario easy to test.

Bank account application scope

Our example application will contain bank accounts and will enable money transfer from one account to another. It has been built using PHP, Symfony and Doctrine ORM, but you don't need to be familiar with these technologies, only with Postgres database.

Account entity

The bank account will store the name of the owner and the final amount.

CREATE TABLE "public"."bank_account" (
    "id" int4 NOT NULL,
    "name" varchar(255) NOT NULL,
    "amount" int4 NOT NULL,
    PRIMARY KEY ("id")
);

Money transfer API

The endpoint for transferring money between two accounts will receive 3 variables by query parameters:

• from: source account id
• to: destination account id
• amount: amount to be transferred

Dus to transfer 100 amount from account 1 to 2, we can use the request:

http://localhost:8000/move?from=1&to=2&amount=100

Account repository

To support the endpoint above we will use the following repository:

class BankAccountRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, BankAccount::class);
    }

    public function transferAmount($from, $to, $amount): void
    {
        // Fetches both account entities to be update
        $fromAccount = $this->find($from);
        $toAccount = $this->find($to);

        // Updates the amount on each of them
        $fromAccount->setAmount($fromAccount->getAmount() - $amount);
        $toAccount->setAmount($toAccount->getAmount() + $amount);

        // Persist both entities
        $this->getEntityManager()->persist($fromAccount);
        $this->getEntityManager()->persist($toAccount);
        $this->getEntityManager()->flush();
    }
}

On SQL it does the following (edited the Doctrine generated SQL for readability):

SELECT * FROM bank_account WHERE id = 1; # source
SELECT * FROM bank_account WHERE id = 2; # destination
START TRANSACTION;
UPDATE bank_account SET amount = ? WHERE id = 1; # source
UPDATE bank_account SET amount = ? WHERE id = 2; # destination
COMMIT;

Account controller

On controller side we then just need to parse the query parameters and call the repository accordingly.

class BankAccountController extends AbstractController
{
    #[Route('/move', name: 'bank_account')]
    public function transfer(Request $request, BankAccountRepository $repository): Response
    {
        $from = $request->query->get('from');
        $to = $request->query->get('to');
        $amount = $request->query->get('amount');

        $repository->transferAmount($from, $to, $amount);

        return new Response(sprintf('from %s to %s amount %s', $from, $to, $amount));
    }
}

Let's test!

Now, we create some test accounts in the database:

INSERT INTO "public"."bank_account" ("id", "name", "amount") VALUES
(1, 'Alice', 1000),
(2, 'Bob', 0);

And transfer 100 from Alice to Bob:

curl http://localhost:8000/move?from=1&to=2&amount=100

Checking the database, everything is fine:

| id | name  | amount |
|----|-------|--------|
|  1 | Alice |    900 |
|  2 | Bob   |    100 |

Easy, right? We could stress out this implementation and write unit tests, integration tests and everything will be working correctly.

What's wrong, then?

To identify the problem let's use the Apache HTTP server benchmarking tool (ab) to perform several requests to your application.

The first test will have the following scenario:

• Alice starts with 1000 amount
• Bob starts with 0 amount
• Alice makes 10 transfers of 100 to Bob, one request per time
• Final result expected:
  • Alice: 0
  • Bob: 1000

We can use the following command, where parameters n is the total number of requests and c is the number of concurrent requests:

ab -n 10 -c 1 'http://localhost:8000/move?from=1&to=2&amount=100'

You will have to trust me now, but I can assure you that after running the command above, Alice has 0 and Bob has 1000.

The second scenario will be similar but we will do 10 concurrent requests:

• Alice starts with 1000 amount
• Bob starts with 0 amount
• Alice makes 10 simultaneous transfers of 100 to Bob
• Final result expected:
  • Alice: 0
  • Bob: 1000

The parameter c is changed to 10:

ab -n 10 -c 10 'http://localhost:8000/move?from=1&to=2&amount=100'

The not so good result:

| id | name  | amount |
|----|-------|--------|
|  1 | Alice |    300 |
|  2 | Bob   |    700 |

But why? Basically there are processes updating the amount while there others that read and kept the old amount in memory. Let's imagine two concurrent processes A and B updating only Alice's account:

1 - Process A reads 1000 on Alice's account
2 - Process B reads 1000 on Alice's account
3 - Process A writes 900 on Alice's account
4 - Process B writes 900 on Alice's account (it should've been 800, shame on you!)

What's the fix then?

There's more than one solution, but I'll show you the one using Pessimistic Locking for read and writes. That means the database will only allow one read or write per resource, that in this case is the account entity.

On Doctrine we can achieve this using the following code:

public function transferAmountConcurrently($from, $to, $amount): void
{
    $this->getEntityManager()->beginTransaction();
    $fromAccount = $this->find($from, LockMode::PESSIMISTIC_WRITE);
    $toAccount = $this->find($to, LockMode::PESSIMISTIC_WRITE);

    $fromAccount->setAmount($fromAccount->getAmount() - $amount);
    $toAccount->setAmount($toAccount->getAmount() + $amount);

    $this->getEntityManager()->persist($fromAccount);
    $this->getEntityManager()->persist($toAccount);
    $this->getEntityManager()->flush();
    $this->getEntityManager()->commit();
}

We have now to explicitly begin a transaction before acquiring a lock, which makes sense because Doctrine can't know when the transaction should've started.

Finally, on SQL:

START TRANSACTION;
SELECT * FROM bank_account WHERE id = 1 FOR UPDATE; # source
SELECT * FROM bank_account WHERE id = 2 FOR UPDATE; # destination
UPDATE bank_account SET amount = ? WHERE id = 1; # source
UPDATE bank_account SET amount = ? WHERE id = 2; # destination
COMMIT;

For testing, I'll create a new endpoint on previously created BankAccountController:

#[Route('/move-concurrently', name: 'bank_account_concurrent')]
public function transferConcurrently(Request $request, BankAccountRepository $repository): Response
{
    $from = $request->query->get('from');
    $to = $request->query->get('to');
    $amount = $request->query->get('amount');

    $repository->transferAmountConcurrently($from, $to, $amount);

    return new Response(sprintf('from %s to %s amount %s', $from, $to, $amount));
}

And now we can test using the Apache benchmarking tool:

ab -n 10 -c 10 'http://localhost:8000/move-concurrently?from=1&to=2&amount=100'

Trust me, it's working now: Alice has 0 and Bob has 1000.

The end

By using the lock strategy, we guarantee the process that acquired lock is reading the most updated value and then updating consistent data based on the last read. Final code is on Github.

Thank you for reading and I hope you enjoyed!