Violação ACID no Postgres 9.1?

Problemas específicos com a consulta

O padrão do PostgreSQL é o READ COMMITTEDisolamento e parece que você não está usando nada diferente. Em READ COMMITTEDcada instrução obtém seu próprio instantâneo. Ele não pode ver alterações não confirmadas de outras transações; é como se simplesmente não tivessem acontecido.

Agora, imagine que você execute isso simultaneamente em três sessões, em uma configuração com três entradas em jobs, zero em jobs_runninge zero em jobs_completed:

insert into piper.jobs_running
select x.fid from ( 
  SELECT fid FROM piper.jobs
  except 
  select fid from piper.jobs_running
  except 
  select fid from piper.jobs_completed
 ) as x limit 1
returning(fid)

Cada execução selecionará os três trabalhos em jobs. Como seus instantâneos foram todos tirados antes de qualquer um deles fazer uma alteração ou até mesmo criar a linha não confirmada, *todos eles encontrarão zero linhas em jobs_runninge jobs_completed.

Então todos eles reivindicam um emprego. Provavelmente o mesmo trabalho , porque mesmo que não haja ORDER BY, a ordem de digitalização será a mesma.

Bloqueio

O bloqueio de linha cruza as fronteiras transnacionais e permite que você se comunique entre as transações para impor a ordem. Então você pode pensar que isso resolveria seu problema, mas não vai.

Se você FOR UPDATEbloquear a rowentrada, de modo que a linha seja bloqueada exclusivamente, o bloqueio será retido até que a transação seja confirmada ou revertida. Portanto, você pensaria que a próxima transação obteria uma linha diferente ou, se tentasse obter a mesma linha, aguardaria a liberação do bloqueio, verificaria se agora havia uma entrada em jobs_runninge pularia a linha. Você estaria errado.

O que acontecerá é que você bloqueará todas as linhas. Uma transação obterá com sucesso os bloqueios em todas as linhas. As outras transações, que farão o mesmo índice ou varredura sequencial, geralmente tentarão bloquear as linhas na mesma ordem, travarão na primeira tentativa de bloqueio e aguardarão a reversão ou confirmação da primeira transação. Se você não tiver sorte, eles podem começar a bloquear diferentes conjuntos de linhas e travar uns contra os outros, causando a interrupção do impasse, mas geralmente você não está obtendo nenhuma simultaneidade útil.

Pior ainda, a primeira transação escolhe uma das linhas que bloqueou, insere uma linha jobs_runninge confirma, liberando os bloqueios. Outra transação é capaz de continuar e bloqueia todas as linhas .... mas não obtém um novo instantâneo do estado do banco de dados (o instantâneo é obtido no início da instrução), então * não pode ver que você inseriu uma linha em jobs_running. Assim, ele pega o mesmo trabalho, insere uma linha para esse trabalho em jobs_runninge confirma.

Novas verificações de condição

O PostgreSQL tem um recurso peculiar não incluído na maioria dos bancos de dados onde, se uma transação for bloqueada em um bloqueio, ele verifica novamente se a linha selecionada ainda corresponde à condição de bloqueio quando obtém o bloqueio após a confirmação da primeira transação.

É por isso que o exemplo em https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres funciona - ele se baseia em novas verificações de qualificador em WHEREcláusulas após recuperar um bloqueio.

O uso de locking força tudo a rodar serialmente, então na prática você pode muito bem ter uma única conexão fazendo o trabalho.

Isolamento, ACID e realidade

O isolamento de transações no PostgreSQL não é o ideal perfeito onde as transações são executadas simultaneamente, mas seus efeitos são os mesmos como se fossem executados em série.

O único banco de dados do mundo real que forneceria isolamento perfeito seria aquele que bloqueia exclusivamente todas as tabelas quando uma transação de gravação a acessa pela primeira vez; portanto, na prática, as transações só podem ser de acesso simultâneo se forem para diferentes partes do banco de dados. Ninguém deseja usar esse banco de dados em situações em que a simultaneidade é desejável ou útil.

