Controlando Transações

Vimos as instruções SQL INSERT, UPDATE e DELETE. Essas três instruções, juntamente com SELECT, formam um conjunto de instruções SQL conhecidas como Data Manipulation Language. Existem outros tipos de instruções SQL, mas um tipo em particular é de especial importância para o DML. Conforme mencionado anteriormente, esse tipo é conhecido como Transaction Control Language. Essas instruções são importantes para qualquer sessão SQL na qual você usa instruções DML, pois as instruções TCL fornecem a funcionalidade para salvar ou desfazer as alterações feitas nas instruções DML. Veremos três instruções TCL nesta seção: COMMIT, ROLLBACK e SAVEPOINT:

COMMIT Salva um conjunto de modificações DML executadas na sessão atual do banco de dados
ROLLBACK Desfaz um conjunto de modificações DML executadas durante a sessão atual do banco de dados
SAVEPOINT Marca uma posição em uma sessão para se preparar para um futuro ROLLBACK para permitir que ROLLBACK restaure dados em um ponto selecionado em uma sessão que não seja o evento de confirmação mais recente.

Observe que o TCL executa as alterações feitas no banco de dados dentro de uma sessão. Uma sessão começa quando um único usuário efetua login e continua à medida que o usuário se envolve em uma série de transações, até que o usuário se desconecte do banco de dados efetuando logout ou interrompendo a conexão de alguma maneira. Um único usuário pode participar de várias sessões. Mas o oposto não é verdadeiro: apenas um usuário pode participar de uma determinada sessão. Esta discussão sobre o TCL está relacionada às sessões, incluindo as transações que ocorrem em uma determinada sessão e como o impacto dessas transações pode ou não afetar outras sessões. Com isso em mente, vamos analisar cada declaração com mais detalhes.

COMMIT

A instrução SQL COMMIT é usada para salvar as alterações feitas em uma sessão em quaisquer tabelas que foram modificadas pelas instruções DML INSERT, UPDATE e DELETE. COMMIT torna as alterações permanentes no banco de dados e, uma vez confirmadas, essas alterações não podem mais ser desfeitas com uma instrução ROLLBACK.

Antes que um COMMIT seja executado, as alterações no banco de dados podem ser desfeitas com uma instrução ROLLBACK, depois de executado o commit não é possível voltar as alterações com rollback.

O commit pode ocorrer de duas formas explícita, ou seja, executando a instrução commit, a outra forma é a implícita, quando um evento ocorre no banco de dados.

Commit explícito

A sintaxe é simples, basta executar o comando abaixo

COMMIT;

-- Podemos também usar da seguinte forma abaixo, irá gerar o mesmo resultado
COMMIT WORK;

Ao realizar instruções DML como INSERT, UPDATE e DELETE, essas alterações só serão efetuadas quando um COMMIT for executado, se não um ROLLBACK poderá ser realizado voltando as alterações ao estado anterior.

Commit implícito

Uma confirmação implícita ocorre quando determinados eventos ocorrem no banco de dados. Esses eventos incluem:

  • Imediatamente antes e imediatamente após uma tentativa de executar qualquer instrução DDL, como CREATE, ALTER, DROP, GRANT ou REVOKE. Nota: Mesmo que a instrução DDL falhe com um erro de execução (em oposição a um erro de sintaxe), um commit implícito “anterior” é executado e a alteração é salva no banco de dados.
  • Uma saída normal da maioria dos utilitários e ferramentas da Oracle, como SQL * Plus ou SQL Developer. (Uma exceção são os pré-compiladores da Oracle, que não executam uma confirmação implícita na saída, mas executam um rollback.)
update tabela_navio set porto_id = 12 where navio_id=31;
alter table porto add nota varchar2(100);

O update acima é executado mesmo sem o commit, pois um commit implícito é executado sempre que uma instrução DDL é executado que no caso foi um ALTER TABLE.

Commit e várias sessões

Uma sessão é aberta quando um usuário loga no banco e a cada login teremos uma sessão aberto, cada sessão é separada quando se trata de execuções DML, ou seja, um insert realizado em uma sessão só será possível sua visualização em outra sessão qualquer quando um commit for realizado, isso serve para outras instruções DML. Um usuário pode executar várias instruções DML como INSERT, UPDATE e DELETE, mas enquanto o usuário não efetuar o commit apenas ele próprio poderá ver as alterações realizadas.

