Menu de navegação

[C-S-RParticipante]

Ola pessoal tudo bem?

Gostaria de saber pq no teste que estou efetuando, quando faço um insert na tabela pai o Oracle esta lockando a filha.

Meu teste é bem simples.

CREATE TABLE TABELA_A (PK_A NUMBER);
ALTER TABLE TABELA_A ADD CONSTRAINT PK_A PRIMARY KEY (PK_A);
CREATE TABLE TABELA_B (PK_B NUMBER, FK_A NUMBER);
ALTER TABLE TABELA_B ADD CONSTRAINT PK_B PRIMARY KEY (PK_B);
ALTER TABLE TABELA_B ADD CONSTRAINT FK_A FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);
CREATE INDEX IDX_B ON TABELA_B (FK_A);

INSERT INTO TABELA_A VALUES (1);

Esse insert lock as duas tabelas.

Estou efetuando esse teste, por um problema que estou enfrentando.
Eu preciso dropar um tabela filha, que a parent é altamente concorrente. Quando tento efetuar o drop da resourse busy ORA-00054.

vlw pela ajuda.

[José Laurindo ChiappaModerador]

Colega, o conceito em questão aqui é que o RDBMS *** não pode *** deixar que um objeto seja alterado via DDL (ie, CREATE, ALTER, DROP, etc) enquanto há Transações ativas : pensemos, o que o RDBMS deveria fazer se eles deixar que um DROP acontecesse numa tabela enquanto uma Transação está inserindo nela ?/ Os dados vão pra onde, pro vácuo ??? Afinal, a tabela foi destruída….
Mesmo coisas que vc pode pensar que são inócuas, como alterar ou modificar uma coluna ou uma constraint, o RDBMS *** não pode deixar *** que sejam feitas se tem transações usando a tabela em questão : afinal, e se vc alterar a coluna para menos espaço do que os INSERTs estão gravando na tabela, por exemplo ?? SACOU ??? Proteção e Integridade dos dados é Paradigma num RDBMS, e isso vale ainda mais no RDBMS Oracle….

Para implementar essa integridade, o RDBMS ** automaticamente ** coloca um Bloqueio nas linhas que está alterando (de forma a ninguém alterar a mesma linha ao mesmo tempo) , MAS TAMBÉM coloca um Bloqueio de um tipo especial na tabela, que Proíbe DDLs (para evitar que alguém faça ALTERs, CREATEs, DROPs, etc na tabela em uso) mas NÂO BLOQUEIA A TABELA PARA TODAS AS ATIVIDADES, esse lock que as tabelas sofrem automaticamente é um lock COMPARTILHADO…
Veja aqui :

==> crio as tabelas que vc indicou :

