Database concurrency as simple as possible
Tags: php, database, sql, tutorial
Published: 2022-01-29 18:32:19

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
![atm losing money](https://media.giphy.com/media/3ov9jOmaqfoIuJuKd2/giphy.gif)

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.

```sql
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:
```bash
http://localhost:8000/move?from=1&to=2&amount=100
```

## Account repository
To support the endpoint above we will use the following repository:
```php
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):

```sql
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.

```php
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:
```sql
INSERT INTO "public"."bank_account" ("id", "name", "amount") VALUES
(1, 'Alice', 1000),
(2, 'Bob', 0);
```

And transfer 100 from Alice to Bob:
```bash
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?
![Someone asking what is the problem](https://media.giphy.com/media/3oKHWcp23mdfNIEEIo/giphy.gif)

To identify the problem let's use the [Apache HTTP server benchmarking tool (ab)](https://httpd.apache.org/docs/2.4/programs/ab.html) 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:
```bash
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:
```bash
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 |
```

![the results are not good](https://media.giphy.com/media/1iUZa41YxKQtaJq0/giphy.gif)

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?
![Someone trying to discover a bugfix](https://media.giphy.com/media/l2JefhuLRZWPnxF1m/giphy.gif)

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:
```php
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:
```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`:
```php
#[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:
```bash
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.

![someone celebrating because it's working](https://media.giphy.com/media/CuMiNoTRz2bYc/giphy.gif)

## 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](https://github.com/fabiothiroki/symfony-bank-transaction).

{% github fabiothiroki/symfony-bank-transaction no-readme %}

Thank you for reading and I hope you enjoyed!