GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

Conflitos de bloqueios

O Oracle como outros bancos de dados, deve gerenciar múltiplos usuários acessando a mesma data simultaneamente, garantindo o controle de concorrência do banco de dados.

Conflitos de bloqueios (lock conflicts) ocorrem quase que frequentemente durante as operações das transações, mais geralmente são quase todas resolvidos pelo próprio mecanismo de fila da Oracle. Para entender melhor o lock conflicts observe a tabela abaixo.

TRANSAÇÃO 1

TEMPO

TRANSAÇÃO 2

UPDATE PRODUTO SET valor = 30 WHERE id_produto = 1; 1 row updated. 08:00 UPDATE PRODUTO SET valor = 50 WHERE id_produto = 2; 1 row updated.
UPDATE produto SET valor = 52 WHERE id_produto = 2;

Nenhuma linha atualizada no momento, pois a transação 1 entra em lock conflict com a transação 2.

08:15 Transação continua normal, utilizando selects, inserts, updates e até mesmo deletes na mesma ou em outras tabelas.
Sessão ainda aguardando 08:20 Transação continua normal, utilizando selects, inserts, updates e até mesmo deletes na mesma ou em outras tabelas.
1 row updated.

Liberado o lock, a sessão continua normalmente.

08:25 commit;

A tabela mostra a atividade, de duas transações. Observe no horário das 08:15 que a transação 1 não conseguiu fazer o update, pois a transação 2 estava como lock na linha. Somente as 08:25, quando o commit da transação 2 é feito, o lock é liberado que a transação 1 consegui realizar a operação.

Como em nenhum ambiente o cenário é perfeito (como o do meu quadro apresentado), o usuário não vai ficar esperando cinco minutos um simples update de outra transação ser liberado para ele trabalhar, ele vai imediatamente contatar você que o banco está lendo, não esta funcionando, e por ai vai…

Evitando os lock conflicts.

Obviamente para se ter um pouco de prevenção dos conflitos, é necessário assegurar que nenhuma transação execute por longos períodos de tempo sem um commit (DDL, DCL). A cada mudança, assim que necessário, pedir para o LGWR trabalhar (processo de commit), fazendo a gravação dos dados persistente nos arquivos de Redo e consequentemente nos Datafiles

Detectando os lock conflicts.

É realmente fácil detectar usuário que sofrem bloqueios e os que fazem o bloqueio. Para isso criaremos um ambiente para o melhor entendimento do conceito. O script abaixo cria o usuário usr1 e o usuário usr2.

SQL> create user usr1
2  profile default
3  identified by oracle
4  quota unlimited on users
5  account unlock;

 Usuário criado.

SQL> create user usr2
2  profile default
3  identified by oracle
4  quota unlimited on users
5  account unlock;

Usuário criado.

SQL> grant create table, connect to usr1, usr2;

 Concessão bem-sucedida.

Agora com o usuário usr1 criamos a tabela de produto e disparamos dois insert.
SQL> create table produto

2  (
3     id_produto number primary key,
4     valor number(10,2)
5  )
6  /

 Tabela criada.

SQL> insert into produto values (&id, &valor);
Informe o valor para id: 1
Informe o valor para valor: 50

1 linha criada.

SQL> /
Informe o valor para id: 2
Informe o valor para valor: 5

1 linha criada.

SQL> commit;

Commit concluído.

Ainda como o usuário usr1, vamos dar a permissão de update para o usuário usr2, assim garantimos que o usr2 poderá acessar a tabela de usr1.

SQL> grant update on produto to usr2;

Concessão bem-sucedida.

Agora sim, nosso ambiente está pronto para detectar os locks. Com o usuário usr1 faça um update atualizando a coluna valor para 35 onde o id_produto é igual a 1.

SQL> update produto set valor = 35
2 where id_produto = 1;

1 linha atualizada.

Em outro terminal, com o usuário usr2 também atualizamos a coluna valor onde o id_produto é igual a 1, observe o lock entrando em ação.

SQL> update usr1.produto set valor = 36
2 where id_produto = 1;

A sessão do usr2 fica aguardando algum commit da transação do usr1. Para conseguir detectar sessões que estão bloqueando outras sessões, basta executar o select baixo.

SQL> select username from v$session
2 where sid in (
3 select blocking_session from v$session
4 )
5 /

USERNAME
------------------------------
USR1

Verificando usuários que estão aguardando commit de outra sessão.

SQL> select sid, username, blocking_session
2 from v$session
3 where blocking_session is not null;


