[firebase-br] Rollback que não desfaz nada!

Evandro's mailing lists (Please, don't send personal messages to this address) listasjr em gmail.com
Qui Nov 26 17:12:43 -03 2009


Já encontrei a resposta:

Em resumo DDL não aceita o comando rollback passado pelo DBA/programador
pois eles tem um commit implícito após a execução.

Me perdoem aqueles que não entendem inglês.

-Evandro

   >   QUESTION POSED ON: 14 August 2008
*I have three questions:

1. Why would DDL statements not roll back?

2. Why would DDL statements not be written to roll back segments?
*

*3. Why can DDL statement roll back?*

 >   Question 1 and 3 are the same question. And the question is incorrect
in its assumption. DDL
statements<http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1235584,00.html>
 do roll back. If the DDL statement hits an error before it completes, the
operation will be rolled back. But if the DDL statement was successful, it
will immediately be followed by an implicit COMMIT. Since the COMMIT was
issued for you, you cannot issue a ROLLBACK to undo the DDL statement. By
the nature of these operations, you cannot roll back past a COMMIT.

DDL statements used to not be written to rollback segments. But many DDL
statements modify the Data Dictionary. And the modifications of the Data
Dictionary are written to the roll back segments (or Undo tablespace). These
modifications are also written to the online redo logs. If I recall
correctly, more and more DDL statements are being written to the online redo
logs to aid in recovery efforts.



2009/11/26 Evandro's mailing lists (Please, don't send personal messages to
this address) <listasjr em gmail.com>

> Caros colegas
>
> Como faço para desfazer comandos DDL dentro de uma transação? Imagino que
> isso seja possível pois o IBExpert aparentemente consegue fazer isso.
>
> Se executo:
>
> set transaction READ WRITE WAIT SNAPSHOT;
>
> DECLARE EXTERNAL FUNCTION ALLTRIM
>     CSTRING(256)
>     RETURNS CSTRING(256) FREE_IT
>     ENTRY_POINT 'fn_alltrim_c' MODULE_NAME 'udflib';
>
> DECLARE EXTERNAL FUNCTION CGC
>     CSTRING(256)
>     RETURNS INTEGER BY VALUE
>     ENTRY_POINT 'fn_cgc_c' MODULE_NAME 'udflib';
>
> rollback         work;
>
> no isql eu recebo a mensagem mentirosa:
>
> *Rolling back work
> *
> é mentirosa porque se rodar exatamente a mesma coisa terei esse erro pois
> ele vai dizer que as UDF já existem. Mas afinal, se eu dei rollback como foi
> que ele permitiu criar as UDFs? Ele não deveria ter desfeito? O que fiz de
> errado?
>
>  Rolling back work.
> Statement failed, SQLCODE = -607
> unsuccessful metadata update
> -DEFINE FUNCTION failed
> -attempt to store duplicate value (visible to active transactions) in
> unique ind
> ex "RDB$INDEX_9"
> After line 1 in file
> D:\Dev\NeoUpdate\NeoUpdateManager\bin\Debug\8069961c-e45e-4
> 15b-95d6-ce01d82dd7cc.sql
> Statement failed, SQLCODE = -607
> unsuccessful metadata update
> -DEFINE FUNCTION failed
> -attempt to store duplicate value (visible to active transactions) in
> unique ind
> ex "RDB$INDEX_9"
> After line 4 in file
> D:\Dev\NeoUpdate\NeoUpdateManager\bin\Debug\8069961c-e45e-4
> 15b-95d6-ce01d82dd7cc.sql
>
>
> Vocês acham que é um problema específico de UFD? Não é não:
>
> Se executar esse comando duas vezes obtenho esse erro:
>
> set transaction READ WRITE WAIT SNAPSHOT;
>
> CREATE TABLE JUNIOR (
>     NEW_FIELD INTEGER);
>  rollback         work;
>
>
> #Erro 1 - Rolling back work.
> Statement failed, SQLCODE = -607
> unsuccessful metadata update
> -Table JUNIOR already exists
> After line 1 in file
> D:\Dev\NeoUpdate\NeoUpdateManager\bin\Debug\d65fe842-ad41-4f63-a52c-6c9331af3368.sql
>
> Muito obrigado!
> -Evandro
>
>



Mais detalhes sobre a lista de discussão lista