Entendendo os Locks em Bancos de Dados: Como Resolver Conflitos e Manter a Integridade dos Dados

Bloqueios (Locks)

Olá á todos, hoje resolvi falar sobre um assunto que acredito ser interessante, este assunto por vezes nos assalta todos os dias, quem é que já não passou pela seguinte situação, estamos trabalhando eis que derepente chega um developer e nos faz a pergunta chave : “Tem como você ver se alguem esta “lockando” essa tabela?”

Pois bem, iremos navegar um pouco por esse assunto agora, os LOCK’S, espero que apreciem.

Conceito

Os lock’s se dão, de um modo geral, quando duas ou mais transações concorrem em relação a um determinado recurso em especifico, concorrem no sentido de utilização, os tipos de recursos aqui citados serão melhor explicados e citados quais seus respectivos tipos. Portanto quando uma determinada sessão de banco de dados esta transacionando e utilizando um determinado recurso, e esta por sua vez não o libera, uma sessão subsequente que tentar utilizar este recurso poderá entrar em estado de “WAIT”, pelo fato de a sessão que entrou e “reservou” aquele recurso para efetuar sua tarefa primeiramente estar “lockando”, ha então neste caso uma situação tipica de LOCK presente.

Esta situação pode ser solucionada por dois meios simples, ou você finaliza a sessão com um KILL SESSION ou então simplesmente a conclui por meio do COMMIT , caso tenha certeza de sua efetivação ou ainda por meio de um ROLLBACK caso haja dúvidas quando a certeza de sua transação.

Vejamos agora algumas incidências dos Lock’s, até para que possamos ilustrar melhor tal problemática.

BLOQUEIOS REGULARES

Ocorre quando uma transação aguarda pela liberação de um recurso que esta sendo usado por outra sessão.

Veja:

update emp
set name='YourName'
where id=10;

Aqui temos um lock do tipo bloqueio a nível de linha realizado pela clausula ID = 10, que só será liberado após a transação principal realizar seu COMMIT ou ROLLBACK. Poderiamos então sugerir o seguinte questionamento: Porque o Oracle faz isso? E a resposta é quase que automatica, para que possamos manter a integridade dos dados, já pensou se todos pudessem atualizar, deletar ou inserir tudo a todo momento sem nenhum tipo de controle? É quase que impossível de imaginar tal CAOS.

DEADLOCK:

Ocorrem quando duas transações ( um conjunto de declarações SQL que executam uma ação especifica),ficam aguardando que uma libere um determinado recurso para que outra o utilize.

Essa situação pode acabar por acontecer pelo fato de existir essa “co-espera”, por parte das transações, onde fatalmente entraram em uma situação de deadlock. E como o Oracle entene isso? Bem, internamente o Banco de Dados Oracle , possui um algoritmo identificador desses casos, que vai não só localizar essas situações dentre as transações correntes como também vai soluciona-las. Uma desvantagem é que o Banco de Dados tem seu próprio tempo para que entre em ação esse algoritimo solucionador, e se no caso esta situação estiver gerando problemas maiores, então se faz necessário uma intervenção do DBA para que possa dar rápida resolução a questão. Vale lembrar que as soluções para essa situação é a mesma que o próprio algoritimo faria, ou seja, eliminar uma das transações envolvidas em DEADLOCK, no entanto o melhor mesmo é identificar essas situações e tomar providencias para que não ocorram novamente em seu sistema.

TIPOS DE RECURSOS

Abaixo elecanrei algumas tipagens de recursos que são envolvidos em situações de lock, são eles:

• Tabela(s)
• Linhas de uma Tabela
• Pacotes Armazenados

TIPOS DE BLOQUEIOS

Dentre os vários tipos de locks existentes , nós podemos separa-los em :bloqueios implícitos,bloqueios explicitos,bloqueios no nivel de linha e bloqueios no nivel de tabela.

Vamos analisar cada uma dessas situações.

• BLOQUEIOS IMPLICITOS : São os bloqueios que o próprio sistema mantém como forma de garantir a integridade das informações contidas nas tabelas, como ocorre em incidências de comandos do tipo UPDATE, onde ha um lock exclusivo que só será liberado após um COMMIT ou ROLLBACK.
• BLOQUEIOS EXPLICITOS : São os bloqueios por vontade o usuário, quando serão realizadas algumas tarefas a nível de testes e o usuário que acesso exclusivo naquele objeto, e pode ser obtido através da instrução LOCK TABLE, muito usada pelos developer’s de um modo geral. Este tipo de bloqueio deve ser usado com responsábilidade , pois implicará em indisponibilidade de um ou mais objetos por parte de quem estiver precisando acessar os objetos envolvidos nesse tipo de bloqueio, portanto muita atenção ao utiliza-lo.
• BLOQUEIOS A NÍVEL DE LINHA : esses são os lock’s comuns diários efetuados por usuários durante o dia-a-dia de uma base de dados transacional, onde vários usuários podem fazer bloqueios compartilhados ao mesmo tempo.
• BLOQUEIO A NÍVEL DE TABELA : apenas um bloqueio pode se colocado neste nível, e se compara muito ao bloqueio exclusivo só que á nivel de tabela.

