Gerenciamento de Locks e Sessões no Oracle Database

Configuração de Perfil e Usuários no Oracle

Passo 1: Criar Perfil para Usuário

O primeiro passo consiste em criar um perfil para limitar os recursos do usuário, como o número de sessões simultâneas.

CREATE PROFILE perfil_padrao LIMIT SESSIONS_PER_USER 1;

Passo 2: Criar Primeiro Usuário para Simulação

Criaremos o usuário usr1 associado ao perfil criado anteriormente.

CREATE USER usr1 
PROFILE perfil_padrao 
IDENTIFIED BY oracle 
QUOTA UNLIMITED ON users ACCOUNT UNLOCK;

Passo 3: Criar Segundo Usuário para Simulação

Da mesma forma, criaremos o usuário usr2 para simular o bloqueio (lock).

CREATE USER usr2 
PROFILE perfil_padrao 
IDENTIFIED BY oracle 
QUOTA UNLIMITED ON users ACCOUNT UNLOCK;

Gerenciamento de Permissões

Conceder e Revogar Acessos

Utilize os comandos abaixo para gerenciar as permissões de criação de tabela e conexão para os usuários usr1 e usr2.

-- Comando para conceder permissão
GRANT CREATE TABLE, CONNECT TO usr1, usr2;

-- Comando para revogar permissões
REVOKE CREATE TABLE, CONNECT FROM usr1, usr2;

Simulação de Transações e Bloqueios (Locks)

Passo 5: Criar Tabela de Produtos

Conecte-se com o usuário usr1 e crie a tabela de produtos para os testes.

CONNECT usr1/oracle;

CREATE TABLE produto (
  id_produto VARCHAR2(5) PRIMARY KEY,
  valor NUMBER(10,2)
);

INSERT INTO produto VALUES (1, 50);
INSERT INTO produto VALUES (2, 5);

Passo 6 e 7: Validar Operações e Conceder Acesso ao usr2

Após inserir os dados, é necessário efetivar a transação e permitir que o usr2 realize atualizações.

COMMIT;

GRANT UPDATE ON produto TO usr2;

Passo 8: Realizar Update no Usuário 1

No usuário usr1, realize um update atualizando a coluna valor para 35, onde o id_produto é igual a 1.

UPDATE produto SET valor = 35 WHERE id_produto = 1;

Passo 9: Simular o Lock no Usuário 2

No usuário usr2, em outra sessão, tentaremos atualizar a mesma linha. Observaremos que o lock entra em ação, deixando a sessão em espera.

UPDATE usr1.produto SET valor = 36 WHERE id_produto = 1;

Identificação e Resolução de Bloqueios

Passo 10: Detectar Sessões Bloqueadoras

O comando abaixo identifica quais usuários estão bloqueando outras sessões no banco de dados.

SELECT username FROM v$session 
WHERE sid IN (SELECT blocking_session FROM v$session);

Passo 11: Verificar Usuários Aguardando Commit

Identifique quais sessões estão aguardando a liberação de um recurso (blocking_session não nulo).

SELECT sid, username, blocking_session FROM v$session 
WHERE blocking_session IS NOT NULL;

Passo 12: Relatório Completo de Bloqueios

Utilize a consulta abaixo para listar tanto quem está bloqueado quanto quem é o causador do bloqueio.

SELECT sid, username, blocking_session FROM v$session 
WHERE blocking_session IS NOT NULL 
UNION ALL 
SELECT a.sid, a.username, a.blocking_session 
FROM v$session a, v$session b 
WHERE a.sid = b.blocking_session;

-- Consulta formatada com aliases
SELECT sid AS "Qual SID", username AS "Usuario", blocking_session AS "Bloqueando a Sessao" 
FROM v$session 
WHERE blocking_session IS NOT NULL 
UNION ALL 
SELECT a.sid, a.username, a.blocking_session 
FROM v$session a, v$session b 
WHERE a.sid = b.blocking_session;

Passo 13 e 14: Finalizar a Sessão Bloqueadora

Para resolver o lock, identifique o SID e o SERIAL# da sessão do usr1 e finalize-a.

-- Identificando SID e SERIAL#
SELECT sid, serial# FROM v$session WHERE username = 'usr1';

-- Finalizando a sessão de forma imediata
ALTER SYSTEM KILL SESSION '27,316' IMMEDIATE;