Todas as implementações do mundo real são concessões.

READ COMMITTEDpredefinição

O padrão do PostgreSQL é o READ COMMITTEDisolamento, um nível de isolamento bem definido que permite leituras não repetíveis e fantasmas , conforme discutido no manual do PostgreSQL sobre isolamento de transação .

SERIALIZABLEisolamento

Você pode solicitar um SERIALIZABLEisolamento mais rígido por transação ou como padrão global por usuário, por banco de dados ou (não recomendado). Isso fornece garantias muito mais fortes, embora ainda não perfeitas, ao custo de forçar a reversão das transações se elas interagirem de maneiras que não poderiam acontecer se tivessem sido executadas em série.

Como suas consultas simultâneas sempre tentarão obter o primeiro trabalho, não importa quantos trabalhos existam, todos, exceto um, serão interrompidos com falhas de serialização. Portanto, na prática, você não obtém nenhuma simultaneidade útil e pode muito bem ter uma única conexão distribuindo tarefas aos trabalhadores.

(Observe que antes do PostgreSQL 9.1 SERIALIZABLEoferecia garantias muito mais fracas e não detectava muitos casos em que as transações eram interdependentes.)

Reexecução automáticaSERIALIZABLE

O PostgreSQL não executa automaticamente transações SERIALIZABLEque abortam devido a falhas de serialização. Há casos em que isso seria bastante útil, mas outros casos em que isso seria completamente errado e perigoso - especialmente quando estão envolvidos ciclos de leitura/modificação/gravação por meio do aplicativo. No momento, não há suporte para a reexecução automática de transações em falhas de serialização. Espera-se que o aplicativo tente novamente.

Não escreva um sistema de filas DIY

Parece que o que você está tentando fazer é escrever um sistema de filas. Considere não fazer isso. Escrever um sistema de filas robusto, confiável e correto é difícil, e existem alguns bons já disponíveis que você pode adotar. Você tem que lidar com coisas como segurança contra falhas, o que acontece quando alguém pega uma tarefa e não consegue completá-la, condições de corrida em torno da conclusão assim que você desiste de o manipulador de tarefas concluí-la, etc. Existem muitos problemas sutis de simultaneidade. Não tente DIY.

9.5 eSKIP LOCKED

O PostgreSQL 9.5, ainda em desenvolvimento, adiciona um recurso que torna o enfileiramento um pouco mais fácil.

Ele permite que você diga que if when you SELECT ... FOR UPDATE, se uma linha estiver bloqueada, você deve ignorá-la e continuar para encontrar a próxima linha não bloqueada. Isso é muito útil quando combinado com um LIMIT, pois permite dizer "encontre-me a primeira linha que outra pessoa ainda não está tentando reivindicar". Portanto, torna-se muito simples escrever filas que capturam trabalhos de forma segura e simultânea.

Até que esse recurso esteja disponível, sugiro fortemente que você se atenha ao gerenciamento de fila de conexão única ou use um sistema de fila de tarefas bem testado.

A resposta de craig-ringer responde totalmente à pergunta. Por uma questão de integridade, adiciono o seguinte código trivial para resolver o problema usando bloqueios:

create or replace function f_requestJob(
    jobs           text, 
    jobs_running   text, 
    jobs_completed text
) 
    returns bigint as
$$
declare 
    sql varchar;
    c bigint;

begin
    execute 'LOCK TABLE ' || jobs           || ' IN ACCESS EXCLUSIVE MODE'; 
    execute 'LOCK TABLE ' || jobs_running   || ' IN ACCESS EXCLUSIVE MODE'; 
    execute 'LOCK TABLE ' || jobs_completed || ' IN ACCESS EXCLUSIVE MODE';

    sql := '
    insert into ' || jobs_running || '
    select x.fid     
    from ( 
      SELECT fid FROM ' || jobs || '
      except 
      select fid from ' || jobs_running || '
      except 
      select fid from ' || jobs_completed || '
     ) as x limit 1
    returning(fid)';

    execute sql into c;

    return c; --may be a null value

end;
$$ language plpgsql;