Olá Chiappa, Como sempre, bem detalhada sua explanação, mas queria aproveitar e tirar uma duvida. Em um ambiente nosso aqui, eu vejo constantemente várias sessões fazendo update e esperando por TM, até onde eu sempre ouvi, esse tipo de contenção acontece por falta de índice em colunas com FK, o problema é que todas colunas estão indexadas, menos algumas que faze referencia a tabelas cadastrais que nunca sofrem alteração e no momento dos locks eu vejo que nenhuma telas tem nenhum tipo de lock. Você consegue me ajudar a entender, e como rastrear, o motivos desses TMs?
Obrigado To: [email protected] From: [email protected] Date: Thu, 29 Aug 2013 21:43:24 +0000 Subject: [oracle_br] Re: Row-X (SX) - Problema? Não, vc não entendeu : veja lá na msg , o LOCK que é utilizado é do tipo TM, que serve para impedir *** DDLs *** nas Colunas, pois como eu disse Pode Ser que esses DDLs alterassem a estrutura, mexendo em colunas-chave dos relacionamentos... DMLs, como eu disse na msg, vc Sempre pode fazer, a qualquer momento, para tabelas protegidas por locks do tipo TM, DESDE QUE o(s) registro(s)-alvo do DML não estejam com alterações impedidas via lock de transação .... No meu texto original (que , inclusive, vejo que tinha um trechinho com erro de digitação) deveria-se ser (ênfase com *s adicionada aqui) : "TIPICAMENTE, com INSERTs feitos em tabelas sem constraints (já que aí nada impediria vc de inserir o mesmo valor) vc só vai encontrar o comum lock TM (a nível de tabela), : é ele que impede outra sessão de alterar a estrutura da tabela (o que daria conflito lógico, já que os registros estão sendo inseridos/deletados/modificados numa dada estruturajá existente , assim enquanto esse DML não for resolvido, quaisquer *** alterações via DDL *** que POTENCIALMENTE poderia mudar a estrutura do registro vão ser impedidas).... " e em outro trecho : "...muito certamente os locks que vc está vendo nas tabelas que não estão sendo alteradas mas são relacionadas com a tabela em alteração são locks TM, que *** protegem contra DDLs *** mas *** Não Impedem DMLs ***" O exemplo : => primeiro, cria a massinha de dados : SCOTT#1@O11GR2:SQL>create table teste_pai(pk_number number(3) primary key, campo_desc 2 varchar2(25) ); Tabela criada. SCOTT#1@O11GR2:SQL> SCOTT#1@O11GR2:SQL> SCOTT#1@O11GR2:SQL>create table teste_filho(pk_number number(3) primary key, fk_pai 2 number(3), campo_desc varchar2(25), FOREIGN KEY (fk_pai) REFERENCES 3 teste_pai(pk_number)); Tabela criada. SCOTT#1@O11GR2:SQL> SCOTT#1@O11GR2:SQL>insert into teste_pai(pk_number, campo_desc) values (1, 'Valor qualquer'); 1 linha criada. SCOTT#1@O11GR2:SQL>commit; Commit concluído. SCOTT#1@O11GR2:SQL> ==> agora abro transação na sessão 1 : SCOTT#1@O11GR2:SQL>insert into teste_filho (pk_number, fk_pai,campo_desc) values (1,1,'Teste Qualquer'); 1 linha criada. ==> vamos consultar os locks que tenho : SYSTEM@o11gr2:SQL>select sid from v$session where username='SCOTT'; SID ---- 35 39 SYSTEM@o11gr2:SQL>select sid, type, id1, id2, lmode, request, block from v$lock where sid in (35, 39); SID TY ID1 ID2 LMODE REQUEST BLOCK ---- -- ------------------ ------------------ ------------------ ------------------ ------------------ 39 AE 100 0 4 0 0 35 AE 100 0 4 0 0 39 TM 82625 0 3 0 0 39 TM 82627 0 3 0 0 39 TX 65565 4002 6 0 0 SYSTEM@o11gr2:SQL> ==> veja que eu TENHO um lock TX, protegendo a linha que inseri (pois há Chave, não posso duplicar o mesmo valor) , mas há Também o já esperado TM para evitar alterações.... Como eu disse , o TM protege contra DDLs e o TX contra concorrência/duplicidade na mesma linha, vamos ver : SCOTT#2@O11GR2:SQL>insert into teste_filho (pk_number, fk_pai,campo_desc) values (2,1,'outro filho'); 1 linha criada. => blz, DML em outra linha sem probs... Agora vamos tentar DMl na mesma linha protegida pelo TX : SCOTT#2@O11GR2:SQL>insert into teste_filho (pk_number, fk_pai,campo_desc) values (1,1,'duplicado!!'); ===>> Imediatamente a sessão fica BLOQUEADA, esperando o lock TX ser liberado, yep ??? Vc não está vendo a minha tela, mas repita o teste e veja aí... Consultando os locks : SYSTEM@o11gr2:SQL>select sid, type, id1, id2, lmode, request, block from v$lock where sid in (35, 39); SID TY ID1 ID2 LMODE REQUEST BLOCK ---- -- ------------------ ------------------ ------------------ ------------------ ------------------ 39 AE 100 0 4 0 0 35 AE 100 0 4 0 0 35 TX 65565 4002 0 4 0 35 TM 82625 0 3 0 0 35 TM 82627 0 3 0 0 39 TM 82625 0 3 0 0 39 TM 82627 0 3 0 0 35 TX 262171 3968 6 0 0 39 TX 65565 4002 6 0 1 9 linhas selecionadas. ===>> OPA, tai : a sessão 2 (SID=39) está sendo Bloqueada... Sim ??? Vamos commitar a sessão 1, para que ela libere o lock TX, e assim a sessão 2 possa continuar : SCOTT#1@O11GR2:SQL>commit; Commit concluído. ==> Imediatamente após a transação que estava segurando o lock em questão ser fechada (com COMMIT no meu caso) , veja o que aconteceu na sessão 2 : insert into teste_filho (pk_number, fk_pai,campo_desc) values (1,1,'duplicado!!') * ERRO na linha 1: ORA-00001: restrição exclusiva (SCOTT.SYS_C0012320) violada SCOTT#2@O11GR2:SQL> ==> ou seja, ela PÔDE continuar, e receber a violação da constraint.... Blz ??? ==> okdoc, demonstramos que DMLs (que Não Seja nos registros protegidos por TX) são possíveis, normal... Agora, vamos ver que os **** DDLs **** não são permitidos pelo lock TM : SCOTT#1@O11GR2:SQL>insert into teste_filho (pk_number, fk_pai,campo_desc) values (3,1,'Teste#2'); 1 linha criada. => no que tento um DDL em outra sessão (AINDA que não seja na tabela sendo alterada!!), veja : SCOTT#2@O11GR2:SQL>alter table teste_pai add blabla number; ==> imediatamente a sessão que tentou o DDL fica BLOQUEADA, yep ?? Mesmo sendo a teste_filho a tabela sendo alterada, e isso por causa do RELACIONAMENTO da teste_pai com ela, e dela com a teste_pai.... É ** ISSO ** que eu quis dizer no neu texto, sim ??? E veja que o meu DDL ** não está ** mexendo com as colunas-chave, mas AINDA ASSIM ele é bloqueado, já que, Como EU Disse, não há um lock, um controle de aceso a nível de coluna..... Consultando a v$lock : SYSTEM@o11gr2:SQL>select sid, type, id1, id2, lmode, request, block from v$lock where sid in (35, 39) order by 1,2; SID TY ID1 ID2 LMODE REQUEST BLOCK ---- -- ------------------ ------------------ ------------------ ------------------ ------------------ 35 AE 100 0 4 0 0 35 OD 82625 0 6 0 0 35 TM 82625 0 3 0 0 35 TX 131095 4180 6 0 0 35 TX 589843 4139 0 4 0 39 AE 100 0 4 0 0 39 TM 82625 0 3 0 0 39 TM 82627 0 3 0 0 39 TX 589843 4139 6 0 1 9 linhas selecionadas. SYSTEM@o11gr2:SQL>select owner, object_id, object_name from dba_objects where object_id in (589843, 82627, 131095); OWNER OBJECT_ID OBJECT_NAME ---------------- ------------------ ------------------------------ SCOTT 82627 TESTE_FILHO SYSTEM@o11gr2:SQL> ==> Vc viu que interessante ??? O meu DDL foi na TESTE_PAI, mas como a TESTE_FILHO tá com um lock TM **** E **** consultando no dicionário de dados o RDBMS Oracle "descobriu" que a TESTE_PAI tem relacionamento com a TESTE_FILHO, o DDL na TESTE_PAI foi bloqueado... Muito legal, e muito bem bolado - essa parte do controle de acessos/concorrência e da consistência de leitura sempre foram uma das partes do RDBMS Oracle que eu mais admiro, muito bem pensada e implementada.... []s Chiappa --- Em [email protected], Alessandro Lúcio Cordeiro da Silva <alecordeirosilva@...> escreveu > > Olá Chiappa, > > Tem um trecho que você diz : "Como NÃO EXISTEM locks a nível de COLUNA" , mas > já vi caso que o Oracle dá Lock se a coluna estiver sendo alterada. Para ser > mais especifico se estivermos inserindo um registro FILHO o CAMPO da de > Referencia da Tabela pai de "Lockada" > > Segue o campo para reprodução: > > ---------------------------------------- SESSÃO 1 > ---------------------------------------- > > Conectado. > > SQL> create table teste_pai(pk_number number(3) primary key, campo_desc > varchar2(25) ); > Tabela criada. > > > SQL> create table teste_filho(pk_number number(3) primary key, fk_pai > number(3), campo_desc varchar2(25), FOREIGN KEY (fk_pai) REFERENCES > teste_pai(pk_number)); > Tabela criada. > > > SQL> insert into teste_pai(pk_number, campo_desc) values (1, 'Valor > qualquer'); > 1 linha criada. > > > SQL> commit; > Commit concluÝdo. > > > > ---------------------------------------- SESSÃO 2 > ---------------------------------------- > > C:\>sqlplus /nolog > Conectado. > > SQL> insert into teste_filho (pk_number, fk_pai,campo_desc) values > (1,1,'Teste Qualquer'); > 1 linha criada. > > > ---------------------------------------- SESSÃO 1 > ---------------------------------------- > > SQL> update teste_pai set campo_desc = 'OUTRO VALOR' where pk_number = 1; > 1 linha atualizada. > > > SQL> update teste_pai set pk_number = 2 where pk_number = 1; > > > ---------------------------------------------------------- > > Veja que posso alterar qualquer campo do Registro PAI enquanto o Registro > Filho Criado esta em Transação, ..:: MAS ::.. não o campo CHAVE do Pai. > > > > Alessandro Lúcio Cordeiro da Silva > Analista de Sistema > þ http://alecordeirosilva.blogspot.com/ > O tic-tac do relógio me lembra de algo muito importante que esta acontecendo: > estamos vivos. > "Joana de Souza Schmitz Croxato" > > > > ________________________________ > De: J. Laurindo Chiappa <jlchiappa@...> > Para: [email protected] > Enviadas: Quinta-feira, 29 de Agosto de 2013 14:58 > Assunto: [oracle_br] Re: Row-X (SX) - Problema? > > > > > Tudo jóia ? Então, na verdade vc está vendo aí o comportamento absolutamente > NORMAL do RDBMS Oracle - os Conceitos para vc poder entender o que está > ocorrendo estão melhor detalhados nos manuais Oracle (**principalmente ** o > "Oracle® Database Concepts 11g Release 2" no cap. 9 - Data Concurrency and > Consistency), nas notas metalink relacionadas (como a clássica "Master Note: > Locks, Enqueues and Deadlocks (ORA-00060)" (Doc ID 1392319.1) , e em bons > livros que detalham a arquitetura do RDBMS (como por exemplo os do Tom Kyte), > mas a versão resumida da historinha é (sempre falando de locks relacionados a > DML, os causados por DDLs são outra coisa) : > > - conceito 1 : no RDBMS Oracle, no instante mesmo em que o DML é processado > de cara já é feito um lock em ** TODOS ** os registros envolvidos, não se > 'espera' haver um acesso , nem nada, e é Autiomático (o programador/usuário > não tem que fazer/programar NADA para isso) e inescapável : no caso, isso não > traz problemas de acesso multi-usuário porque no RDBMS Oracle o SELECT, a > leitura, ** NUNCA ** é afetada/bloqueada/impedida por lock de nenhum tipo, e > vice-versa : LOCKERS nunca intererferem em/são interferidos por readers E > readers nunca interferem em/são interferidos por LOCKERS, e isso não implica > em leitura "suja" (ie, uma outra sessão ler dados alterados mas não > comitados) porque os dados antes da alteração são "copiados" para uma > estrutura chamada UNDO, e são esses dados que são lidos/retornados. > > - conceito 2 : existem 2 tipos básicos de locks automáticos relacionados a > DML : locks row-level, que protegem/evitam alterações em uma só linha da > tabela (normalmente chamados de TX Locks, ou transaction locks), e locks a > nível de tabela (TM Locks, ou locks table management) , que protegem a tabela > TODA, evitando DDLs na tabela lockada, okdoc ? > ==> Esse conceito é Crítico : como ambos os locks se relacionam a linhas, vc > VAI ver a palavra "ROW" nas descrições de ambos, mas Não Confunda-os - ambos > são relacionados a linhas (daí o ROW na descrição) mas o escopo entre eles é > Totalmente diferente, um locka uma só linha, outro locka TODAs as linhas (na > prática "fechando"/protegendo a tabela toda), mas apenas para DDLs... DMLs > (que se referem á linhas específicas de dados) não são que se refiram a > linhas > Aqui está, penso eu, a fonte de sua dúvida : a V$LOCKED_OBJECT mostra o > locked_mode , a 'funcionalidade' dos locks , que para DMLs normalmente vai > ser 3, que é relacionado com linhas (rows), mas ** NÃO MOSTRA ** o TIPO do > lock, se é TX ou TM, então só pela V$LOCKED_OBJECT vc não tem como saber o > TIPO dos locks presentes, se ele está protegendo uma linha só ou não, se > protege o conunto de linhas (a tabela toda).... Isso vc só vai achar na > V$LOCK, coluna TYPE... Sim ??? > É um pouco de nomenclatura deslocada, concordo, mas é o que é..... > > TIPICAMENTE, com INSERTs feitos em tabelas sem constraints (já que aí nada > impediria vc de inserir o mesmo valor) vc só vai encontrar o comum lock TM (a > nível de tabela), : é ele que impede outra sessão de alterar a estrutura DML > na tabela (o que daria conflito lógico, já que os registros estão sendo > inseridos/deletados/modificados numa dada estruturajá existente , assim > enquanto esse DML não for resolvido, quaisquer alterações via DDL que > POTENCIALMENTE poderia mudar a estrutura do registro vão ser impedidas).... > > ==> IMPORTANTE : Como NÃO EXISTEM locks a nível de COLUNA, aí então mesmo nas > situações em que o objetivo é evitar a alteração de uma ou mais colunas > apenas, o RDBMS vai empregar LOCKs row-level e table-level, cfrme necessário > > - conceito 3 : quando há Relacionamentos/Constraints/Índices,etc, esses caras > são baseados em COLUNAS-CHAVE (existentes TANTO nas tabelas-pai quanto nas > tabelas-filha), então (já que não é possível se ter um lock apenas para as > colunas-chave a preservar de DDLs, como dito acima) Logicamente todas as > tabelas envolvidas vão obter um lock do tipo TM, para EVITAR que algum DDL > altera colunas-chaves, altere relacionamentos, yep ???? > Aí vc pode dizer "ah, não seria melhor, ao invés de via lock de tabela o > RDBMS proibir qualquer DDL, ele ao invés ANALISAR cada DDL enviado, > pesquisando o dicionário de dados, para ver se o DDL vai ou não alterar > colunas-chaves ???? Poder podia, mas A dificuldade de se implementar isso > seria a LÓGICA COMPLEXA envolvida, que poderia levar a falsos positivos E a > problemas de performance na fase de interpretação dos comandos que o RDBMS > recebe, então aí optou-se por esse comportamento de simplesmente barrar > DDLs.... > > Tendo em vista esses conceitos aí acima, imho então o que vc está vendo é > algo TOTALMENTE NORMAL : muito certamente os locks que vc está vendo nas > tabelas que não estão sendo alteradas mas são relacionadas com a tabela em > alteração são locks TM, que protegem contra DDLs mas Não Impedem DMLs, DMLs > concorrentes (entre sessões diferentes) só são impedidos nas linhas > protegidas por locks do tipo TX, em princípio.... > Há muitos OUTROS detalhes aí a serem passíveis de entendimento, mas com isto > ACHO que já explica o que vc está vendo : Consulte a V$LOCK e vc vai ver o > que vc vai ver.... > > > []s > > Chiappa > > --- Em [email protected], Luciana Camargo <lcamargo@> escreveu > > > > Pessoal > > boa noite! > > > > Preciso uma ajuda de vocês para entender se é uma situação normal ou > > posso ter algum problema. > > > > Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit > > > > > > > Eu fiz um teste simulando o que temos no banco aqui > > > > > > CREATE TABLE TAB_TYPE_1 > > > (ID NUMBER(10) PRIMARY KEY, > > > CODE VARCHAR2(10) NOT NULL); > > > > > > CREATE TABLE TAB_TYPE_2 > > > (ID NUMBER(10) PRIMARY KEY, > > > CODE VARCHAR2(10) NOT NULL); > > > > > > -- Tabela principal que depende das 2 tabelas acima de configuração: > > > CREATE TABLE TAB_A > > > (ID NUMBER(10) PRIMARY KEY, > > > CODE VARCHAR2(10) NOT NULL, > > > TYPE_1_ID NUMBER(10), > > > TYPE_2_ID NUMBER(10)); > > > > > > ALTER TABLE tab_a ADD CONSTRAINT fk_tab_a_type_1 FOREIGN KEY (type_1_id) > > > REFERENCES tab_type_1 (id); > > > > > > ALTER TABLE tab_a ADD CONSTRAINT fk_tab_a_type_2 FOREIGN KEY (type_2_id) > > > REFERENCES tab_type_2 (id); > > > > > > CREATE INDEX idx_tab_a_type_1 ON tab_a (type_1_id) TABLESPACE indx; > > > CREATE INDEX idx_tab_a_type_2 ON tab_a (type_2_id) TABLESPACE indx; > > > > > > -- Quando faço a inserção na tabela principal, mesmo SEM valor nos > > > atributos de tipos verifico que existe a seguinte indicação em * > > > v$locked_object*: > > > > > > INSERT INTO tab_a (id, code) VALUES (1,1); > > > > > > OBJECT_NAME LOCKED_MODE > > > ------------------ --------------- > > > TAB_A Row-X (SX) > > > TAB_TYPE_1 Row-X (SX) > > > TAB_TYPE_2 Row-X (SX) > > > > > > Eu entendo que os valores de v$locked_object.locked_mode são: > > > 0, 'None', > > > 1, 'Null (NULL)', > > > 2, 'Row-S (SS)', > > > 3, 'Row-X (SX)', > > > 4, 'Share (S)', > > > 5, 'S/Row-X (SSX)', > > > 6, 'Exclusive (X)', > > > > > > SID Lock Type Mode Held Blocking? > > > ----- --------------- --------------- --------------- > > > 579 DML Row-X (SX) Not Blocking > > > 579 DML Row-X (SX) Not Blocking > > > 579 DML Row-X (SX) Not Blocking > > > 579 Transaction Exclusive Not Blocking > > > 579 AE Share Not Blocking > > > > > > Apesar de aparecer conforme acima, NÃO está impedindo nenhuma > > > alteração/remoção em relação a dados para as tabelas de tipos, apenas > > > segurando alterações de estrutura. > > > > > > Isso impacta em algo em relação a contenção ou é um comportamento normal? > > > Por que o indicativo "Row" se não há nenhuma contenção de linha? > > > > > > > > > > Obrigada > > > > > Luciana > > > > > > > > > > > > > > [As partes desta mensagem que não continham texto foram removidas] > > > > > > > [As partes desta mensagem que não continham texto foram removidas] > [As partes desta mensagem que não continham texto foram removidas] ------------------------------------ -------------------------------------------------------------------------------------------------------------------------- >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/[email protected]/ -------------------------------------------------------------------------------------------------------------------------- >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 <*> Para visitar o site do seu grupo na web, acesse: http://br.groups.yahoo.com/group/oracle_br/ <*> Para sair deste grupo, envie um e-mail para: [email protected] <*> O uso que você faz do Yahoo! Grupos está sujeito aos: http://br.yahoo.com/info/utos.html
