Identificando e resolvendo disputas por bloqueio no Oracle

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

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!