Concorrncia em banco de dados explicada de forma simples

Nesse artigo vou mostrar conceitos bsicos para lidar com requisies simultneas criando uma aplicao web de um banco. Quando estamos programando tem algumas armadilhas que precisamos nos atentar especialmente porque no so cenrios fceis de testar.

Escopo da aplicao do banco

Nossa aplicao de exemplo armazenar contas bancrias que podero transferir dinheiro entre elas. Ela ser construda usando PHP, Symfony e o mapeamento objeto-relacional (ORM) Doctrine, mas voc no precisa conhecer essas tecnologias, apenas o banco de dados Postgres.

Entidade conta bancria (Account)

A conta bancria guardar o nome do dono e a quantidade final de dinheiro.

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

API para transferncia de dinheiro

O endpoint para transferir dinheiro entre duas contas ir receber trs variveis por query parameters:

• from: id da conta origem
• to: id da conta destino
• amount: quantidade a ser transferida

Ento para transferir 100 da conta 1 para conta 2, podemos usar a seguinte requisio:

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

Repositrio Conta Bancria

Para que o endpoint acima funcione, precisamos do seguinte repositrio:

class BankAccountRepository extends ServiceEntityRepository{    public function __construct(ManagerRegistry $registry)    {        parent::__construct($registry, BankAccount::class);    }    public function transferAmount($from, $to, $amount): void    {        // Busca as contas bancrias 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):

SELECT * FROM bank_account WHERE id = 1; -- origemSELECT * FROM bank_account WHERE id = 2; -- destinoSTART TRANSACTION;UPDATE bank_account SET amount = ? WHERE id = 1; -- origemUPDATE bank_account SET amount = ? WHERE id = 2; -- destinoCOMMIT;

Controlador Conta

Do lado do controlador, temos que extrair os query parameters da requisio e repass-los para o repositrio:

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:

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

E transferir 100 de Alice para Bob:

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 |

Fcil, certo? A partir de agora podemos melhorar essa implementao criando testes unitrios, de integrao e tudo continuar funcionando corretamente.

O que tem de errado?

Para identificar o problema, vamos usar o Apache HTTP server benchmarking tool (ab) para fazer vrias requisies na nossa aplicao.

O primeiro teste ter o seguinte cenrio:

• Alice comea com o montante 1000
• Bob comea com o montante 1000
• Alice faz 10 transferncias de 100 para Bob, uma de cada vez
• Resultado final esperado:
  • Alice: 0
  • Bob: 1000

Ns podemos usar o seguinte comando, onde o parmetro n o nmero total de requisies e c o nmero de requisies simultneas:

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 cenrio ser bem parecido, mas faremos 10 requisies simultneas:

• Alice comea com 1000
• Bob comea com 0
• Alice faz 10 transferncias simultneas com o montante de 100 para Bob
• Resultado final esperado:
  • Alice: 0
  • Bob: 1000

Preciamos alterar o parmetro c para 10:

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

O resultado nada agradvel:

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

Mas por qu? Basicamente h processos atualizando o montante enquanto h outros que esto lendo e mantendo o valor antigo na memria. 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?

H mais de uma soluo, 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 cdigo no repositrio:

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 incio da transao antes de adquirir o lock, o que faz sentido uma vez que o Doctrine no consegue saber quando a transao deveria ter comeado.

Finalmente, a mesma soluo em SQL:

START TRANSACTION;SELECT * FROM bank_account WHERE id = 1 FOR UPDATE; # origemSELECT * FROM bank_account WHERE id = 2 FOR UPDATE; # destinoUPDATE bank_account SET amount = ? WHERE id = 1; # origemUPDATE bank_account SET amount = ? WHERE id = 2; # destnoCOMMIT;

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

E agora podemos testar usando o Apache benchmarking tool

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.

O fim

Ao usar a estratgia de locking ns garantimos que o processo que adquiriu o lock est lendo o valor mais atualizado e ento atualizando dado consitente baseado na ltima leitura. O cdigo final est no Github.

Obrigado pela leitura e espero que tenha gostado!