Início / dba / Perguntas / 106121
Accepted
Rob Miller
Asked: 2015-07-07 13:43:28 +0800 CST

Bloqueando no Postgres para combinação UPDATE / INSERT

  • 772

Eu tenho duas mesas. Uma é uma tabela de log; outro contém, essencialmente, códigos de cupom que só podem ser usados ​​uma vez.

O usuário precisa poder resgatar um cupom, que inserirá uma linha na tabela de log e marcará o cupom como usado (atualizando a usedcoluna para true).

Naturalmente, há um problema óbvio de condição/segurança de corrida aqui.

Eu fiz coisas semelhantes no passado no mundo do mySQL. Nesse mundo, eu bloquearia ambas as tabelas globalmente, faria a lógica com segurança sabendo que isso só poderia acontecer uma vez por vez e, em seguida, desbloquearia as tabelas assim que terminasse.

Existe uma maneira melhor no Postgres de fazer isso? Em particular, estou preocupado que o bloqueio seja global, mas não precisa ser - eu realmente só preciso ter certeza de que ninguém mais está tentando inserir esse código específico, então talvez algum bloqueio em nível de linha funcione?

postgresql update

2 respostas

  1. Best Answer

    Já ouvi falar de problemas de simultaneidade como esse no MySQL antes. Não é assim no Postgres.

    Os bloqueios de nível de linha integrados no READ COMMITTEDnível de isolamento de transação padrão são suficientes.

    Sugiro uma única instrução com um CTE modificador de dados (algo que o MySQL também não possui) porque é conveniente passar valores de uma tabela para outra diretamente (se você precisar disso). Se você não precisar de nada da coupontabela, também poderá usar uma transação com instruções UPDATEe separadas INSERT.

    WITH upd AS (
   UPDATE coupon
   SET    used = true
   WHERE  coupon_id = 123
   AND    NOT used
   RETURNING coupon_id, other_column
   )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

    Deve ser raro que mais de uma transação tente resgatar o mesmo cupom. Eles têm um número único, não é? Mais de uma transação tentando no mesmo momento deve ser muito mais rara, ainda. (Talvez um bug de aplicativo ou alguém tentando enganar o sistema?)

    Seja como for, o UPDATEúnico sucesso para exatamente uma transação, não importa o quê. An UPDATEadquire um bloqueio de nível de linha em cada linha de destino antes de atualizar. Se uma transação concorrente tentar UPDATEna mesma linha, ela verá o bloqueio na linha e aguardará até que a transação de bloqueio seja concluída ( ROLLBACKou COMMIT), sendo a primeira na fila de bloqueio:

    • Se confirmado, verifique novamente a condição. Se ainda estiver NOT used, bloqueie a linha e prossiga. Caso contrário, o UPDATEnow não encontra nenhuma linha de qualificação e não faz nada , não retornando nenhuma linha, então o INSERTtambém não faz nada.

    • Se revertido, bloqueie a linha e prossiga.

    Não há potencial para uma condição de corrida .

    Não há potencial para um impasse , a menos que você coloque mais gravações na mesma transação ou bloqueie mais linhas do que apenas uma.

    O INSERTé livre de cuidados. Se, por algum erro, o coupon_idjá estiver na logtabela (e você tiver uma restrição UNIQUE ou PK em log.coupon_id), toda a transação será revertida após uma violação única. Indicaria um estado ilegal em seu banco de dados. Se a instrução acima for a única maneira de gravar na logtabela, isso nunca deve ocorrer.

    • 32

  2. A resposta de Erwin é o estado da arte. Mas por uma questão de integridade, gostaria de sugerir mais opções.

    Bloqueio Pessimista

    O primeiro é típico - usando o Pessimistic Lock na linha do cupom, ele deve ser obtido no início:

    select * from coupon where id=? and used=false for update
insert into log(...)

    A 2ª transação aguardará até que a 1ª seja concluída. Se o 1º TX for confirmado, então selectnão retornará o cupom e você poderá abortar. Essa abordagem é semelhante à resposta de Erwin, mas permite dividir a transação em várias instruções.

    Bloqueio pessimista com salto bloqueado

    Se você não se importa com qual cupom pegar - qualquer um que satisfaça o preço funciona, então você pode simplesmente pular o que está bloqueado e seguir em frente. Nesse caso, você nunca precisa abortar as transações - elas sempre serão bem-sucedidas:

    select * from coupon where used=false for update skip locked limit 1
insert into log(...)

    Estou hesitante em chamar isso de "bloqueio pessimista" porque, na verdade, nunca será bloqueado.

    Isolamento serializável

    Outra opção é otimista (no caso de PG): usando o isolamento serializável. Nesse caso, você pode inserir na tabela de log antes de atualizar o arquivo used=false. Observe que quando o 2º TX tentar fazer o commit, ele notará que o 1º TX atualizou a linha que atualizou (ou mesmo se apenas a leu) e você obterá:

    ERROR:  could not serialize access due to concurrent update

    Mais sobre isolamentos no PG .

    • 1

relate perguntas