hr@XE:SQL>CREATE TABLE TABELA_A (PK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_A ADD CONSTRAINT PK_A PRIMARY KEY (PK_A);

Tabela alterada.

hr@XE:SQL>–
hr@XE:SQL>CREATE TABLE TABELA_B (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_B ADD CONSTRAINT PK_B PRIMARY KEY (PK_B);

Tabela alterada.

hr@XE:SQL>ALTER TABLE TABELA_B ADD CONSTRAINT FK_A FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE INDEX IDX_B ON TABELA_B (FK_A);

Índice criado.

==> veja que só com a criação, sem que haja Transações, obviamente nenhum LOCK é implementado :

system@XE:SQL>select * from dba_dml_locks;

não há linhas selecionadas

==> Agora vou disparar uma Transação, fazendo DMLs numa tabela (a tabela-pai no caso, mas não importa) :

hr@XE:SQL>INSERT INTO TABELA_A VALUES (1);

1 linha criada.

hr@XE:SQL>

==> veja os locks ::

system@XE:SQL>select * from dba_dml_locks;

SESSION_ID OWNER NAME

---

MODE_HELD MODE_REQUESTE LAST_CONVERT

---

BLOCKING_OTHERS

   139 HR                             TABELA_A

Row-X (SX) None 125
Not Blocking

   139 HR                             TABELA_B

Row-X (SX) None 125
Not Blocking

system@XE:SQL>

==> veja que o RDBMS lockou a tabela B também, mesmo ela não estar sofrendo DMLs : a questão é que a constraint de A que utiliza a tabela B ** força ** que B também seja protegida contra DDLs, vai que alguém tenta fazer um DDL que interfere com as constraints de A…. Repito, NUNCA DE MODO ALGUM ABSOLUTAMENTE o RDBMS Oracle vai permitir quebra de integridade de dados….

SOMENTE quando as transações forem fechadas (como COMMIT ou ROLLBACK) é que os locks são liberados, veja só :

hr@XE:SQL>rollback;

Rollback concluído.

hr@XE:SQL>

==> veja que Automagicamente os locks são removidos :

system@XE:SQL>select * from dba_dml_locks;

não há linhas selecionadas

system@XE:SQL>

==> veja a diferença de vc fazer um lock Exclusivo na tabela toda :

hr@XE:SQL>lock table tabela_B in exclusive mode;

Tabela(s) Bloqueada(s).

hr@XE:SQL>

==> o resultado :

system@XE:SQL>/

SESSION_ID OWNER NAME

---

MODE_HELD MODE_REQUESTE LAST_CONVERT

---

BLOCKING_OTHERS

   139 HR                             TABELA_B

Exclusive None 26
Not Blocking

system@XE:SQL>

==>> okdoc ??? Isso tudo que falei espero que não seja novidade pra vc, são conceitos Fundamentais do RDBMS Oracle…
É importante também frisar que o lock compartilhado não proíbe para sempre o DDL, ele só proíbe os DDLs enquanto a(s) transação/ões que criaram o lock estão Ativas/abertas : AUTOMAGICAMENTE o DDL vai ter sucesso no exato instante em que a(s) transação/ões terminar(em) :

==> removo os lock :

hr@XE:SQL>rollback;

Rollback concluído.

hr@XE:SQL>

==> confirmo que os locks não existem mais :

system@XE:SQL>/

não há linhas selecionadas

system@XE:SQL>

==> faço um DML na sessão 1 do schema HR, automaticamente abrindo Transação e causando locks nos registros E nas tabelas mas em modo compartilhado :

hr@XE:SQL>INSERT INTO TABELA_A VALUES (1);

1 linha criada.

hr@XE:SQL>

==> o resultado :

system@XE:SQL>/

SESSION_ID OWNER NAME

---

MODE_HELD MODE_REQUESTE LAST_CONVERT

---

BLOCKING_OTHERS

   139 HR                             TABELA_A

Row-X (SX) None 33
Not Blocking

   139 HR                             TABELA_B

Row-X (SX) None 33
Not Blocking

system@XE:SQL>

===>> OK, vou tentar fazer um DDL numa das tabelas lockadas / em uso numa outra sessão 2 :

HR#2@xe:SQL>alter table TABELA_B ADD x number;

==> vc não vê aqui, mas imediatamente a sessão #2 ficou congelada esperando os locks serem finalizados… OS locks ficaram :

system@XE:SQL>/

SESSION_ID OWNER NAME

---

MODE_HELD MODE_REQUESTE LAST_CONVERT

---

BLOCKING_OTHERS

    96 HR                             TABELA_B

Row-X (SX) None 44
Not Blocking

   139 HR                             TABELA_A

Row-X (SX) None 250
Not Blocking

   139 HR                             TABELA_B

Row-X (SX) None 250
Not Blocking

system@XE:SQL>

==> quando eu libero os locks automáticos, Finalizando a(s) transação/ões :

hr@XE:SQL>commit;

Commit concluído.

hr@XE:SQL>

==> Aí sim o DDL se completa :

HR#2@xe:SQL>alter table TABELA_B ADD x number;

Tabela alterada.

HR#2@xe:SQL>

====>>> Isso é o comportamente NORMAL e ESPERADO do RDBMS Oracle, yes ??? É Exatamente Por Isso (além das questão de PERFORMANCE, de disponibilidade, etc) que sistemas que querem fazer DDLs a toda hora (digamos, saem criando ou alterando tabelas dinamicamente, on-the-fly), são ODIOSOS, são um Lixo, são a ANTÍTESE de como deveria funcionar um RDBMS… POR DEFINIÇÃO, o modelo de dados (COMPLETO, com constraints, estrutura dos campos e TUDO O MAIS), deveria ser implementado no início do uso do Sistema e serem muuuuito muuuito Muuuito raramente alterados durante o uso…

A minha recomendação, portanto, é que vc simplesmente *** não *** faça DROP das tabelas, e sim simplesmente as tenha criadas com todas as colunas que precisar, OU nos casos específicos onde vc tenha dados voláteis pense na chance de usar Global Temporary Tables …

Espero ter esclarecido…

[]s

Chiappa

[José Laurindo ChiappaModerador]

Só pra deixar Escrupulosamente Claro – além de tudo o que demonstrei, há um ponto a mais aí no cenário que pode estar te pegando, qual seja : cfrme http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38asktom-085659.html nos mostra (e tá documentado Direitinho na Documentação Oracle), alguns DDLs (como o ADD COLUMN que usei no meu exemplo) por default ficam esperando “eternamente” os locks serem liberados, enquanto outros só esperam o período pré-programado de poucos segundos e se os locks não saíram eles já se jogam um ORA-00054…
REPITO, o correto seria não sair fazendo DDLs a torto e a direito, mas pro seu caso se vc tá recebendo um ORA-00054 vc vai :

• optar por um intervalo muito mais longo, usando o recurso indicado no link fornecido, para dar tempo pras transações finalizarem

OU

• solicitar que os usuários encerrem as suas transações e se não o fizerem aí vc dispara uma rotina que o faz na marra, MATANDO as sessões dos folgados em questão….

  []s

  Chiappa

[C-S-RParticipante]

Opa Chiappa, obrigado pela resposta.

But….

Até onde eu sei o lock mode 3 (exclusive) deveria ser feito somente quando se faz um delete ou modificação na coluna da PK, esse é um dos motivos de se criar um index na FK.
Acho que essa parte que é o problema, no seu teste gerou lock mode 2 (RS), no meu 3(RX)

Se estou inserindo um valor novo na tabela pai, pq o lock na tabela filha? se uma outra sessão efetuar um insert na tabela filha utilizando esse valor ele precisa dar erro de integridade, o registro na tabela pai não existe ainda.

Qual o problema de eu dropar a tabela filha? Qual problema de consistencia terei? Os dados serão apagados

Se eu tenho uma tabela com 20 tabelas filhas, quando efetuar um insert ele vai dar lock em todas? Eu acredito que não, tenho tabelas com muito mais relacionamentos e não tem todos esses locks.

“Colega, o conceito em questão aqui é que o RDBMS *** não pode *** deixar que um objeto seja alterado via DDL (ie, CREATE, ALTER, DROP, etc) enquanto há Transações ativas : pensemos, o que o RDBMS deveria fazer se eles deixar que um DROP acontecesse numa tabela enquanto uma Transação está inserindo nela ?/ Os dados vão pra onde, pro vácuo ???”

Acho que vc não entendeu a pergunta. A tabela filha não esta sendo utilizada.

[C-S-RParticipante]

Um intervalo maior não adianta a tabela pai sempre esta utilizada.
Matar as sessões?…… Caraca eu vou precisa parar a aplicação para dropar uma tabela não utilizada? Acho que se eu fizer isso é justa causa na hora kkkkkkkkk.

Melhor deixar a tabela quietinha la, não ta fazendo mal para ninguem. kkkkkk

[José Laurindo ChiappaModerador]

Seguem as respostas cada cada item :

“Até onde eu sei o lock mode 3 (exclusive) deveria ser feito somente quando se faz um delete ou modificação na coluna da PK”

==> sim, lock exclusive é quando vc tem FK sem índice sofrendo UPDATE ou DELETE (não simulei esse caso mas é assim, pesquisa em asktom que vc acha vários cases), OU quando vc pede diretamente um lock a nível de tabela :

==> veja a diferença de vc fazer um lock Exclusivo na tabela toda :

hr@XE:SQL>lock table tabela_B in exclusive mode;

Tabela(s) Bloqueada(s).

hr@XE:SQL>

==> o resultado :

system@XE:SQL>/

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
139 HR TABELA_B
Exclusive None 26
Not Blocking

system@XE:SQL>

==> veja acima que o MODE está em EXCLUSIVE, sim… Agora compare com os locks gerados por DML :

hr@XE:SQL>INSERT INTO TABELA_A VALUES (1);

1 linha criada.

hr@XE:SQL>

==> veja os locks ::

system@XE:SQL>select * from dba_dml_locks;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
139 HR TABELA_A
Row-X (SX) None 125
Not Blocking

139 HR TABELA_B
Row-X (SX) None 125
Not Blocking

system@XE:SQL>

==> além de acima estar mostrando que Não São locks Exclusivos (vide as colunas de MODE), o Row-X (SX) mostra que é um lock que permite acesso compartilhado a linhas diferentes da tabela, ou seja, protege contra DDLs (que são a nível de tabela) mas NÃO IMPEDE os DMLs em registros diferentes da tabela lockada…

“esse é um dos motivos de se criar um index na FK.”

==> Perfeitamente, isso mesmo…

“Acho que essa parte que é o problema, no seu teste gerou lock mode 2 (RS), no meu 3(RX)”

Com certeza, se nada mais estivesse em Ação vc deveria ter visto locks Row-X (SX), que nem o meu exemplo Provou, sim…. SE vc está como eu na versão 11g E está fazendo seus SQLs diretamente no sqlplus E usando SQL puro (nada de frameworks), tal como eu fiz, algum Problema vc tem aí : talvez triggers adicionais nas tabelas sendo disparados, talvez replicação de dados ativa, talvez bug (sei que houveram alguns principalmente nas versões iniciais do 11g, tanto R1 quanto R2)… É um sinal de ALERTA TOTAL aí, vc vai ter que acionar seu DBA e debugar isso….

“Se estou inserindo um valor novo na tabela pai, pq o lock na tabela filha?”

==> Eu acreditava que já tinha respondido isso, mas é ÓBVIO : se o RDBMS não bloquear as tabelas-filhas, ele abre a possibilidade de outras sessões fazer ** DDLs ** nessas tabelas-filhas, que poderiam remover as colunas-chave do relacionamento ou outros absurdos desses….

” se uma outra sessão efetuar um insert na tabela filha utilizando esse valor ele precisa dar erro de integridade, o registro na tabela pai não existe ainda.”

==> Correto, e a maneira do RDBMS garantir que as constraints que criam o relacionamento pai-filha estão Íntegras é proibir DDLs, assim proibindo alteraçõies tanto nas Constraints quanto nas colunas relacionadas…

“Se eu tenho uma tabela com 20 tabelas filhas, quando efetuar um insert ele vai dar lock em todas?”

==> ÓBVIO que sim – O EXEMPLO ABAIXO é com 6 tabelas mas seria o mesmo com 10, 20, quantas tabelas-filhas forem :

hr@XE:SQL>CREATE TABLE TABELA_1 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_1 ADD CONSTRAINT FK_1 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE TABLE TABELA_2 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_2 ADD CONSTRAINT FK_2 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE TABLE TABELA_3 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_3 ADD CONSTRAINT FK_3 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE TABLE TABELA_4 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_4 ADD CONSTRAINT FK_4 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE TABLE TABELA_5 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_5 ADD CONSTRAINT FK_5 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

hr@XE:SQL>CREATE TABLE TABELA_6 (PK_B NUMBER, FK_A NUMBER);

Tabela criada.

hr@XE:SQL>ALTER TABLE TABELA_6 ADD CONSTRAINT FK_6 FOREIGN KEY (FK_A) REFERENCES TABELA_A (PK_A);

Tabela alterada.

==> OKDOC, tenha 7 filhas na tabela_a , vamos fazer um DML na tabela A :

hr@XE:SQL>INSERT INTO TABELA_A VALUES(3);

1 linha criada.

==> ó os locks :

system@XE:SQL>SELECT * FROM DBA_DML_LOCKS;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT

---

BLOCKING_OTHERS

    92 HR                             TABELA_A                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_B                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_1                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_2                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_3                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_4                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_5                       Row-X (SX)    None                    33

Not Blocking

    92 HR                             TABELA_6                       Row-X (SX)    None                    33

Not Blocking

8 linhas selecionadas.

system@XE:SQL>

===> E ANTES QUE VC PERGUNTE, isso Não Causa problema algum de performance no RDBMS Oracle porque nele Não Há uma tabela de locks a ser mantida (os locks ficam como atributos físicos de cada registro E cada tabela, registrados em bitmaps), então a performance é basicamente a mesma NÃO IMPORTA quantos locks o RDBMS tem ativos…

“Acho que vc não entendeu a pergunta. A tabela filha não esta sendo utilizada. ”

===> Entendi perfeitamente, e como os testes mostram , o fato é que a tabela-filha ESTÁ SIM SENDO USADA : a partir do momento em que ela é filha, os metadados dela (a constraint que Exige que ela seja filha) VAI SER USADA sim, ela TEM QUE SER PROTEGIDA, o que acontece com LOCKs…. Repito, isso é CONCEITO bÁSICO de integridade de dados lógica…

” Caraca eu vou precisa parar a aplicação para dropar uma tabela não utilizada?”

===>> REPITO : as constraints são PARTE INTEGRANTE da segurança / integridade do RDBMS, então se uma tabela tem constraints, ela VAI ser lockada, vai ser protegida… Como eu disse, no RDBMS Oracle a integridade de dados é Condição de Paradigma, é levada a ferro e fogo, SIM, essa tabela NÂO ESTÀ sendo “naõ utilizada” coisa nenhuma…. Esta SIM sendo utilizada para integridade de dados….

O que vc PODE fazer, se realmente esta é uma manutenção rara (e não uma situação ROTINEIRA causada por um sistema mal e porcamente programado que a TODO MOMENTO quer fazer DDL/alterar tabelas que estão constantemente em uso – como eu disse o modo CORRETO de se usar um RDBMS é um modelo de dados fixo, qe sofra quantos MENOS alterações possíveis), vc tem alternativas como o DBMS_REDEFINE (que mantém uma “cópia” dos dados sendo mexidos enquanto a alteração que vc solicita está em curso), ou o recurso de criar uma nova tabela com a nova estrutura que vc precisa com outro nome e depois renomear ela para o nome original quando houver uma pasua no uso, ou então (outra coisa comum de se fazer) agendar o DDL para uma janela de manutenção….

[]s

Chiappa

[Autor]

Posts