De acordo com as afirmações do autor do livor ORACLE DBA Scripts de Automação – Rajendra Gutta, ocorre o seguinte:

“Se apenas um usuário colocar um bloqueio compartilhado, ele pode inserir,excluir ou atualizar. Se mais de um usuário colcoar um bloqueio compartilhado na mesma tabela, ninguém mais pode inserir, excluir ou atualizar.”
Nessa linha de raciocinio podemos concluir que, uma pessoa com um bloqueio do tipo exclusivo tem a opção de fazer insert’s, update’s e delete’s nesta tabela e quando ocorrer de mais pessoas realizarem bloqueios nas tabelas do tipo compartilhado a essas serão concedidos direitos de realizarem inserções, atualizações e deleções.

As transações de um modo geral consistem em comandos DDL ( Data Definition Language ), ou ainda DML ( Data Manipulation Language ). Podemos encontrar várias instruções DML ao mesmo tempo, essas formam uma transação que necessitam de processo manual de conclusão através das opções já citadas COMMIT ou ROLLBACk, o que não processa para as transações DDL, essas formam suas próprias transações.

Porque há a necessidade de acesso exclusivo para as declarações DDL’s? Pelo fato de que essas irão alterar a estrutura do objeto, e podem ser do tipo CREATE ou ALTER.

Para as declarações DML’s, podemos identificar quase que todos os tipos de bloqueios, para essas encontramos os comandos INSERT, UPDATE, DELETE e SELECT. Em um jogo de palavras podemos dizer que :

• SELECT esta para lock share e UPDATE esta para LOCK EXCLUSIVE.

Em determinadas situações o Oracle nos permite fazer o lock de alguns registros apenas, para que não haja um comprometimento do objeto como um todo. E para que esse mecanismo fique claro vamos ver o que Rajendra Gutta diz em seu livro :

“…um bloqueio nunca evita que os outros usuários consultem a tabela, e uma consulta nunca coloca um bloqueio em uma tabela, exceto quando uma consulta de atualização é feita.”

SITUAÇÕES COMUNS ONDE PODEMOS TER PROBLEMAS

• Lock por falta de indice em tabela.
• Lock por Resurce Busy ou por um processo ou uma transação.

SCRIPTS PARA IDENTIFICAR LOCKS:
Fonte : ORACLE DBA Scripts de Automação
Autor: Rajendra Gutta

/*  Displays locks on the objects  and
also gives the SQL statement causing the lock */

set echo off
set feedback on
set linesize 1000
col sid format 99999
col serial format 99999
col username format a20
col osuser format a10
col owner format a10
col object_name format a30
col machine format a15
col program format a35
col lockmode format a10
prompt ----Sessions and objects involved in the locks

Select  distinct s.sid,s.serial#,
s.username,
s.status,
s.osuser,
p.spid "OS Pid",
o.object_name,
decode(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive',
to_char(l.locked_mode)) "LockMode",
s.lockwait,
s.program,s.taddr
from dba_objects o ,
v$locked_object l,
v$session s,
v$process p,
v$sqltext t
where l.object_id=o.object_id
and  l.session_id = s.sid
and  s.paddr = p.addr
and  t.address = s.sql_address
and  t.hash_value = s.sql_hash_value
order by sid,serial#;

prompt ----SQL statements sessions currently executing

 Select  distinct s.sid,
s.serial#,
p.spid "O.S|Id",
t.piece,
t.sql_text
from dba_objects o ,
v$locked_object l,
v$session s,
v$process p,
v$sqltext t
where l.object_id=o.object_id
and  l.session_id = s.sid
and  s.paddr = p.addr
and  t.address = s.sql_address
and  t.hash_value = s.sql_hash_value
order by sid,serial#;

Agora vamos entender alguns procedimentos de resolução de bloqueios (lock’s), vejamos:

1. Identificar as sessões que estão em Lock
2. Eliminar a sessão que mantem o lock
3. Verificar se ha algum processo background ou amarrado ao S.O
4. Eliminar o processo background aliado a sessão em lock

Ainda podemos contar com alguns artificios a nível de sistema operacional para nos auxiliar na eliminação desses processos Background ligados as sessões em lock, no caso da paltaforma Windos contamos com o ORAKILL um aplicativo externo da Oracle que vem junto com o pacote padrão de instalação, e no caso de ambientes Unix podemos nos valer do poderoso KILL -9 na hora de eliminar esses DAEMON’s, que muitas vezes atrapalham.

Bem , era isso que eu queria compartilhar com vocês, espero que apreciem e comentem, quaisquer dúvidas vocês podem me contactar neste email : drb.ricardo@gmail.com, terei prazer em responder aos questionamentos. Obrigado á todos!

David

Referências

• Scripts de Automação Oracle para DBA / Rajendra Gutta; tradução de Kátia Roque. – Rio de Janeiro : Campus, 2002