Monitor and resolve locking conflicts

Quando uma sessão tenta conseguir um exclusive lock e não consegue por que outra sessão já tem esse exclusive lock no mesmo recurso desejado, isso é chamado de lock contention, a sessão vai ficar aguardando esse lock sair pra conseguir o seu lock.

Acontecer locks no banco é algo normal, mas acontecer muitos locks contention e aguardar muito tempo geralmente é um design mal feito da aplicação. E geralmente o DBA vai ter que intervir para resolver esses conflitos.

Existe algumas dynamic views que pode nos ajudar a encontrar o lock contention e saber qual objeto esta com o lock, qual sessão esta originando o lock e qual a sessão esta sendo bloqueada por esse lock. É possível saber até mesmo qual linha esta com lock no momento, de qual tabela.

A primeira view que devemos conhecer é a view V$SESSION. Essa view tem uma linha para cada sessão no banco. Se uma sessão estiver sendo bloqueada por outra, a coluna blocking_session vai ter o sid da sessão que esta causando isso. Vamos fazer um exemplo:

###PRIMEIRA SESSÃO####
SQL> CREATE TABLE teste_lock(A NUMBER, b NUMBER);

Table created.

SQL> INSERT INTO teste_lock VALUES(1,1);

1 row created.

SQL> INSERT INTO teste_lock VALUES(2,2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE teste_lock
  2  SET    a=3,b=3
  3  WHERE  a=1;

1 row updated.

SQL> SELECT SYS_CONTEXT('USERENV','SID') FROM dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
954

###SEGUNDA SESSÃO###
SQL> SELECT SYS_CONTEXT('USERENV','SID') FROM dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
1130

SQL> UPDATE teste_lock
  2  SET    a=3,b=3
  3  WHERE  a=1;

###SESSÃO 1 DE NOVO, OU QUALQUER OUTRA SEM SER A 2###
SQL> SELECT tt.blocking_session,
  2         tt.sid
  3  FROM   V$SESSION tt
  4  WHERE  tt.blocking_session IS NOT NULL;

BLOCKING_SESSION        SID
---------------- ----------
             954       1130

A primeira sessão, com SID 954, criou a tabela teste_lock, inseriu duas linhas e atualizou uma, sem COMMIT. A segunda sessão, com SID 1130 atualizou a mesma linha. Como a primeira sessão não terminou a transação, por commit ou rollback, o lock continuou lá. Assim, a segunda sessão não conseguiu o exclusive lock na linha em questão, e assim ficou travada, esperando até conseguir. Isso causou o lock contention.

Depois, de outra sessão, consultamos a tabela V$SESSION, onde a coluna blocking_session não é nula, ou seja, tem uma sessão que esta bloqueando outra. Verificamos que a sessão 1130, esta com o blocking_session 954, e é realmente essa sessão que esta bloqueando de conseguir o exclusive lock. Para resolver drasticamente o problema, podemos acabar, matar, com a sessão que esta bloqueando, com o comando ALTER SYSTEM.

ALTER SYSTEM KILL SESSION '954,4546' IMMEDIATE;

Dessa forma acabamos com a sessão que esta bloqueando a outra e a transação sofrerá um rollback. O segundo número após o número do SID, é o serial# que também está na view V$SESSION. Isso é necessário pois o Oracle começa a reutilizar o número da sessão em outras sessões, mas com o SERIAL# esse conjunto fica único durante a vida da instância, nunca irá se repetir.

Existe outra view que pode nos ajudar, que é a V$LOCKED_OBJECT, onde tem uma lista de todos os locks adquiridos por todas as sessões no banco. Nessa view tem o object_id, informando qual objeto está em lock e também o SID da sessão que adquiriu o lock.

Anúncios

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: , , ,
Publicado em Administração, Concurrency

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 138.077 Visualizações
%d blogueiros gostam disto: