Concorrência em banco de dados explicada de forma simples
Tags: braziliandevs, php, database, sql
Published: 2022-02-15 18:18:54

Nesse artigo vou mostrar conceitos básicos para lidar com requisições simultâneas criando uma aplicação web de um banco. Quando estamos programando tem algumas armadilhas que precisamos nos atentar especialmente porque não são cenários fáceis de testar.

## Escopo da aplicação do banco
![caixa eletrônico perdendo dinheiro](https://media.giphy.com/media/3ov9jOmaqfoIuJuKd2/giphy.gif)

Nossa aplicação de exemplo armazenará contas bancárias que poderão transferir dinheiro entre elas. Ela será construída usando PHP, Symfony e o mapeamento objeto-relacional (ORM) Doctrine, mas você não precisa conhecer essas tecnologias, apenas o banco de dados Postgres.

## Entidade conta bancária (Account)
A conta bancária guardará o nome do dono e a quantidade final de dinheiro.

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

## API para transferência de dinheiro
O endpoint para transferir dinheiro entre duas contas irá receber três variáveis por query parameters:
- `from`: id da conta origem
- `to`: id da conta destino
- `amount`: quantidade a ser transferida

Então para transferir 100 da conta 1 para conta 2, podemos usar a seguinte requisição:
```bash
http://localhost:8000/move?from=1&to=2&amount=100
```

## Repositório Conta Bancária
Para que o endpoint acima funcione, precisamos do seguinte repositório:
```php
class BankAccountRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, BankAccount::class);
    }

    public function transferAmount($from, $to, $amount): void
    {
        // Busca as contas bancárias a serem atualizadas
        $fromAccount = $this->find($from);
        $toAccount = $this->find($to);

        // Atualiza a quantidade delas
        $fromAccount->setAmount($fromAccount->getAmount() - $amount);
        $toAccount->setAmount($toAccount->getAmount() + $amount);
        
        // Persiste as duas entidades
        $this->getEntityManager()->persist($fromAccount);
        $this->getEntityManager()->persist($toAccount);
        $this->getEntityManager()->flush();
    }
}
```
Traduzindo para SQL, temos o seguinte (editado a partir do SQL gerado pelo Doctrine para fins de legibilidade):

```sql
SELECT * FROM bank_account WHERE id = 1; -- origem
SELECT * FROM bank_account WHERE id = 2; -- destino
START TRANSACTION;
UPDATE bank_account SET amount = ? WHERE id = 1; -- origem
UPDATE bank_account SET amount = ? WHERE id = 2; -- destino
COMMIT;
```

## Controlador Conta
Do lado do controlador, temos que extrair os query parameters da requisição e repassá-los para o repositório:

```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));
    }
}
```

## Vamos testar!
Vamos criar contas de teste no banco de dados:
```sql
INSERT INTO "public"."bank_account" ("id", "name", "amount") VALUES
(1, 'Alice', 1000),
(2, 'Bob', 0);
```

E transferir 100 de Alice para Bob:
```bash
curl http://localhost:8000/move?from=1&to=2&amount=100
```

Verificando os dados no banco podemos observar que o resultado está correto:

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

Fácil, certo? A partir de agora podemos melhorar essa implementação criando testes unitários, de integração e tudo continuará funcionando corretamente.

## O que tem de errado?
![Alguém perguntando qual é o problema](https://media.giphy.com/media/3oKHWcp23mdfNIEEIo/giphy.gif)

Para identificar o problema, vamos usar o [Apache HTTP server benchmarking tool (ab)](https://httpd.apache.org/docs/2.4/programs/ab.html) para fazer várias requisições na nossa aplicação.

O primeiro teste terá o seguinte cenário:
- Alice começa com o montante 1000
- Bob começa com o montante 1000
- Alice faz 10 transferências de 100 para Bob, uma de cada vez
- Resultado final esperado: 
  - Alice: 0
  - Bob: 1000

Nós podemos usar o seguinte comando, onde o parâmetro `n` é o número total de requisições e `c` é o número de requisições simultâneas:
```bash
ab -n 10 -c 1 'http://localhost:8000/move?from=1&to=2&amount=100'
```

Você terá que confiar em mim agora, mas posso garantir que ao rodar o comando acima Alice terá 0 e Bob terá 1000.

O segundo cenário será bem parecido, mas faremos 10 requisições simultâneas:
- Alice começa com 1000
- Bob começa com 0
- Alice faz 10 transferências **simultâneas** com o montante de 100 para Bob
- Resultado final esperado: 
  - Alice: 0
  - Bob: 1000

Preciamos alterar o parâmetro `c` para 10:
```bash
ab -n 10 -c 10 'http://localhost:8000/move?from=1&to=2&amount=100'
```

O resultado nada agradável:
```
| id | name  | amount |
|----|-------|--------|
|  1 | Alice |    300 |
|  2 | Bob   |    700 |
```

![Os resultados não são bons](https://media.giphy.com/media/1iUZa41YxKQtaJq0/giphy.gif)

Mas por quê? Basicamente há processos atualizando o montante enquanto há outros que estão lendo e mantendo o valor antigo na memória. Vamos imaginar dois processos concorrentes A e B atualizando apenas a conta de Alice: 

1 - Processo A lê o valor 1000 na conta de Alice
2 - Processo B lê o valor 1000 na conta de Alice
3 - Processo A escreve 900 na conta de Alice
4 - Processo B escreve 900 na conta de Alice (deveria ser 800, que vergonha!)

## Como corrigir?
![Alguém tentando achar a correção](https://media.giphy.com/media/l2JefhuLRZWPnxF1m/giphy.gif)

Há mais de uma solução, mas a que eu vou mostrar será utilizando `Pessimistic Locking` para escritas e leituras. Isso significa que o banco de dados só permitirá uma escrita ou uma leitura por recurso, que nesse caso é a nossa entidade conta.

No Doctrine podemos fazer isso utilizando o seguinte código no repositório:
```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();
}
```

Agora temos que explicitamente demarcar o início da transação antes de adquirir o lock, o que faz sentido uma vez que o Doctrine não consegue saber quando a transação deveria ter começado.

Finalmente, a mesma solução em SQL:
```sql
START TRANSACTION;
SELECT * FROM bank_account WHERE id = 1 FOR UPDATE; # origem
SELECT * FROM bank_account WHERE id = 2 FOR UPDATE; # destino
UPDATE bank_account SET amount = ? WHERE id = 1; # origem
UPDATE bank_account SET amount = ? WHERE id = 2; # destno
COMMIT;
```

Para testar, vou criar uma nova rota no controlador existente `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));
}
```

E agora podemos testar usando o Apache benchmarking tool
```bash
ab -n 10 -c 10 'http://localhost:8000/move-concurrently?from=1&to=2&amount=100'
```

Pode confiar em mim, está funcionando agora: Alice possui 0 e Bob possui 1000.

![Alguém celebrando que está funcionando](https://media.giphy.com/media/CuMiNoTRz2bYc/giphy.gif)

## O fim
Ao usar a estratégia de locking nós garantimos que o processo que adquiriu o lock está lendo o valor mais atualizado e então atualizando dado consitente baseado na última leitura. O código final está no [Github](https://github.com/fabiothiroki/symfony-bank-transaction).

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

Obrigado pela leitura e espero que tenha gostado!