Só para mostrar que *** Não É Verdade *** que vc não possa identificar as
sessões que estão Esperando para obter um lock, veja o Exemplo abaixo (em 11gr2
EE, mas em princípio Independente de versão) : terei 3 janelas separadas (e
portanto 3 sessões) que vão rodar a mesma rotina que não trata corretamente os
locks, ficando em loop eterno E não limitando transações nem sessões, vamos
botar a porimeira para executar :
scott#1:SQL>DECLARE
2 resource_busy exception;
3 pragma exception_init (resource_busy,-54);
4 v_num number;
5 BEGIN
6 loop
7 Begin
8 select 1 into v_num from dept where deptno=10 FOR UPDATE NOWAIT;
9 update dept set dname='TESTE' where deptno=10;
10 exit;
11 Exception
12 when resource_busy then
13 null;
14 End;
15 end loop;
16 END;
17 /
Procedimento PL/SQL concluÝdo com sucesso.
==> ok, essa seria a sessão A no seu cenário, como ela foi casualmente a
primeira ela obtém o lock... Vamos botar pra rodar a mesma rotina com loop
eterno em outras múltiplas janelas :
SCOTT#2:SQL>DECLARE
2 resource_busy exception;
3 pragma exception_init (resource_busy,-54);
4 v_num number;
5 BEGIN
6 loop
7 Begin
8 select 1 into v_num from dept where deptno=10 FOR UPDATE NOWAIT;
9 update dept set dname='TESTE' where deptno=10;
10 exit;
11 Exception
12 when resource_busy then
13 null;
14 End;
15 end loop;
16 END;
17 /
=> Imediatamente a sessão 'congela' dado o loop eterno, vamos abrir ainda
outra :
scott#3:SQL>DECLARE
2 resource_busy exception;
3 pragma exception_init (resource_busy,-54);
4 v_num number;
5 BEGIN
6 loop
7 Begin
8 select 1 into v_num from dept where deptno=10 FOR UPDATE NOWAIT;
9 update dept set dname='TESTE' where deptno=10;
10 exit;
11 Exception
12 when resource_busy then
13 null;
14 End;
15 end loop;
16 END;
17 /
okdoc, as janelas 2 e 3 estão em "freeze" aqui, vamos consultar o status geral
consultando as views adequadas em uma sessão privilegiada (no caso consultando
só o usuário SCOTT só por Brevidade de exemplo, claro que em Prod seriam Outras
as consições de filtro quye vc colocaria na V$SESSION) :
SYSTEM@o11gr2:SQL>select sid, serial#, username, logon_time, event,
blocking_instance, blocking_session, wait_class, p1, p2, p3, seconds_in_wait
from v$session where username='SCOTT';
SID SERIAL# USERNAME LOGON_TI EVENT
BLOCKING_INSTA
NCE BLOCKING_SESSION WAIT_CLASS
P1 P2 P3 SECONDS_IN_WAIT
---------- ---------- ------------------------------ --------
---------------------------------------------------------------- ------
----------- ----------------
---------------------------------------------------------------- ----------
---------- ---------- ------
---------
14 12 SCOTT 18/03/13 SQL*Net message
from client
Idle
1111838976 1 0 416
20 17 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
141 12 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
.... espero um pouco ...
SYSTEM@o11gr2:SQL>/
SID SERIAL# USERNAME LOGON_TI EVENT
BLOCKING_INSTA
NCE BLOCKING_SESSION WAIT_CLASS
P1 P2 P3 SECONDS_IN_WAIT
---------- ---------- ------------------------------ --------
---------------------------------------------------------------- ------
----------- ----------------
---------------------------------------------------------------- ----------
---------- ---------- ------
---------
14 12 SCOTT 18/03/13 SQL*Net message
from client
Idle
1111838976 1 0 428
20 17 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
141 12 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
... espero mais um pouco ...
SYSTEM@o11gr2:SQL>/
SID SERIAL# USERNAME LOGON_TI EVENT
BLOCKING_INSTA
NCE BLOCKING_SESSION WAIT_CLASS
P1 P2 P3 SECONDS_IN_WAIT
---------- ---------- ------------------------------ --------
---------------------------------------------------------------- ------
----------- ----------------
---------------------------------------------------------------- ----------
---------- ---------- ------
---------
14 12 SCOTT 18/03/13 SQL*Net message
from client
Idle
1111838976 1 0 433
20 17 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
141 12 SCOTT 18/03/13 buffer busy waits
Concurrency
4 135 1 0
SYSTEM@o11gr2:SQL>/
==> tá CLARO que está havendo Concorrência enter as sessões 20 e 141 por alguma
outra, vamos consultar qual é o objeto que elas estão sendo Impedidas de
acessar :
SYSTEM@o11gr2:SQL>select
2 owner,
3 segment_name,
4 segment_type
5 from
6 dba_extents
7 where
8 file_id = &P1
9 and
10 &P2 between block_id and block_id + blocks -1;
Informe o valor para p1: 4
antigo 8: file_id = &P1
novo 8: file_id = 4
Informe o valor para p2: 135
antigo 10: &P2 between block_id and block_id + blocks -1
novo 10: 135 between block_id and block_id + blocks -1
OWNER SEGMENT_NAME
SEGMENT_TYPE
------------------------------
---------------------------------------------------------------------------------
------------------
SCOTT DEPT
TABLE
SYSTEM@o11gr2:SQL>
=> MARAVILHA... Vamos ver se há LOCK nesse objeto, se sim tá basicamente
PROVADO que é o caso que queríamos demonstrar, espera Eterna por locks causada
por loop eterno :
SYSTEM@o11gr2:SQL>ed
Gravou file afiedt.buf
1 SELECT session_id, oracle_username, os_user_name, username U_NAME, owner
OBJ_OWNER,
2 object_name, object_type, s.osuser,
3 DECODE(l.block,
4 0, 'Not Blocking',
5 1, 'Blocking',
6 2, 'Global') STATUS,
7 DECODE(v.locked_mode,
8 0, 'None',
9 1, 'Null',
10 2, 'Row-S (SS)',
11 3, 'Row-X (SX)',
12 4, 'Share',
13 5, 'S/Row-X (SSX)',
14 6, 'Exclusive', TO_CHAR(lmode)
15 ) MODE_HELD
16 FROM gv$locked_object v, dba_objects d,
17 gv$lock l, gv$session s
18 WHERE v.object_id = d.object_id
19 AND (v.object_id = l.id1)
20 AND v.session_id = s.sid
21* ORDER BY username, session_id
SYSTEM@o11gr2:SQL>/
SESSION_ID ORACLE_USERNAME OS_USER_NAME U_NAME
OBJ_OWNER OBJECT
_NAME
OBJECT_TYPE
---------- ------------------------------ ------------------------------
------------------------------ -----------------------------
-
--------------------------------------------------------------------------------------------------------------------------------
--
-----------------
OSUSER STATUS MODE_HELD
------------------------------ ------------
----------------------------------------
14 SCOTT Win7_Casa\jlchiappa SCOTT
SCOTT
DEPT
TABLE
Win7_Casa\jlchiappa Not Blocking Row-X (SX)
14 SCOTT Win7_Casa\jlchiappa SCOTT
SCOTT
DEPT
TABLE
Win7_Casa\jlchiappa Not Blocking Row-X (SX)
141 SCOTT Win7_Casa\jlchiappa SCOTT
SCOTT
DEPT
TABLE
Win7_Casa\jlchiappa Not Blocking None
141 SCOTT Win7_Casa\jlchiappa SCOTT
SCOTT
DEPT
TABLE
Win7_Casa\jlchiappa Not Blocking None
okdoc ?
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, "J. Laurindo Chiappa" <jlchiappa@...>
escreveu
>
> Hmmm, peraí : "nunca vai sair" é absolutamente Falso : o lock que A está
> mantendo (e que impede B de lockar o mesmo recurso) *** NÃO *** é Eterno, ele
> VAI SIM ser liberado assim que A encerrar a transação, seja com COMMIT seja
> com ROLLBACK, yes ??? Da mesma maneira, dizer que é "impossível identificar
> quem está lockando registros de quem" sorry, mas afaik é Inverdade também :
> via script é Plenamente Possível vc consultar quem está esperando pelo que,
> aí não é impossível não vc localizar quem está com o lock para si, os outros
> TODOS estão em espera por essa sessão, dado o cenário que vc descreve...
>
> O que é Questionável, podendo mesmo ser considerado uma FALHA no
> aplicativo é esse comportamento de :
>
> 1. deixar a transação que obteve o lock ficar ILIMITADAMENTE aberta, sem
> nenhum tipo de timeout, nem de aviso ao Operador se a transação for
> controlada pelo Usuário da aplicação e não for encerrada o mais logo possível
>
> e
>
> 2. dado o fato 1. acima (de poder existir Transação sem controle
> estrito de tempo), após a Aplicação corretamente usar o for update nowait
> para testar se um recurso está lockado, quando detectar que um
> registro/resource está lockado (recebendo o ORA-0054) a Aplicação ficar
> tentando e retentando o lock INDEFINIDAMENTE é falha - o correto ao invés
> seria LIMITAR a quantidade de vezes em que espera o lock ser liberado (via
> LOOP ** não-eterno **, repetido uma qtdade FIXA de vezes) E/OU limitar o
> período de tempo em que espera o lock já presente ser encerrado com o fim
> datransação que o criou...
>
> Agora entendi, é uma falha de Controle/Tratamento pela Aplicação no
> procedimento de LOCK NOWAIT, que em si está Corretíssimo e Não deveria causar
> espanto algum, ok...
>
> Muito bem, o que vc vai poder fazer aí a nível de banco de dados, Enquanto
> a Aplicação não tem essa falha corrigida é fazer no banco de dados o que a
> Aplicação não faz, ie, LIMITAR a duração de uma transação (e portanto limitar
> o tempo que um lock pode existir), OU então eliminar esses casos, o que
> poderia ser feito via :
>
> a) implementação algum tipo de timeout nas sessões (via PROFILE, talvez),
> para que uma sessão A que obteve um lock num dado recurso NÂO fique com
> transações abertas por tempo irrazoavelmente longo, assim limitando o tempo
> que B fica esperando pelo lock
>
> e/ou
>
> b) tendo um JOB no banco que a cada poucos x minutos consulta a V$SESSION
> e/ou a DBA_LOCKS e/ou views de WAIT, e se detectar espera muito longa por
> locks o JOB poderia mandar um e-mail para te avisar e/ou matar a sessão
> incial que implantou o lock (REGISTRANDO essa ação nalgum lugar, é Claro),
> coisa do tipo
>
> []s
>
> Chiappa
>
>
> --- Em oracle_br@yahoogrupos.com.br, JLSilva <jljlsilva@> escreveu
> >
> > Chiappa, o meu espanto é devido à lógica utilizada na aplicação.
> > Se o registro está lockado, o processo entra em um loop e tenta novamente
> > executar exatamente o mesmo select for update nowait para lockar o registro.
> > O efeito cascata disso é que, ao fazer isto, a sessão A não libera os
> > registros anteriores e continua tentando lockar novos registros que estão
> > lockados pela sessão B.
> > Enquanto isso, a sessão B que está lockando outros registros, e em um
> > determinado momento, a sessão B vai tentar lockar o registro que está
> > lockado pela sessão A.
> > Se não fosse utilizado o "nowait" nesse select, ocorreria um deadlock, pois
> > A está locando o registro 1, B está locando o registro 2, A tenta locar o
> > registro 2 e B tenta locar o registro 1.
> > Mas, devido à forma que a aplicação foi construída, o deadlock nunca
> > ocorre, e a aplicação nunca vai sair desse loop.
> > Agora, imagine isto ocorrendo com muitas sessões (mais de 10 sessões).
> > Acaba que tem várias sessões lockando registros, elas não liberam esses
> > registros, e ficam tentando locar novos registros que já estão lockados por
> > outras, e ficam nesse loop. Fica impossível identificar quem está lockando
> > registros de quem.
> > Infelizmente, o fornecedor é daquele tipo difícil, e diz que "o sistema
> > está funcionando em 200 outros locais". Ok, mas aqui está ocorrendo esse
> > problema...
> > Mas, concordo com você. Desativar o nowait teria um resultado muito
> > imprevisível para o funcionamento do rdbms como um todo.
> >
> > On Mar 18, 2013, at 5:30 PM, "J. Laurindo Chiappa" <jlchiappa@> wrote:
> >
> > > Bom, vamos começar respondendo à sua pergunta : Não, em princípio afaik
> > > (salvo alguma alteração PESADA, não-suportada e EXTREMAMENTE perigosa de
> > > interferir no banco como um todo, tipo via parâmetros internos, OU então
> > > jogando-se o parâmetro de compatibility lá embaixo pra alguma versão
> > > antiga, etc ) no caso não há como vc alterar o comportamento do comando
> > > "SELECT FOR UPDATE NOWAIT", não... Aliás, de modo geral, Não Há como vc
> > > fazer um dado comando que está documentado agir assim ou assado agir de
> > > outro jeito, não...
> > >
> > > Isso respondido, observo que vc está "espantado" com a utilização, e nem
> > > imagino porque : veja vc, é ABSOLUTAMENTE normal e documentado (vide
> > > manual "Oracle® Database Advanced Application Developer's Guide" cap. 1 -
> > > SQL Processing for Application Developers , e algumas entradas sobre
> > > LOCKs no Concepts) que é EXATAMENTE ASSIM que vc "pergunta" se um
> > > registro está lockado ou não : vc tenta fazer um lock com NOWAIT e se der
> > > ORA-0054 ele tá lockado, se não der não está... Eu ABSOLUTAMENTE NÃO
> > > ENTENDI a sua "admiração", os "!!!" que vc colocou na msg, sim ????
> > > Também Não Entendi porque vc quer mudar isso - é Absolutamente Normal que
> > > um recurso (tabela/registro/whatever) possa ser acessado em modo
> > > exclusivo, corretamente o teu Aplicativo CHECA isso, e vc quer remover
> > > esse check ?? Não entendo porque - diga para nós EXATAMENTE o que vc quer
> > > que a gente tenta sugerir algo....
> > >
> > > []s
> > >
> > > Chiappa
> > >
> > >
> > > --- Em oracle_br@yahoogrupos.com.br, JLSilva <jljlsilva@> escreveu
> > >>
> > >> Senhores, boa tarde.
> > >> Temos uma aplicação de terceiros (CHB) que faz "select ... for update
> > >> ... NOWAIT".
> > >> Essa aplicação recebe o erro ORA-00054 e o tratamento que ela dá é:
> > >> tenta o select novamente!!!!!!!
> > >> Com isso, não ocorre o deadlock, nem consigo saber exatamente quem é o
> > >> bloqueador, pois há dezenas de sessões atualizando a mesma tabela.
> > >> Alguém aí sabe se é possível desativar o "nowait" através de algum
> > >> parâmetro/configuração do banco?
> > >> Obrigado!
> > >>
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > --------------------------------------------------------------------------------------------------------------------------
> > >> Atenção! As mensagens do grupo ORACLE_BR são de acesso público e de
> > >> inteira responsabilidade de seus remetentes.
> > > Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
> > > --------------------------------------------------------------------------------------------------------------------------
> > >> Apostilas » Dicas e Exemplos » Função » Mundo Oracle » Package »
> > >> Procedure » Scripts » Tutoriais - O GRUPO ORACLE_BR TEM SEU PROPRIO
> > >> ESPAÇO! VISITE: http://www.oraclebr.com.br/
> > > ------------------------------------------------------------------------------------------------------------------------
> > > Links do Yahoo! Grupos
> > >
> > >
> >
>
