Conflitos de bloqueios

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.

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.

1 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.