SID USERNAME BLOCKING_SESSION
----- ------------ ----------------
136  USR2        141

Usuários que estão com o lock, e usuário que estão aguardando a outra transação.

SQL> select sid, username, blocking_session
2 from v$session where blocking_session is not null
3 UNION ALL
4 select A.sid, A.username, A.blocking_session
5 from v$session A, v$session B
6 where A.sid = B.blocking_session;


SID USERNAME BLOCKING_SESSION
---- ---------- ----------------
136  USR2                 141
141  USR1

Resolvendo lock conflicts.

Somente é possível resolver conflitos de bloqueios através de duas maneiras:

1) Emitir um commit na sessão que está bloqueando
2) Terminar a sessão que está bloqueando.

A melhor maneira de resolver é contatar o usuário que está segurando o lock e pedir para liberar, afim de poder completar a transação. Como nem sempre está opção é possível, existe uma maneira mais rápida radical, que seria finalizar a sessão pelo modo kill do Oracle.

ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’ IMMEDIATE;

SQL> select sid, serial#
2  from v$session where username = 'USR1';

        SID          SERIAL#
---------- ----------
141            46

SQL> alter system kill session '141, 46' immediate;

 Sistema alterado.

Depois de alguns segundos, se voltarmos para o terminal do usuário usr2, pode observar que o update antes que estava aguardando terminar o lock do usuário usr1, é atualizada.

1 linha atualizada.
SQL>

Por fim, aqui está outras views com informações de lock disponível no Oracle:

V$ENQUEUE_LOCK
V$EVENT_NAME
V$LOCK
V$_LOCKV
$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT

Uma dica do nosso amigo Jorge, para ambientes RAC, as views como V$SESSION e V$LOCK mostram somente os dados atuais da instância em que o usuário está conectado. Para resolver esse problemas existem as views :

GV$ENQUEUE_LOCK
GV$LOCK_ELEMENT
GV$LOCKS_WITH_COLLISIONS
GV$LOCKED_OBJECT
GV$LOCK
GV$EVENT_NAME
GV$LOCK_ACTIVITY

Que mostram os dados independente, do cluster que ele estará.

Obrigado pela dica Jorge.

Share

6 Responses

  1. Dica útil em caso de ambiente RAC
    A view V$SESSION mostra as sessões da instância que o usuário que está realizando a consulta a esta view está conectado. Se a sessão do bloqueio está em outra instância, esta view não vai mostrar a sessão que está causando o bloqueio, e o DBA vai jurar até a morte que não tem ninguém bloqueado.
    Para não ter essa dúvida, use a view GV$SESSION ao invés de V$SESSION. Como neste caso, as views com prefixo “G” (GV$ENQUEUE_LOCK, GV$LOCK_ELEMENT, GV$LOCKS_WITH_COLLISIONS, GV$LOCKED_OBJECT, GV$LOCK, GV$EVENT_NAME, GV$LOCK_ACTIVITY) vão mostrar os dados independente do cluster onde está a sessão bloqueante.

    Fui claro?

  2. Flávio Soares disse:

    Realmente, os teste que eu fiz aqui, as views V$LOCK e V$SESSION, mostram somente os dados da atual instância em que o usuário está conectado.

    Atualizarei o post colocando as suas dicas Jorge.

    Muito obrigado : ) …

  3. Mauro disse:

    Só acho que faltou você falar um pouco de DEAD LOCK, que se encaixaria nesse mesmo contexto.

    Mas mesmo assim o post está muito bom.

  4. Flávio Soares disse:

    Tá ai Mauro … em uma outra oportunidade falarei sobre dead lock no Oracle.

    Obrigado pelo feedback : )

  5. Bruno Murassaki disse:

    Boa tarde Flavio Parabens pela OCA.

    Gostaria de saber se você sabe em qual view eu consigo verificar qual o DML que esta causando o LOCK, pois eu tentei fazer um join com as Views V$session e V$sql ou V$sqlarea mas não tive sucesso.
    Pois a sessão causadora é facil descobrir, então eu gostaria de descobrir o SQL que esta causando.

    Desde ja obrigado pela atenção

    Bruno Murassaki
    DBA ORACLE

  6. Flávio Soares disse:

    Olá Bruno,

    agora vc me pegou … eu realmente não sei essa, sei somente descobrir qual sessão e objeto está causando o lock. Vou fazer alguns testes aqui, se descobrir eu aviso aqui no blog. Se vc conseguir passa a dica ai para o pessoal.

    Grande abraço.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *