Rastreando, depurando e corrigindo contenções de bloqueio de linha

Seria possível descobrir qual linha de um procedimento armazenado está causando essas contenções de bloqueio de linha?

Não exatamente, mas você pode obter a instrução SQL que está causando o bloqueio e, por sua vez, identificar as linhas relacionadas no procedimento.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

Qual seria a diretriz geral para reduzir/evitar/eliminar tais problemas com a codificação?

A seção do Oracle Concepts Guide sobre bloqueios diz: "Uma linha é bloqueada apenas quando modificada por um gravador". Outra sessão atualizando a mesma linha aguardará a primeira sessão COMMITou ROLLBACKantes de poder continuar. Para eliminar o problema, você pode serializar os usuários, mas aqui estão algumas coisas que podem reduzir o problema talvez ao nível de não ser um problema.

• COMMITmais frequentemente. Todos COMMITos lançamentos são bloqueados, portanto, se você puder fazer as atualizações em lotes, a probabilidade de outra sessão precisar da mesma linha será reduzida.
• Certifique-se de não atualizar nenhuma linha sem alterar seus valores. Por exemplo, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1);deve ser reescrito como o mais seletivo (leia menos bloqueios) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Obviamente, se a alteração da instrução ainda bloquear a maioria das linhas na tabela, a alteração terá apenas um benefício de legibilidade.
• Certifique-se de usar sequências em vez de bloquear uma tabela para adicionar uma ao valor atual mais alto.
• Verifique se você não está usando uma função que está fazendo com que um índice não seja usado. Se a função for necessária, considere torná-la um índice baseado em função.
• Pense em conjuntos. Considere se um loop executando um bloco de PL/SQL fazendo atualizações pode ser reescrito como uma única instrução de atualização. Caso contrário, talvez o processamento em massa possa ser usado com BULK COLLECT ... FORALL.
• Reduza o trabalho que é feito entre o primeiro UPDATEe o COMMIT. Por exemplo, se o código enviar um e-mail após cada atualização, considere enfileirar os e-mails e enviá-los após confirmar as atualizações.
• Projete o aplicativo para lidar com a espera fazendo um SELECT ... FOR UPDATE NOWAITou WAIT 2. Você pode detectar a incapacidade de bloquear a linha e informar ao usuário que outra sessão está modificando os mesmos dados.

Vou fornecer uma resposta do ponto de vista do desenvolvedor.

Na minha opinião, quando você encontra uma disputa de linha como a que você descreveu, é porque você tem um bug em seu aplicativo. Na maioria dos casos, esse tipo de contenção é um sinal de vulnerabilidade de atualização perdida. Este tópico no AskTom explica o conceito de uma atualização perdida:

Uma atualização perdida ocorre quando:

sessão 1: leia o registro do funcionário de Tom

sessão 2: leia o registro do funcionário de Tom

sessão 1: atualize o registro do funcionário de Tom

sessão 2: atualize o registro do funcionário de Tom

A sessão 2 sobrescreverá as alterações da sessão 1 sem nunca vê-las -- resultando em uma atualização perdida.

Você experimentou um efeito colateral desagradável de atualização perdida: a sessão 2 pode ser bloqueada porque a sessão 1 ainda não foi confirmada. O principal problema, entretanto, é que a sessão 2 atualiza cegamente o registro. Suponha que ambas as sessões emitam a declaração:

UPDATE table SET col1=:col1, ..., coln=:coln WHERE id = :pk

Após ambas as instruções, as modificações da sessão1 foram sobrescritas, sem que a sessão2 tenha sido notificada de que a linha foi modificada pela sessão 1.

A atualização perdida (e o efeito colateral da contenção) nunca deve acontecer, eles são 100% evitáveis. Você deve usar o bloqueio para evitá-los com dois métodos principais: bloqueio otimista e pessimista .

1) Bloqueio pessimista

Você deseja atualizar uma linha. Nesse modo, você impedirá que outras pessoas modifiquem essa linha solicitando um bloqueio nessa linha ( SELECT ... FOR UPDATE NOWAITinstrução). Se a linha já estiver sendo modificada, você receberá uma mensagem de erro, que pode ser facilmente traduzida para o usuário final (essa linha está sendo modificada por outro usuário). Se a linha estiver disponível, faça suas modificações (UPDATE) e confirme sempre que sua transação for concluída.

2) Bloqueio Otimista

Você deseja atualizar uma linha. No entanto, você não deseja manter um bloqueio nessa linha, talvez porque use várias transações para atualizar a linha (aplicativo sem estado baseado na Web) ou talvez não queira que nenhum usuário mantenha um bloqueio por muito tempo ( o que pode resultar no bloqueio de outras pessoas). Nesse caso, você não solicitará um bloqueio imediatamente. Você usará um marcador para garantir que a linha não foi alterada quando sua atualização for emitida. Você pode armazenar em cache o valor de todas as colunas ou pode usar uma coluna de carimbo de data/hora que é atualizada automaticamente ou uma coluna baseada em sequência. Seja qual for a sua escolha, quando você estiver prestes a realizar sua atualização, você se certificará de que o marcador nessa linha não foi alterado emitindo uma consulta como:

SELECT <...>
  FROM table
 WHERE id = :id
   AND marker = :marker
   FOR UPDATE NOWAIT

Se a consulta retornar uma linha, faça sua atualização. Caso contrário, isso significa que alguém modificou a linha desde a última vez que você a consultou. Você terá que reiniciar o processo desde o início.

Nota: Se você tiver confiança total em todos os aplicativos que acessam seu banco de dados, poderá contar com uma atualização direta para o bloqueio otimista. Você pode emitir diretamente:

UPDATE table
   SET <...>, 
       marker = marker + 1
 WHERE id = :id;

Se a instrução não atualizar nenhuma linha, você saberá que alguém alterou essa linha e precisará começar tudo de novo.

Se todos os aplicativos concordarem com esse esquema, você nunca será bloqueado por outra pessoa e evitará a atualização cega. No entanto, se você não bloquear a linha de antemão, ainda estará suscetível ao bloqueio indefinido se outro aplicativo, trabalho em lote ou atualização direta não implementar o bloqueio otimista. É por isso que aconselho sempre bloquear a linha, independentemente da escolha do esquema de bloqueio (o impacto no desempenho pode ser insignificante, pois você recupera todos os valores, incluindo o rowid, ao bloquear a linha).

TL;DR

• Atualizar uma linha sem ter um bloqueio prévio expõe o aplicativo a um possível "congelamento". Isso pode ser evitado se todos os DML para o banco de dados implementarem bloqueio otimista ou pessimista.
• Verifique se a instrução SELECT retorna valores consistentes com qualquer SELECT anterior (para evitar qualquer problema de perda de atualização)

Esta resposta provavelmente se qualificaria para uma entrada no The Daily WTF.

Certo, depois de rastrear as sessões e pesquisar USER_SOURCE- localizei a causa raiz

• A causa, sem surpresa, foi uma lógica falha
• Recentemente, uma declaração de atualização foi adicionada ao SP. A instrução de atualização basicamente atualizaria a tabela inteira. Aparentemente, o desenvolvedor em questão se esqueceu de adicionar as cláusulas right where para atualizar as instruções necessárias.
• A tabela que estava sendo atualizada era como mencionado acima, uma das tabelas mais movimentadas e com um grande número de registros. A atualização levaria um tempo longo e agonizante.
• O resultado foi que outras sessões não conseguiram obter um bloqueio na mesa e permaneceram em disputas de bloqueio de linha.