Identificando e resolvendo disputas por bloqueio no Oracle
Postado por Adauto Santos
O Oracle implementa dois tipos de bloqueios o Exclusivo e o Compartilhado. Pode-se imaginar um cenário hipotético em que o usuário JOSE executa um UPDATE (sem COMMIT) alterando a UF do cliente com código igual a 10020 (UPDATE CLIENTE SET UF=’RS’ WHERE CODIGO=10020) e na sequência a usuária MARIA deseja colocar o nome do mesmo cliente com as letras iniciais em maiúsculo (UPDATE CLIENTE SET NOME=INITCAP(NOME) WHERE CODIGO=10020).
Essa situação fará com que a instrução de MARIA espere a liberação de JOSE, pois o registro do cliente 100252 está sob um bloqueio exclusivo. Além disso, toda a tabela precisa ser bloqueada (bloqueio compartilhado) para que ela não seja alterada (ou até mesmo removida) durante o processo de alteração. A figura a seguir ilustra o cenário proposto.
O comando
Para identificar todas as sessões com bloqueios exclusivos basta apenas utilizar a consulta SQL que se segue:
-- Consulta para obter os bloqueios exclusivos (Registros)
SELECT SUBSTR(L.INST_ID,1,2) INSTANCIA,
SUBSTR(DECODE(L.REQUEST, 0,'HOLDER: ', 'WAITER: ') || L.SID, 1, 12) SESSAO,
S.SERIAL#,
S.OSUSER,
S.MODULE,
L.ID1,
L.ID2,
L.LMODE,
L.REQUEST,
L.TYPE
FROM GV$LOCK L,
GV$SESSION S
WHERE L.SID = S.SID
AND (L.ID1, L.ID2, L.TYPE) IN (SELECT L2.ID1, L2.ID2, L2.TYPE FROM GV$LOCK L2 WHERE L2.REQUEST > 0)
ORDER BY L.ID1, L.REQUEST;
Para identificar todos os objetos bloqueados (compartilhado) pode-se utilizar a consulta:
-- Consulta para obter os bloqueios compartilhados (Objetos)
SELECT SUBSTR(O.OBJECT_NAME,1,25) OBJETO,
L.SESSION_ID SESSION_ID,
L.ORACLE_USERNAME ORA_USER,
L.OS_USER_NAME OS_USER
FROM DBA_OBJECTS O,
V$LOCKED_OBJECT L
WHERE L.OBJECT_ID = O.OBJECT_ID
ORDER BY 1,3,4;
A explicação
A implementação destes bloqueios pelo Oracle é fundamental para a proteção das transações. Voltando ao exemplo do início do artigo, seria problemático que uma operação manipulasse um determinado registro, sendo que ele nem mesmo foi confirmado pelo comando anterior. Imagine um cenário de alteração de preços em que dois funcionários fizessem a adequação de um preço exatamente ao mesmo tempo, qual seria o preço correto? Pode parecer inusitado, mas essa situação está suscetível a acontecer em ambientes com milhares (ou milhões) de transações simultâneas.
O Oracle isola as transações realizadas pelo usuário do resto do mundo até que ele de fato as confirme com a utilização do comando COMMIT, que por sua vez a tornará durável no banco e disponível para os demais usuários. O objetivo do presente artigo não é aprofundar nos aspectos técnicos do COMMIT, mas é importante que se saiba que além de tornar a transação durável o COMMIT também fará com que os bloqueios compartilhados e exclusivos sejam liberados.
Meus testes
Para ilustrar a situação de bloqueio exclusivo e compartilhado, iremos abordar o cenário de uma atualização de e-mail de um mesmo cliente por duas instruções. Para isso, será criada a tabela CLIENTE com alguns registros.
CREATE TABLE CLIENTE
(
CODIGO NUMBER
NOME VARCHAR2(50)
EMAIL VARCHAR2(50)
DATA_NASCIMENTO DATE
UF CHAR(2)
CODIGO_MUNICIPIO_IBGE NUMBER(10)
NR_CPF NUMBER(11)
REGIAO_BRASIL CHAR(2)
SEXO CHAR(1)
DATA_EXCLUSAO DATE
);
SQL> SELECT CODIGO, NOME FROM CLIENTE;
CODIGO NOME EMAIL
---------- -------------------------------------------------- --------------------------------------------------
1 Gabriel Almeida Martins gabriel.martins@gmail.com
2 Luana Castro Alves luana.alves@hotmail.com
3 Nicole Souza Costa nicole.costa@yahoo.com.br
50 Raissa Ribeiro Rocha raissa.rocha@gmail.com
51 Guilherme Pereira Cunha guilherme.cunha@hotmail.com
52 Renan Almeida Dias renan.almeida@gmail.com
100352 Luiza Silva Azevedo luiza.azevedo@yahoo.com.br
100353 Martim Ribeiro Gomes martim.gomes@hotmail.com
Na sequência iremos alterar o e-mail do cliente Gabriel Almeida Martins (CODIGO="1") para gabriel.martins@novoemail.com.br, mas não efetuaremos o COMMIT.
SQL> update cliente set email='gabriel.martins@novoemail.com.br' where codigo = 1; 1 linha atualizada.
Logo em seguida, iremos abrir um novo terminal com o SQL*Plus e alterar o e-mail do cliente Gabriel Almeida Martins para , mas não efetuaremos o COMMIT.
SQL> UPDATE CLIENTE SET EMAIL='GABRIEL.MARTINS@NOVOEMAIL2.COM.BR' WHERE CODIGO = 1;
A sessão ficará congelada, evidenciando o aguardo da segunda sessão em relação a primeira. Executando os SQL da sessão anterior obteremos os seguintes resultados:
-- Bloqueio exclusivo INST SID SERIAL# OSUSER MODULE -------- ------------ ---------- ------------------------- ------------ 1 HOLDER: 99 8781 desktop-PC\adauto.santos sqlplus.exe 1 WAITER: 15 10815 desktop-PC\adauto.santos sqlplus.exe -- Bloqueio compartilhado OBJETO SESSION_ID ORA_USER OS_USER -------- ---------- --------- ------------------------------ CLIENTE 15 ADAUTO desktop-PC\adauto.santos CLIENTE 99 ADAUTO desktop-PC\adauto.santos
Essa situação poderá perdurar durante algum tempo, até que a comando seja de fato confirmado com o COMMIT, que a sessão finalize por algum motivo (ex: queda de energia) ou que a situação seja resolvida com a execução do comando abaixo:
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @ID_INSTANCIA;
Lembrando que o encerramento da sessão sem o seu devido COMMIT fará com que a alteração não tenha efeito, sendo assim o e-mail será o anterior (gabriel.martins@gmail.com).
