A questão 1 poderia entrar em conflito se a primeira instrução de cada transação já tivesse adquirido seu bloqueio em nível de linha na primeira tabela antes de precisar do segundo bloqueio.

Uma das transações será eventualmente selecionada para reversão, permitindo que a outra seja concluída.

A solução, conforme você observou, seria adquirir os bloqueios em uma ordem consistente.

Para a pergunta 2: Ambas as consultas devem obter um ACCESS SHAREbloqueio para ler a tabela e não devem bloquear uma à outra, pois estão lendo a tabela e não alterando os dados. O ACCESS SHAREbloqueio não entra em conflito com o outro.

Esse bloqueio entraria em conflito apenas com um ACCESS EXCLUSIVEbloqueio, acredito. (Ou alguma forma de seleção para atualização)

Eles também são uma afirmação. Pode-se esperar que outro seja concluído, mas não há razão para impasse. Nem manter um bloqueio exclusivo está aguardando outro bloqueio mantido exclusivamente.

Um deadlock ocorre quando duas transações desejam adquirir bloqueios conflitantes no mesmo objeto em uma ordem que não pode ser resolvida aguardando a conclusão da outra.

Para referência: https://www.postgresql.org/docs/current/explicit-locking.html

Questão 1

...

Como podemos evitar isso? Minha única idéia é escrever em algum lugar uma ordem de bloqueio para todas as tabelas, como user->book, e garantir que a sigamos.

Obviamente, você já entende a essência de como surge o impasse, que, afirmando brevemente o exemplo mínimo, é que dois threads adquirem e mantêm um bloqueio, e cada um precisa adquirir outro para prosseguir, mas o bloqueio que cada um precisa adquirir é aquele já mantido pelo outro.

Esta situação, quando inesperada, surge essencialmente como resultado de uma falha de design do programador. Os mecanismos de banco de dados detectam (em tempo de execução) os impasses que esses defeitos causam e os resolvem encerrando um dos threads e liberando todos os seus bloqueios, mas os mecanismos não analisam estaticamente o código para discernir a possibilidade de impasse entre dois threads e agem para evitá-los. , e eles não lidam com deadlocks de maneira inteligente, além de abortar permanentemente um thread e todo o seu trabalho não confirmado.

De forma mais ampla, é uma das deficiências no princípio de "isolamento" entre algoritmos concorrentes, que os motores SQL (no entendimento popular) deveriam fornecer.

No entanto, o monitoramento de impasses executado pelos mecanismos de banco de dados é melhor do que o anterior, em que um sistema de computador inteiro simplesmente travaria permanentemente ao encontrar um desses defeitos de programação simultâneos.

Como você já observou, uma maneira de projetar corretamente é garantir que uma série de bloqueios nunca seja proposta para ser executada em uma ordem que possa causar o conflito. Isso pode envolver uma ordem na qual as tabelas são bloqueadas, mas também envolve potencialmente uma ordem na qual as linhas são bloqueadas na mesma tabela. Por exemplo, um padrão padrão é atualizar uma tabela de saldos de contas em uma ordem sempre indo dos números de contas mais baixos para os mais altos - porque se você seguir os dois caminhos, acabará entrando em um impasse, mesmo que os bloqueios estejam puramente dentro do escopo de apenas uma mesa.

Na prática, como os mecanismos SQL decidem os planos de consulta por si mesmos (por padrão), incluindo o bloqueio necessário e a ordem de posicionamento dos bloqueios, e como não é possível especificar todas as ordenações de maneira direta e explícita usando SQL, muitas vezes é muito mais fácil identificar as ordens necessárias do que realmente fazer o motor obedecê-las.

Além disso, embora a análise necessária seja bastante simples quando se trata de um número limitado de algoritmos simultâneos em um número limitado de tabelas, torna-se um esforço sério realizar a análise e determinar uma ordem de bloqueio apropriada para todo o banco de dados de uma aplicação não trivial. .

Outra estratégia é aumentar a granularidade dos bloqueios, de modo que a possibilidade de uma ordem conflitante seja eliminada. Assim, por exemplo, em vez de bloqueio de linha (no exemplo que dou sobre atualização de saldos de contas), aumente para bloqueio de tabela para que apenas um bloqueio de tabela seja necessário para cobrir todas as linhas que estão sendo atualizadas, e um thread tenha esse único bloqueio ou não.

Uma terceira estratégia é implementar algum tipo de mecanismo de serialização personalizado, que não torne diretamente compatível o bloqueio de vários algoritmos quando eles são executados simultaneamente, mas simplesmente evite que um cronograma de execução concorrente surja entre dois algoritmos que de outra forma seriam propensos para um impasse.

E, finalmente , o risco de impasses pode ser aceito e tratado. As vítimas de deadlock que podem ser abortadas podem ser colocadas em uma ordem de prioridade para determinar quais são abortadas e quais têm prioridade para prosseguir, e lógica adicional pode ser adicionada aos algoritmos para fazer com que o trabalho seja tentado novamente no caso de ser vítima de um resolução de impasse.

Às vezes, mesmo em um sistema projetado com uma ordem de bloqueio devidamente analisada, pode ser necessário (por simples razões de desempenho) ir contra a ordem e, portanto, correr o risco de um impasse.

Um sistema que foi originalmente projetado com uma ordem de bloqueio também pode ser alterado de uma forma que potencialmente exija a alteração da ordem de bloqueio, mas em vez de se envolver na complicada análise e retrabalho que pode ser necessário para alterar a ordem existente em torno da qual um um grande aplicativo de banco de dados já foi escrito; em vez disso, um algoritmo é projetado para ir contra a ordem usando o melhor esforço e simplesmente tentar novamente em caso de impasse.