Pedro F. Carvalho Analista de Sistemas contato@pedrofcarvalho

Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
GERENCIANDO DADOS E CONCORRÊNCIA
Pl/SQL
PL/SQL significa "Procedural Language extensions to SQL". O PL/SQL pode ser usado
em bancos de dados Oracle, no Oracle Server, em ferramentas clientes, isto é, Oracle
Forms. O PL/SQL é muito similar à linguagem SQL mas acrescenta construções de
programação
similares
a
outras
liguagens.
Procedure
Para criar uma procedure. Uma procedure é um grupo de comandos PL/SQL que
realizam uma tarefa.
Sintaxe
CREATE [OR REPLACE] PROCEDURE [schema.]procedure
[(argument[IN I OUT I IN OUT] datatype
[,argument [ IN I OUT I IN OUT] datatype]...)]
{IS I AS} pl/sql_subprogram_body
Parâmetros
OR
REPLACE
Recria a procedure se esta já existir.
schema
Dono da procedure.
procedure
Nome da procedure.
argument
Parâmetro da procedure.
IN
Indica que você deve especificar o valor deste
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
parâmetro na chamada da procedure.
OUT
Indica que a procedure ira retornar um valor
por este parâmetro.
IN OUT
Através deste parâmetro deve-se informar um
valor para procedure e este voltara modificado.
datatype
Tipo de dado do parâmetro
pl/sql_subprogram_body
Codificação da procedure
Exemplo que cria a procedure totaliza_nota:
CREATE PROCEDURE totaliza_nota (p_cod_cliente IN number)
AS BEGIN
UPDATE nota_fiscal
SET val_total = val_total + (SELECT sum(valor * qtd) FROM itens WHERE
itens.nr_nota = nota_fiscal.nr_nota)
dt_processamento = sysdate
WHERE dt_processamento is NULL and cod_cliente = p_cod_cliente;
COMMIT;
END;
Alter Procedure
Para recompilar uma procedure.
Sintaxe
ALTER PROCEDURE [schema.]procedure COMPILE
Drop Procedure
Para remover uma procedure da base.
Sintaxe
DROP PROCEDURE [schema.]procedure
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Executando Procedure
Exemplo
BEGIN
NomeProc(Param);
END;
Ou
EXEC NomeProc(Param);
Dentro de um bloco:
BEGIN
NomeProc(Param);
END;
/
FUNCTION
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Create Function
Cria uma função de usuário.
Uma função é um conjunto de comandos PL/SQL normalmente utilizados para efetuar
um cálculo ou manipular um valor. Podem ser utilizadas dentro de comandos SQL.
Nas functions não é aconselhavel fazer alterações no status da base, por exemplo,
incluir, alterar, deletar registros. Ao alterar o status do banco, esta função não poderá
ser utilizada dentro de comandos SQL. Assim a utilidade da function é
descaracterizada, assumindo a função da procedure que é própria para alterar dados.
Sintaxe
CREATE [OR REPLACE] FUNCTION [schema.]function
[ (argument [IN] datatype
[, argument [IN] datatype] ...)]
RETURN datatype
{IS | AS} pl/sql_subprogram_body
Parâmetros
OR REPLACE
Recria a função se esta já existir.
schema
Dono da função.
function
Nome da função
argument
Nome de um parâmetro da função.
IN
Indica que você pode fornecer um valor no
momento da chamada da função.
datatype
É o tipo de dado do parâmetro
RETURN
datatype
Especifica o tipo de dado que a função deve
retornar.
pl/sql_subprogram_body
É o código da função.
Exemplo I
Função que calcula a media dos salário de um determinado departamento:
CREATE FUNCTION cal_media(p_deptno IN NUMBER)
RETURN NUMBER IS v_media number;
BEGIN
SELECT avg(sal)
INTO v_media
FROM emp
WHERE dept_no = p_deptno;
RETURN(v_media);
END;
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
A utilização da função criada acima em um comando SQL:
SELECT cal_media(deptno) FROM dept;
exemplo II
CREATE OR REPLACE FUCTION media(p_dept number DEFAULT -1)
RETURN NUMBER IS v_media number;
BEGIN
IF p_dept = -1 then
SELECT avg(salario) INTO v_media
FROM funcionario;
ELSE
SELECT avg(salario) INTO v_media
FROM funcionario WHERE cod_dep = p_dept;
END IF;
RETURN (v_media);
END;
Drop Function
Remove uma função da base.
Exemplo
DROP FUNCTION cal_media;
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
TRIGGERS
Create Trigger
Cria e habilita triggers. Trigger é um bloco de código PL/SQL associado a uma tabela.
Se um comando SQL for executado sobre esta tabela as triggers são disparadas
automaticamente pelo Oracle. Normalmente as regras de negócio são implementadas
em triggers.
Sintaxe
CREATE [OR REPLACE] TRIGGER [schema.]trigger
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...
ON [schema.]table
[ [REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old] } ]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql_block
Parâmetros
OR
REPLACE
schema
Recria a trigger se esta já existir.
Nome do usuário dono da trigger
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
table
Nome da tabela à qual a trigger esta associada
trigger
Nome da trigger que esta sendo criada.
BEFORE
Indica ao ORACLE para disparar a trigger antes
de executar o comando
AFTER
Indica ao ORACLE para disparar a trigger
depois de executar o comando
DELETE
Indica que esta trigger deve ser disparada
quando um comando DELETE apagar uma linha
da tabela.
INSERT
Indica que esta trigger deve ser disparada
quando um comando INSERT adicionar uma
linha da tabela.
UPDATE OF
Indica que esta trigger deve ser disparada
quando um comando UPDATE alterar uma das
colunas especificadas na clausula OF
REFERENCIN
G
Usado para nomes correlacionados. Você pode
usar correlação de nomes em blocos PL/SQL e
em cláusulas WHEN de uma trigger para fazer
referência de antigos e novos valores da linha
corrente.
FOR
ROW
EACH Indica que a trigger deve ser disparada a cada
linha afetada pelo comando
WHEN
Contém uma condição SQL que deve ser
verdadeira para permitir o disparo da trigger.
Bloco PL/SQL que indica a ação a ser
executada pela TRIGGER
Quando uma trigger é criada para mais que uma operação DML, pode-se utilizar
constantes condicionais no corpo da trigger para executar determinado bloco de
comandos, dependendo do comando que a disparou. As constantes condicionais são as
seguintes:
pl/sql_block
INSERTING
TRUE se o comando que disparou a trigger foi
um INSERT.
DELETING
TRUE se o comando que disparou a trigger foi
um DELETE.
UPDATING
TRUE se o comando que disparou a trigger foi
um UPDATE.
UPDATING (column_name)
TRUE se o comando que disparou a trigger foi
um UPDATE e a coluna column_name foi
alterada.
Obtendo o conteúdo dos campos em triggers:
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
:NEW.Nome_do_C
ampo
BEFORE
Se a trigger for
disparada
BEFORE
os
dados
podem
ser alterados.
Retorna o conteúdo
novo do campo.
No DELETE não é
utilizado pois não há
alteração de valores,
consequentemente
não há valor novo.
:OLD.Nome_do_C
ampo
AFTER
Se a trigger for
disparada
AFTER os dados
não podem ser
alterados.
BEFORE
Retorna o conteúdo
antigo do campo e
nunca
pode
ser
alterado.
AFTER
No INSERT não é
utilizado pois não há
valores antigos a
inserção.
Se a operação for
INSERT retornará o
valor que poderá ser
inserido
pelo
comando SQL. No
UPDATE
o
comportamento será
o
mesmo,
pois
retornará o valor do
campo
com
as
alterações
que
poderão ser feitas.
Se a operação for
INSERT retornará o
valor
que
foi
inserido
pelo
comando SQL. No
UPDATE
o
comportamento será
o
mesmo,
pois
retornará o valor do
campo
com
as
alterações que foram
feitas.
Se a operação for
UPDATE retornará o
valor do campo antes
da alteração. No
DELETE retornará o
conteúdo do campo
atual.
Se a operação for
UPDATE retornará o
valor do campo antes
da alteração. No
DELETE retornará o
conteúdo do campo
atual.
Quando existem diversas triggers a serem executadas (before ou after), não se pode
dizer qual será a ordem de execução dessas triggers.
Triggers
BEFORE
Constrain Gravação Triggers
ts
AFTER
Recomenda-se o uso das triggers before para correção dos dados gravados em :new,
pois é esse o momento ideal para isso. Consistência de valores, checagem de dados
após a gravação e operações similares deve-se usar triggers after.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Declarando variáveis dentro de uma trigger
A variável V1 é do tipo numérica.
A variável V2 é do tipo coluna de uma tabela, no exemplo, da tabela ITEM campo
VAL_UNITARIO.
A variável V3 é do tipo linha de uma tabela, no exemplo, da tabela ITEM.
CREATE OR REPLACE trg_nome1
BEFORE INSERT OR DELETE OR UPDATE ON ITEM
FOR EACH ROW
DECLARE
V1 NUMBER;
V2 ITEM.VAL_UNITARIO%TYPE;
V3 ITEM%ROWTYPE;
BEGIN
...
END;
Gerando uma mensagem de erro
Usando trigger para restringir operações em uma tabela. No exemplo abaixo, irá exibir
uma mensagem de erro quando a operação é diferente de uma inserção.
CREATE OR REPLACE TRIGGER trg_nome2
BEFORE INSERT OR DELETE OR UPDATE ON ITEM
FOR EACH ROW
BEGIN
IF NOT INSERTING THEN
RAISE_APPLICATION_ERROR (-20000, 'Erro: não é permitida está
operação!');
END IF;
END;
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Exemplo I
O exemplo seguinte usa as constantes condicionais para determinar qual tipo de
comando SQL foi feito na tabela classified_table:
CREATE TRIGGER audit_trigger
BEFORE INSERT OR DELETE OR UPDATE ON classified_table
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_table
VALUES (USER || ' inseriu ' || ' novo valor: ' || :new.campo);
ELSIF DELETING THEN
INSERT INTO audit_table
VALUES (USER || ' apagou ' || ' valor antigo: ' || :old.campo);
ELSIF UPDATING('FORMULA') THEN
INSERT INTO audit_table
VALUES (USER || ' alterou ' || ' formula antiga: ' || :old.formula || '
formula nova: ' || :new.formula);
ELSIF UPDATING THEN
INSERT INTO audit_table
VALUES (USER || ' alterou ' || ' valor antigo: ' || :old.campo || ' valor
novo: ' || :new.campo);
END IF;
END;
Exemplo II
Este exemplo cria uma trigger que só permite alterações na tabela EMP sejam feitas
somente no horário comercial:
CREATE TRIGGER scott.emp_permit_changes
BEFORE DELETE OR INSERT OR UPDATE ON scott.emp
DECLARE
dummy INTEGER;
BEGIN
/* Se for Sábado ou Domingo retorna ERROR.*/
IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN')
THEN
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
raise_application_error( -20501, 'Você não pode alterar funcionário no
fim de semana');
END IF;
/* Compara hoje com todos os feriados da empresa. Se é feriado hoje,
então retorna ERRO.*/
SELECT COUNT(*) INTO dummy FROM company_holidays
WHERE day = TRUNC(SYSDATE);
IF dummy > 0 THEN
raise_application_error( -20501, 'Não é permitido alterar funcionário no
feriado');
END IF;
/*Se o horário atual for menor que 8:00AM ou maior 6:00PM, então retorna
ERRO. */
IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 18)
THEN raise_application_error( -20502, 'Só pode alterar funcionário no horário
do expediente');
END IF;
END;
Exemplo III
Este exemplo cria uma trigger que quando um novo funcionário é adicionado ou o
salário de um já existente é alterado, garante que o valor do salário esteja na faixa da
função do funcionário:
CREATE TRIGGER scott.salary_check
BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
DECLARE
minsal NUMBER;
maxsal NUMBER;
BEGIN
/* Pega o menor e o maior salário da função. */
SELECT minsal, maxsal INTO minsal, maxsal FROM sal_guide
WHERE job = :new.job;
/* Se o salário é menor que o mínimo ou maior que o máximo gera um ERRO */
IF (:new.sal < minsal OR :new.sal > maxsal) THEN
raise_application_error( -20601, 'Salario ' || :new.sal || ' fora da faixa para '
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
|| :new.job || ' do funcionario ' || :new.ename );
END IF;
END;
PACKAGES
O que é uma PACKAGE? Resposta, são áreas de armazenamentos de sub-programas,
constantes, váriaveis e cursores em PL/SQL que dependendo do modo que você
construir, você poderá compartilhar as informações desta PACKAGE com outros
aplicativos.
Uma PACKAGE é constituida de duas partes. A primeira é a PACKAGE SPECIFICATION
e a seguda é a PACKAGE BODY, onde atráves delas podemos definir a função do nosso
"pacote" dentro do banco de dados. A seguir irei comentar um pouco sobre cada parte.
PACKAGE SPECIFICATION
Tem como função de criar a interface de suas aplicações e definir os tipos de váriaveis,
cursores, exceções, nomear rotinas, e funções, tudo que se define na parte de
SPECIFICATION do seu PACKAGE poderá ser compartilhado com outros scripts ou
programas em SQL e PL/SQL.
PACKAGE BODY
Está parte da sua PACKGE tem como função executar por completo todas as suas
rotinas, cursores e etc, que você definiu na SPECIFICATION, deste modo, todas as
funções executadas dentro do BODY não poderão ser compartihadas com outras
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
aplicações, além disso no BODY você poderá criar detalhes e declarações que ficarão
invisíveis para outras aplicações.
Abaixo está um exemplo em SQL de PACKAGE SPECIFICATION e BODY.
Legenda dos códigos.
PACKAGE SPECIFICATION
PACKAGE BODY
SQL
CREATE OR REPLACE PACKAGE nome_package IS
>
2
TYPE REGTESTE IS RECORD (NOME VARCHAR2(50), SEXO CHAR(1));
3
CURSOR CTESTE (MATRICULA IN NUMBER) RETURN REGTESTE
4
FUNCTION EXCLUIR (MATRICULA IN NUMBER) RETURN BOOLEAN;
5
END nome_package;
6
/
SQL
CREATE OR REPLACE PACKAGE BODY nome_package IS
>
2
CURSOR CTESTE (MATRICULA IN NUMBER) RETURN REGTESTE IN
3
SELECT NOME, SEXO FROM Tabela WHERE SEXO=M;
4
BEGIN
5
DELETE FROM Tabela WHERE SEXO=M;
6
END;
7
END nome_package;
8
/
Como dito, percebemos que a PACKAGE SPECIFICATION foi compartilhada com a
PACKAGE BODY e a própria BODY não poderá ser compartilhada com outras aplicação.
Assim permitimos que alguma mudança na programação em SPEFIFICATION poderá
afetar outras aplicações que estão utilizando o PACKAGE.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
LOCK – CONFLITO DE BLOQUEIO NO ORACLE
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.
TRANSAÇÃO 1
TEMPO
TRANSAÇÃO 2
UPDATE
PRODUTO
SET
valor
=
30
WHERE id_produto = 1;
08:00
UPDATE
SET
valor
WHERE id_produto = 2;
1 row updated.
UPDATE produto SET valor = 52
WHERE id_produto = 2;
=
PRODUTO
50
1 row updated.
08:15
Transação continua normal, utilizando
selects, inserts, updates e até mesmo
deletes na mesma ou em outras
tabelas.
Sessão ainda aguardando
08:20
Transação continua normal, utilizando
selects, inserts, updates e até mesmo
deletes na mesma ou em outras
tabelas.
1 row updated.
08:25
commit;
Nenhuma
linha
atualizada
no
momento, pois a transação 1 entra
em lock conflict com a transação 2.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Liberado o lock, a sessão continua
normalmente.
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
2
3
identified
4
quota
5 account unlock;
user
on
usr1
default
oracle
users
on
usr2
default
oracle
users
profile
by
unlimited
Usuário criado.
SQL>
create
2
3
identified
4
quota
5 account unlock;
user
profile
by
unlimited
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.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
SQL>
2
3
4
5
6/
create
table
id_produto
number
produto
(
key,
number(10,2)
)
primary
valor
Tabela criada.
SQL>
insert
into
Informe
o
Informe o valor para valor: 50
produto
valor
values
para
valor
para
(&id,
id:
&valor);
1
id:
/
2
1 linha criada.
SQL>
Informe
o
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
2 where id_produto = 1;
produto
set
valor
=
35
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>
2
_
update
where
usr1.produto
set
id_produto
valor
=
=
36
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
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
2
3
4
5/
where
select
sid
blocking_session
in
(
v$session
)
from
USERNAME
-----------------------------USR1
Verificando
usuários
que
estão
aguardando
SQL>
select
sid,
2
from
3 where blocking_session is not null;
SID
----136 USR2
commit
de
username,
USERNAME
------------
outra
sessão.
blocking_session
v$session
BLOCKING_SESSION
----------------
141
Usuários que estão com o lock, e usuário que estão aguardando a outra transação.
SQL>
select
sid,
username,
2
from
v$session
where
blocking_session
3
UNION
4
select
A.sid,
A.username,
5
from
v$session
A,
6 where A.sid = B.blocking_session;
SID
---136
141 USR1
USERNAME
----------
blocking_session
not
null
ALL
A.blocking_session
v$session
B
is
BLOCKING_SESSION
---------------141
USR2
Resolvendo lock conflicts.
Somente é possível resolver conflitos de bloqueios através de duas maneiras:
1)
Emitir
um
commit
na
2) Terminar a sessão que está bloqueando.
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
2 from v$session where username = 'USR1';
sid,
serial#
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
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
MAIS VIEWS
GV$ENQUEUE_LOCK
GV$LOCK_ELEMENT
GV$LOCKS_WITH_COLLISIONS
GV$LOCKED_OBJECT
GV$LOCK
GV$EVENT_NAME
GV$LOCK_ACTIVITY
Lock de linha
Um lock do tipo linha é aplicado individualmente às linhas de uma tabela. Quando uma
linha está ““locada””, nenhuma outra sessão conseguirá alterá-la até que a transação
que detém o lock chegue ao fim. Claro que se uma determinada linha de uma tabela
estiver ““locada”” por uma determinada transação, outros processos poderão sem
problema algum alterar as outras linhas dessa tabela que ainda não estejam
““locada”s”.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Lock de tabela
Esse tipo de lock é aplicado no nivel tabela e pode ser utilizado para obter uma
imagem consistente da tabela e para assegurar que sua definição não mude enquanto
transações ativas ainda existirem nela.
Tipos de Locks
No Oracle, os seguintes tipos de locks são possíveis.
Tipo
Row Share (RS)
Row Excluive (RX)
Share (S)
Share Row Exclusive (SSX)
Share Row Exclusive (SRX)
Exclusive (X)
Level
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela/Linha
Para entender de forma integral e para usufruir e resolver problemas utilizando a view
V$LOCK é importante entender cada um desses tipos de locks. Vejamos a seguir uma
explicação de cada um deles.
Row Share (RS)
Também é conhecido com subshare lock (SS), esse lock indica que a transação possui
linhas ““locada”s” exclusivamente, mas ainda não as alterou. É obtido através da
instrução da Listagem 1.
SELECT .....FROM table...FOR UPDATE...
LOCK TABLE table IN ROW SHARE MODE;
Listagem 1. Lock do tipo Row Share.
Um lock RS pode ser utilizado para garantir que a tabela não será “locada”
exclusivamente antes de sua transação alterá-la.
Row Exclusive (RX)
Também conhecido como subexclusive lock (SX), esse lock indica que a transação
possui linhas “locadas” exclusivamente e já as alterou. É obtido através das instruções
da Listagem 2.
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
INSERT INTO tabela...;
UPDATE tabela...;
DELETE FROM tabela...;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
Listagem 2. Lock do tipo Row Exclusive.
Share (S)
Esse tipo de lock é obtido através da instrução da Listagem 3.
LOCK TABLE table IN SHARE MODE;
Listagem 3. Lock do tipo Share.
Geralmente o lock S é utilizado para garantir uma imagem consistente da tabela ao
longo da transação, isto é, ele garante que nenhum comando DML será permitido. É
necessário utilizar essa abordagem, pois o isolation Level default do Oracle é Read
Commit, isto é, as alterações efetuadas por outras transações são imediatamente
visíveis para todos os usuários logo após serem “comitadas”. Sendo assim, sua
transação pode ter várias versões da mesma tabela durante sua duração, o que às
vezes não é interessante. Para evitar esse tipo de problema, use um lock S ou inicie a
transação com o isolation level definido para Serializable, ou seja, a transação verá
somente dados “commitados” até o seu inicio.
Atenção:
Quando a tabela está “locada” no modo S, somente a transação que possui o lock pode
executar DMLs. Por se tratar de um lock do tipo share, várias transações podem obtêlo concorrentemente. Caso isso aconteça, nenhuma delas conseguirá efetuar
alterações, pois uma estará bloqueando a outra.
Share Row Exclusive (SSX)
Esse tipo de lock é um pouco mais exclusivo que o S, pois somente uma transação
pode obtê-lo por vez. Ele permite a outras transações obterem locks em linhas
especificas, mas não permite a alteração delas. Esse tipo de lock é obtido através da
instrução da Listagem 4.
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Listagem 4. Lock do tipo Share Row Exclusive.
Exclusive (X)
Esse é o mais restritivo lock existente, ele permite apenas que as outras sessões
acessem a tabela através de instruções SELECTs, ou seja, nenhum tipo de lock é
permitido. Esse lock também é o único lock que pode ser aplicado a linhas. Quando
uma linha está “locada”, ela está “locada” exclusivamente e nenhuma transação
conseguirá alterá-la até que a transação que mantém o lock chegue ao fim através de
um rollback ou commit.
Um deadlock é uma situação que ocorre quando dois processos aguardam para utilizar
um recurso que está sendo utilizado pelo outro processo, ou quando mais de dois
processos estão aguardando por recursos de forma circular. Esta situação pode ocorrer
com qualquer tipo de processos que compartilhem recursos mutuamente exclusivos,
mas é relativamente comum em bancos de dados. O objetivo deste post é clarificar
esta situação e apresentar alguns exemplos.
Durante as modificações de dados, os bancos de dados utilizam bloqueios (locks) para
garantir que outros processos não modifiquem/consultem os mesmos dados
simultaneamente. Quando um processo precisa acessar algum dado que está
bloqueado por outro processo, o primeiro deve aguardar até que o segundo conclua
suas operações. Este bloqueio é normal e necessário, e não caracteriza um deadlock.
Quando o segundo processo concluir suas operações os bloqueios serão liberados e o
primeiro processo continuará executando normalmente. Um deadlock ocorre somente
quando nenhum dos dois (ou mais) processos envolvidos pode continuar sua execução
sem intervenção externa.
Deadlocks também podem ocorrer nos bancos de dados com recursos como memória e
threads, mas o mais comum é envolvendo locks. Normalmente os sistemas
gerenciadores de bancos de dados possuem mecanismos para detectar e resolver
deadlocks.
Abaixo um exemplo simples utilizando o SQL Server. O primeiro processo (tela com
fundo preto) realiza uma alteração no nome do cliente 1, que ocorre normalmente:
Um outro processo (tela com fundo azul) realiza uma alteração no nome do cliente 2,
com sucesso. Em seguida tenta alterar o nome do cliente 1:
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Neste momento o segundo processo fica bloqueado, pois o cliente 1 está sendo
alterado pelo primeiro processo. Aqui não há deadlock, pois o processo 1 pode concluir
suas alterações (COMMIT/ROLLBACK), liberando assim o processo 2. No entanto, se
neste momento o processo 1 tentar alterar o cliente 2, haverá um deadlock:
O processo 1 estava bloqueando o cliente 1 e esperando para alterar o cliente 2. O
processo 2 estava bloqueando o cliente 2 e esperando para alterar o cliente 1. Ambos
os processos estavam aguardando por um recurso que estava bloqueado pelo outro, e
nenhum dos dois poderia continuar. O software gerenciador de banco de dados
detectou o deadlock e abortou (ROLLBACK) uma das transações (erro 1205 na tela
acima), fazendo com que a outra transação pudesse continuar.
O mecanismo de detecção de deadlocks e as mensagens de erro variam de acordo com
o banco de dados. No Oracle, o seguinte erro é gerado quando um deadlock é
detectado:
ORA-00060: deadlock detected while waiting for resource
Nem todos os gerenciadores de bancos de dados implementam um mecanismo de
detecção de deadlocks. O Progress OpenEdge, por exemplo, não implementa. Em um
banco de dados OpenEdge, quando um usuário fica bloqueado ele recebe uma
indicação visual, informando que o registro está bloqueado por outro usuário:
Pedro F. Carvalho
Analista de Sistemas
[email protected]
www.pedrofcarvlho.com.br
Como não há controle de deadlock, é possível que dois ou mais usuários recebam esta
mesma mensagem, e fiquem um esperando pelo outro.
Existem técnicas para minimizar a ocorrência de deadlocks em bancos de dados.
Normalmente a própria documentação dos bancos de dados contém informações mais
detalhadas sobre o assunto. Consulte sua documentação para mais detalhes