Dessa maneira, as alterações feitas antes de qualquer COMMIT estão em uma espécie de área de preparação, onde o usuário pode trabalhar no que é quase um modo de “rascunho”. No entanto, qualquer evento de COMMIT – explícito ou implícito – tornará as alterações permanentes e exporá os novos dados a todas as sessões.

ROLLBACK

A instrução ROLLBACK é comparável à função “desfazer” comum a muitos aplicativos de software. ROLLBACK desfaz alterações no banco de dados que foram executadas em uma determinada sessão. Ele não remove nenhuma alteração que já tenha sido confirmada. As únicas alterações revertidas são aquelas emitidas na sessão que está executando a reversão.

Se um programa terminar de forma anormal, o banco de dados emitirá um ROLLBACK implícito. Alterações não confirmadas no momento de um encerramento anormal de, por exemplo, SQL * Plus ou SQL Developer não serão confirmadas no banco de dados.

SAVEPOINT

A instrução SAVEPOINT estabelece pontos de demarcação em uma transação para habilitar qualquer instrução COMMIT ou ROLLBACK a subdividir os pontos nos quais os dados podem ser salvos ou desfeitos posteriormente. Veja exemplo:

commit;
update navio set porto_id = 21 where navio_id = 12;
savepoint sp_1;
update navio set porto_id = 22 where navio_id = 12;
rollback work to sp_1;
commit;

Neste exemplo, começamos com um COMMIT explícito na linha 1 e, em seguida, emitimos uma instrução UPDATE. Em seguida, na linha 3, emitimos uma instrução SAVEPOINT e denominamos SP_1. Isso é seguido por uma segunda declaração UPDATE. Temos uma opção na linha 5 que ainda não vimos que serve para “desfazer” a instrução UPDATE anterior (mas apenas o segundo UPDATE, e não o primeiro). Conseguimos isso revertendo para o SAVEPOINT. E por último efetuamos o COMMIT em nossas alterações.

As regras para usar o SAVEPOINT incluem o seguinte:

  • Todas as instruções SAVEPOINT devem incluir um nome. O nome deve ser especificado usando as mesmas regras e limitações para nomear objetos de banco de dados. Nos bastidores, o nome do SAVEPOINT que você cria está associado a um número de alteração do sistema (SCN). É isso que o SAVEPOINT está marcando.
  • Você não deve duplicar nomes do SAVEPOINT em uma única transação – e lembre-se de que uma transação é uma série de uma ou mais instruções SQL que terminam com um evento de confirmação. Se você duplicar um nome, não receberá um erro de sintaxe ou de execução. Em vez disso, o novo SAVEPOINT substituirá simplesmente o SAVEPOINT anterior, apagando-o efetivamente.
  • Depois que um evento de confirmação ocorre (um evento de confirmação explícito ou implícito), todos os pontos de salvamento existentes são apagados da memória. Quaisquer referências a elas por futuras instruções TCL produzirão um código de erro.
commit;
update (…);
savepoint mark_01;
commit;
rollback to mark_01;

No exemplo anterior, a instrução ROLLBACK na linha 5 está incorreta, o ROLLBACK faz uma referência a um SAVEPOINT que não existe, portanto, produz um erro. Sem a referência de ponto de salvamento nomeada, o ROLLBACK seria executado muito bem e simplesmente não causaria impacto no banco de dados. Mas com a referência a um SAVEPOINT nomeado que não existe mais no momento em que o ROLLBACK é executado, o resultado da linha 5 é um código de erro.

Se a palavra única ROLLBACK for executada sozinha, ela ignorará quaisquer instruções SAVEPOINT que possam ter sido executadas desde o evento de COMMIT mais recente e desfará as alterações feitas pelo usuário na sessão desde a hora do último evento de commit. Há duas sintaxes válidas para o ROLLBACK: 

rollback to nome_do_savepoint;
rollback work to nome_do_savepoint; 
-- a palavra reservada work é opcional

Se uma instrução ROLLBACK for executada com o nome de SAVEPOINT inexistente, o SQL exibirá um código de erro avisando que a reversão estava tentando reverter para um SAVEPOINT que nunca foi estabelecido. O ROLLBACK falhará e nada será alterado em relação ao estado do banco de dados. Nesse ponto, quaisquer alterações pendentes permanecerão em um estado não confirmado. Nesse ponto, as alterações não confirmadas ainda podem ser confirmadas ou revertidas.

Referência

OCA Oracle Database Exam Guide (Exam 1Z0-071). Páginas 186 – 199.

Deixe um comentário