Propriedades ACID em SGBD Oracle

UNIVERSIDADE TECNOLÓGICA FEDERAL DO PARANÁ CAMPUS PATO BRANCO DIRETORIA DE PESQUISA E PÓS-GRADUAÇÃO II CURSO DE ESPECIALIZAÇÃO EM BANCO DE DADOS

IGOR ALLEN BEZERRA DE MAGALHÃES RITZMANN

PROPRIEDADES ACID EM SGBD ORACLE

TRABALHO ACADÊMICO

PATO BRANCO 2015

IGOR ALLEN BEZERRA DE MAGALHÃES RITZMANN

PROPRIEDADES ACID EM SGBD ORACLE

Trabalho do Curso apresentada como requisito parcial à obtenção de nota em II especialização em banco de dados, do Departamento de especialização em banco de dados, da Universidade Tecnológica Federal do Paraná.

PATO BRANCO 2015

AGRADECIMENTOS

Agradeço primeiramente a Deus e a todos que de forma direta ou indireta contribuíram para que eu pudesse me dedicar a este trabalho e contribuir para a minha formação principalmente ao professor Jean Adams pela oportunidade dada.

RESUMO

RITZMANN, Igor Allen Bezerra de Magalhães. Propriedades ACID em SGBD Oracle. 2015. 26 folhas. Trabalho de Curso (Especialização em Banco de dados Universidade Tecnológica Federal do Paraná. Pato Branco, 2015.

O presente trabalho visa entender as propriedades ACID e como o SGBD Oracle consegue atende-las por meio de suas estruturas (memória, processos e armazenamentos). Será abordado de forma que possamos entender estes processos de maneira não profunda. As informações das características do ACID no banco de dados Oracle foi levantado através de pesquisa bibliográfica com embasamento teórico. Palavras-chave: ACID. SGBD. Oracle. Banco de dados

ABSTRACT

RITZMANN, Igor Allen Bezerra de Magalhães. Property ACID on SGBD Oracle. 2015. 26 folhas. Trabalho de Curso (Especialização em Banco de dados – Federal Technology University - Paraná. Pato Branco, 2015. - Federal Technology University - Paraná. Pato Branco, 2015.

This study aims to understand the ACID properties and as the Oracle DBMS can meet them through their structures (memory, processes and stores). Will be addressed so that we can understand these processes not deeply. Keywords: ACID. SGBD. Oracle. Database

LISTA DE SIGLAS

ACID

Atomicidade, Consistência, Isolamento e Durabilidade

SGA

Shared Global Area

SGBD

Sistema de Gerenciamento de Banco de Dados

DML

Data manipulation language

SUMÁRIO

1 INTRODUÇÃO .....................................................................................................13 1.1 PROPRIEDADES ACID DE TRANSAÇÔES ....................................................14 2 ARQUITETURA DO BANCO DE DADOS ORACLE ...........................................15 2.1 ESTRUTURA DE MEMÓRIA ORACLE ............................................................16 2.1.3.1 Estruturas cache de biblioteca ....................................................................18 2.1.3.2 Cache de dicionário de dados .....................................................................18 2.1.3.3 Área PL/SQL ...............................................................................................18 2.1.3.4 Cache de resultados de funções PL/SQL e consultas SQL ........................18 2.2 ESTRUTURA DE PROCESSOS ORACLE .......................................................19 2.2.1 System Monitor-SMON ...................................................................................19 2.2.2 Process Monitor- PMON .................................................................................19 2.2.3 Database Writer-DBW ....................................................................................19 2.2.4 Log Writer-LGWR ...........................................................................................20 2.2.5 Checkpoint Process-CKPT .............................................................................20 2.3 ESTRUTURA DE ARMAZENAMENTO ............................................................20 2.3.1 Arquivo De Controle ........................................................................................21 2.3.2 Arquivo De Redo Log Online ..........................................................................21 2.3.3 Arquivo De Dados ...........................................................................................22 2.4 TRANSAÇÕES SGBD ORACLE ......................................................................23 2.4.1 Atomicidade ....................................................................................................24 2.4.2 Consistência ...................................................................................................24 2.4.3 Isolamento ......................................................................................................24 2.4.4 Durabilidade ....................................................................................................24 3 CONCLUSÃO .......................................................................................................25 REFERÊNCIAS .......................................................................................................26

13

1 INTRODUÇÃO

Atualmente as aplicações planejadas e codificadas/escritas para obtenção de sistemas de informação utilizam algum tipo de SGBD-Sistema de gerenciamento de banco de dados confiável. Neste trabalho, pretende-se demonstrar as características do SGBD Oracle a fim de obter informações de conformidade com as propriedades ACID (Atomicidade, Consistência, Isolamento e Durabilidade). De acordo com (SANTOS, 2008, p.02), um SGBD traz uma coleção de dados inter-relacionados e uma coleção de programas para acesso aos dados. Espera-se que o SGBD proporcione um ambiente eficiente para recuperação e armazenamento das informações do banco de dados. O termo transação refere-se a uma coleção de operações que forma uma única unidade lógica de trabalho. Exemplo de uma transação de dinheiro de uma conta para outra (transação que consiste em duas atualizações uma em cada conta) SANTOS (2008, p.03). Ao verificar no dicionário Aurélio o significado de transação é: “Em um sistema de informações, operação lógica que não fere a coerência dos dados armazenados”. Uma transação é uma visão abstrata que o SGBD tem de um programa de usuário

que

é

uma

sequência

de

leituras

(reads)

e

escritas

(writes).

Ramakrishnan(2003),

Segundo o autor LEGATTI E FURUSHIMA (2015) Uma transação é uma sequência (1 ou mais) de operações de leitura (read) e escrita (write) executadas por um programa (unidade lógica de trabalho) sobre um sistema gerenciador de banco de dados (SGDB), ou seja, trata-se de uma visão abstrata que o sistema gerenciador de banco de dados (SGDB) tem de um programa. Esta transação em um SGBD de modelo transacional clássico (banco de dados relacional) possui quatro importantes propriedades cujo objetivo é manter os dados protegidos de acesso concorrente e de falhas de sistema. Uma transação possui quatro diferentes propriedades chamadas de ACID, um acrônimo derivado da primeira letra das seguintes propriedades: Atomicidade, Consistência, Isolamento e Durabilidade.

14

Segundo REVERBEL(1999) (...) O SGBD “vê” cada transação como uma sequência de leituras e escritas delimitada por comandos begin e commit- abort (...). Nas transações o SGBD deve garantir:  Todas as operações na transformação foram completadas com sucesso e seu efeito será gravado permanentemente no banco de dados.  Transação não terá nenhum efeito sobre o banco de dados ou outras transações casos como a transação falhar durante a execução. O SGBD precisa finalizar uma transação com sucesso, tornar as atualizações permanentes, terminar uma transação com erro e retornar o banco de dados à posição anterior à transação. Os estados de uma transação podem ser: Ativo, parcialmente efetivada(commit), efetivada, falha (abort rollback) e encerrada.

1.1 PROPRIEDADES ACID DE TRANSAÇÔES

ACID é um acrônimo de Atomicidade, Consistência, Isolamento e Durabilidade. Vamos destrinchar cada uma destes elementos abaixo Segundo RAMAKRISHNAN(2003):  Atomicidade: A execução de uma transação deve ser atômica, ou todas as ações são executadas ou nenhuma é (tudo ou nada);  Consistência: Cada transação executada isoladamente deve preservar a consistência do banco de dados- banco continua consistente ao final da transação (preservado);  Isolamento: A execução de uma transação não deve sofrer interferência de outras transações concorrentes.  Durabilidade: Toda transação que for finalizada de forma bem-sucedida deve persistir seus resultados em banco mesmo na presença de falhas no sistemaapós o ponto de confirmação, as alterações devem persistir no banco (persistente).

15

2 ARQUITETURA DO BANCO DE DADOS ORACLE

Vamos explorar como o SGBD Oracle consegue atender as características ACID por meio de sua estrutura de memória, processos e armazenamento que as transações fazem uso no decorrer da arquitetura computacional que segundo LEGATTI E FURUSHIMA (2015), A transação em seu ciclo de vida faz uso de três principais recursos da arquitetura computacional, tais como: área de armazenamento volátil (memoria), CPU e área de armazenamento permanente (Disco - Storage). O fato de uma transação ser interrompida no meio de seu ciclo de vida pode deixar o banco de dados em um estado vulnerável e inconsistente. O intuito das propriedades ACID é impor ao SGDB o tratamento dos efeitos de transações parciais (transações interrompidas) do banco de dados.

A figura abaixo mostra a estrutura do banco de dados Oracle com seus componentes primários:

Figura 1: Componentes primários de um banco de dados Oracle. Fonte: Oracle Architectural Components (2001)

16

2.1 ESTRUTURA DE MEMÓRIA ORACLE Segundo WATSON(2010), uma instância Oracle é composta por um bloco de memória compartilhada conhecida como área global de Sistema (SGA- Shared global area) e por vários processos em Segundo plano. A estrutura do SGA mínima: Cache do buffer do banco de dados, Buffer de log e Shared pool.

2.1.1

Cache De Buffer Do Banco De Dados

De acordo com Watson(2010), O cache de buffer do banco de dados é a área de trabalho do Oracle para execução de SQL, ao atualizar os dados, as sessões de usuários não atualizam diretamente no disco. Os blocos que contém os dados de interesse são primeiramente copiados no cache de buffer do banco de dados. Alterações como inserção de novas linhas e exclusão são aplicadas a cópias dos blocos de dados no cache de buffer do banco. Os blocos ficam por um tempo em cache até que o buffer que eles estão ocupando sejam solicitados para armazenar outro bloco em cache. Teoricamente, todos os blocos que contém dados acessados com frequência estarão no cache de buffer do banco de dados minimizando o I/O – imput/output do disco. Consideremos abaixo um usuário final recuperando um registro de funcionário e atualizando:  select ult_nome, salario, id_trab from empregados where empregado_id=10;  update empregados set salario=salario * 1.0 where empregado_id=10;  commit;

O processo de usuário solicitou o número do funcionário através da instrução SQL select, onde a instrução recupera detalhes a serem enviados ao processo de usuário e formatados para exibição. O processo de servidor da sessão ler o bloco de dados que contém a linha em um arquivo de dados de um buffer. Após é executado a instrução update e commit e serão construídas e enviadas para o processo de servidor para execução. Caso o tempo não tenha sido longo, ao ser feito o update o bloco com a linha ainda estará em cache. Teremos dois acessos do bloco no cache e um acesso no bloco em disco 50% de uso do cache. Importante saber que um buffer que armazena um bloco com a informação do cache diferente do disco é denominado buffer sujo e será denominado buffer limpo quando outro bloco for copiado para o buffer sujo, neste instante, a imagem do

17

bloco no buffer será igual ao bloco no disco. Os buffers sujos são gravados de volta no disco- arquivos de dados, quando o buffer ficar limpo novamente. Importante ressaltar que a instrução commit e o buffer não tem correlação de frequência de atualização no arquivo de dados, o responsável pelas gravações em arquivo de dados fica a cargo de um processo de segundo plano o DBW-database writer. 2.1.2 Cache De Buffer De Log De acordo com Watson(2010), o buffer de log é a área de preparação pequena e de curto prazo para transitar as alterações antes que eles sejam gravados no redo log no disco. O vetor de alteração é gerada por instruções de manipulação de dados DML-Data manipulation language que geram vetores de alteração aplicados aos dados. O redo log é a garantia do banco de dados de que os dados não serão perdidos. Quando um bloco de dados é alterado, os vetores de alteração aplicados ao bloco são gravados no arquivo de redo log. As informações de redo log não são gravadas no arquivo de redo log pelo processo de servidor da sessão. Para não perder tempo e desempenho as sessões gravam o redo no buffer de log na memória sendo um processo mais rápido que no disco diretamente. O buffer de log é gravado no arquivo de log e quando uma instrução commit é executado a gravação do buffer de log acontece em tempo real pelo processo de segundo plano LGWR- Log writer. O tamanho do buffer de log é baseado no número de CPUs do servidor. Sobre a instrução commit é uma parte crítica da arquitetura Oracle pois a garantia que uma transação que sofreu commit nunca será perdida tem como base De acordo com WATSON(2010):

(...) A garantia de que uma transação que sofreu commit nunca será perdida tem como base o seguinte: a mensagem commit-complete não é retornada para a sessão até que os blocos de dados no cache tenham sido alterados (o que significa que a transação foi concluída) e os vetores de alteração forem gravados no redo log no disco(e, assim, a transação poderá ser recuperada, se necessário).

18

2.1.3 Shared Pool O Shared pool é a estrutura de memória mais complexa do SGA, é dividido em várias subestruturas gerenciadas pelo servidor Oracle onde temos:

2.1.3.1Estruturas cache de biblioteca Área de memória usada para armazenar o código executado recentemente na forma de parse que transforma o código do programador em algo executável e reutilizável.

2.1.3.2Cache de dicionário de dados Armazena as definições de objetos usados recentemente como descrição de tabelas, índices, usuários e outros metadados, como exemplo, usar a instrução select e SELECT onde se for padronizado será menos utilizado o dicionário de dados.

2.1.3.3Área PL/SQL São os objetos como procedure, funções chamados pela sessão e lido a partir do dicionário de dados onde para impedir a leitura repetida os objetos são armazenados em cache nesta área PL/SQL. A primeira consulta é feita no dicionário de dados porém, as próximas em cache.

2.1.3.4Cache de resultados de funções PL/SQL e consultas SQL Devido em algumas aplicações a mesma consulta é executada várias vezes pela mesma sessão ou por sessões diferentes o servidor pode recuperar estas informações em cache. O mecanismo de cache de resultados é inteligente para controlar se as tabelas foram atualizadas se não acontecido o resultado será invalido fazendo-se necessário novamente a execução da consulta não havendo perigo de receber resultado desatualizado em cache.

19

A Atomicidade e Durabilidade de uma transação segundo LEGATTI E FURUSHIMA (2015) Uma transação é completada somente após sua confirmação ou cancelamento, conforme as propriedades de atomicidade (A) e durabilidade (D). Para manter este mecanismo, um SGBD de modelo transacional clássico (banco de dados relacional) mantém um registro de log para operações de escritas no banco de dados, nomeado em contexto Oracle, como "redo logs". Essa estrutura é responsável por garantir as propriedades de atomicidade (A) e durabilidade (D) de modo que se o banco de dados sofrer uma eventual queda antes que os dados alterados sejam escritos de forma persistente em disco, o log será utilizado para restaurar essas informações quando o sistema for normalizado. Deste modo, o SGDB garante a atomicidade, desfazendo as ações de transações que não realizaram a confirmação (operação de COMMIT) e a durabilidade, garantindo que todas as ações de transações que realizaram a confirmação (operação de COMMIT) fiquem persistentes em disco e se tornem tolerantes às falhas do sistema (banco de dados)

2.2 ESTRUTURA DE PROCESSOS ORACLE Os processos em segundo plano são iniciado assim que a instância é iniciada e executada. Existe cinco processos em segundo plano: 2.2.1 System Monitor-SMON Tem a tarefa de montar e abrir um banco de dados, resumindo o SMON monta um banco de dados localizando e validando o arquivo de controle do banco de dados, após, abre um banco de dados localizando e validando o arquivo de controle do arquivo de dados e os arquivos de log online. 2.2.2 Process Monitor- PMON Responsável por monitorar todos os processos de servidor e detectar os problemas com as sessões onde se uma sessão terminar de modo anormal o processo de servidor será destruído pelo PMON e retornará a memória ao pool de memória do Sistema operacional e fará rollback de todas as transações incompletas que possam estar em andamento. 2.2.3 Database Writer-DBW O DBW grava os buffer no disco. Padrão de oito (8) DBWn por CPU. As sessões não gravam diretamente no disco, pois é gravado em buffers no cache de

20

buffer do banco de dados. Também grava buffers sujos do cache de buffer do banco nos arquivos de dados gravando o menor número possível de buffer para não perder desempenho devido I/O de disco prejudicar o desempenho. Grava sempre no disco os buffers que as sessões não tiveram interessada por um tempo. Existem quatro circunstâncias que fará o DBW escrever em disco: Ausência de buffers livre, excesso de buffers sujos, tempo limite de três segundos e quando há um check-point.

2.2.4 Log Writer-LGWR O LGWR grava o conteúdo do buffer de log no disco nos arquivos de redo log online (flush). Quando uma sessão faz uma alteração ao bloco no cache de buffer do banco de dados, antes de alterar no bloco é gravado o vetor de alteração a ser aplicado no buffer de log para garantir que nenhum trabalho seja perdido e estes vetores são gravados em disco o mais rápido possível. O LGWR gera um fluxo de de gravação do conteúdo do buffer de log para o arquivo de redo log online no disco praticamente em tempo real, com o commit é em tempo real, suspendendo a sessão até gravar o buffer no disco, confirmando o commit e a transação se torna irreversível. Existem três circunstancias para o LGWR fazer flush do buffer de log para o disco: Sessão emitir commit, buffer de log com 1/3 completo e o DBW estiver para gravar buffers sujos. 2.2.5 Checkpoint Process-CKPT Após uma falha, todos os vetores de alteração que fazem referência aos buffers sujos que não foram gravados em disco pelo DBW na hora de uma falha devem ser retirados do redo log e aplicados aos blocos de dados sendo o processo de restauração. O DBW faz checkpont incremental. O CKPT atualiza o arquivo de controle com a posição do checkpoint atual. 2.3 ESTRUTURA DE ARMAZENAMENTO O banco de dados Oracle faz abstração completa entre o armazenamento lógico e o físico onde segundo Watson(2010), o armazenamento de dados lógicos fica no segmento, onde uma tabela é um segmento e estes segmentos são

21

armazenados em arquivos de dados. A abstração do armazenamento lógico para o armazenamento físico é dado pelo uso de tablespaces e suas estruturas mantidas no dicionário de dados. Abaixo foto que mostra as estruturas de armazenamento e suas abstrações:

FIGURA 02: Estrutura lógica e fisica Fonte: Oracle Architectural Components (2001)

2.3.1 Arquivo De Controle O arquivo de controle é multiplexado podendo existir várias cópias sendo o seu tamanho pequeno, porém, vital pois contém ponteiros para o restante do banco de dados como os locais dos redo log online e dos arquivos de dados e dos arquivos de redo log arquivado mais recentes se o banco de dados estiver em modo archivelog. Não pode se viver sem o arquivo de controle 2.3.2 Arquivo De Redo Log Online O redo log online armazena vetores de alteração aplicado ao banco de dados, tendo o mínimo de informação necessária para construir ou refazer o trabalho. Se perder todo o banco de dados estes arquivos poderão ser aplicados aos backups de de arquivo de dados para refazer o trabalho até onde parou de funcionar. Composto de dois arquivos o arquivo de redo log online obrigatório e os redo logs arquivados opcional. O redo log online é composto de grupos de arquivos, cada um conhecido como um membro sendo necessários no mínimo dois grupos para o Oracle funcionar. Pode-se criar mais de um grupo para aumento de desempenho e mais de

22

um membro por grupo por questões de segurança. O tamanho destes arquivos por padrão são de 50 Megabytes. Pode-se criar, excluir e adicionar arquivos de redo log a qualquer momento e não requer tempo de inatividade sendo transparente para o usuário final. WATSON (2010).

2.3.3 Arquivo De Dados O arquivo de dados deve ter no mínimo dois arquivos de dados a serem criados na hora da criação do banco de dados o tablespace SYSTEM que armazena o dicionário de dados e o tablespace SYSAUX que armazenam os dados que auxiliam o dicionário de dados. Estes arquivos de dados são repositórios de dados com tamanhos e números ilimitados (tamanho limitado pela capacidade do hardware). Estes arquivos de dados são estruturas físicas visíveis para o administrador de sistema, logicamente são repositórios para os segmentos que contém dados dos usuários que os programadores veem e também para os segmentos que compõe o dicionário de dados. Um segmento é uma estrutura de armazenamento para dados onde os típicos são tabelas e índices. No nível do sistema operacional um arquivo de dados consiste em uma quantidade de blocos do sistema operacional, internamente estes arquivos de dados são formatados em blocos Oracle sendo os blocos numerados consecutivamente dentro de cada arquivo de dados com blocos de tamanho fixo onde os blocos podem ter o tamanho de 2 a 64kb sem associação com o tamanho de bloco do Oracle com o tamanho do bloco do sistema operacional. WATSON (2010). Abaixo vemos a figura de esquema dos arquivos de dados físicos e lógicos:

23

Figura 3: Hierarquia de armazenamento lógico e físico do Oracle. Fonte: OCA Oracle Database 11g: Administração I(2010)

2.4 TRANSAÇÕES SGBD ORACLE Segundo Watson(2010), o mecanismo Oracle para assegurar a integridade transacional usa a combinação de segmentos undo e os arquivos de redo log( sendo compatível com os padrões internacionais de processamento de dados) para garantir um banco de dados relacional que atenda as características de ACID a fim de garantir a Atomicidade, a consistência, o isolamento e a durabilidade. O uso do undo é essencial para garantir transações e reverter as instruções de linguagem de manipulação de dados DML(Data manipulation language) sendo referenciados como dados de Rollback(segmento de undo). Sempre que uma transação altera os dados, a versão dos dados anteriores à atualização é gravada em um segmento de undo. Fazer rollback significa utilizar os segmentos de undo para construir uma imagem dos dados como eles eram antes da transação ter ocorrido sendo feito de maneira automática garantindo as características do ACID. O uso de flashback pode aumentar o poder do undo dando a opção de se consultar o banco de dados como ele era em algum tempo do passado. O uso do rollback garante armazenar as versões anteriores dos dados fazendo com que transações incompletas possam ser revertidas automaticamente.

24

2.4.1 Atomicidade Neste quesito, toda a transação deve ser concluído 100% ou nenhuma deve ser concluído 0%. O Oracle garante atomicidade absoluta através de segmentos de undo. 2.4.2 Consistência O princípio da consistência requer que o banco de dados garanta que os valores alterados não sejam vistos na consulta, com o uso dos segmentos de undo o SGBD Oracle garante que se uma consulta for bem sucedida o resultado será consistente. 2.4.3 Isolamento O princípio do isolamento diz que somente a sessão que estiver em execução da transação poderá ver suas alterações e as outras sessões ver os dados inalterados (não os novos valores), pois pode ocorrer da transação completa não ocorrer. O isolamento da transação requer que o banco de dados oculte as transações em andamento dos outros usuários, visualizando a versão pré-atualizada dos dados até que a transação seja concluída completamente em um conjunto consistente onde o SGBD Oracle garante o isolamento através do uso de segmentos de undo. 2.4.4 Durabilidade Uma vez a transação concluída deve-se ser impossível para o banco de dados perder os dados. Segundo Watson (2010) (...) em um banco de dados relacional não é permitido perder dados(...) o SGBD Oracle atende a durabilidade através do uso dos arquivos de log nas formas de redo log online e redo log arquivado, sendo impossível um banco de dados configurado corretamente perca dados.

25

3 CONCLUSÃO

Podemos observar que a arquitetura do banco de dados relacional Oracle contém em sua arquitetura componentes primários e tecnologias de memória, processos e armazenamento que ao longo do tempo vem se aperfeiçoando sendo um SGBD relacional que atende aos requisitos de ACID, trazendo confiabilidade nas transações requisitadas e no que diz respeito a Atomicidade, Consistência, Isolamento e Durabilidade traz excelentes recursos para a entrega de transações. Vemos que o seu controle de estrutura de memória, undo é robusto e garante que os dados vão ser trabalhados de forma orquestrada. Concluímos que o SGBD Oracle é um banco de dados relacional que tem as características que um bom banco de dados deve ter e que atende os requisitos de um sistema confiável para as transações baseado nas características do ACID.

26

REFERÊNCIAS

LEGATTI Eduardo; FURUSHIMA Carlos. Performance, Diagnostic e Tuning: Asynchronous Commit. Disponível em: < http://www.oracle.com/technetwork/pt/articles/database-performance/asynchronouscommit-2488891-ptb.html> Acessado em: 23 Abril 2015.

ORACLE. Oracle Architectural Components. Disponível em: < http://www.oracle.com/technetwork/database/migration/oraclearchitectureoverview129326.pdf>. Acessado em: 24 maio 2015.

RAMAKRISHNAN Raghu; GEHRKE Johannes. Database Management Systems. 3ed, Hardcover, 2003.

REVERBEL, Francisco. Gerência de transação. Disponível em: . Acesso em: 24 abril 2015.

SANTOS, Rodrigo de Carvalho. Estudo Comparativo dos Sistemas Gerenciadores de Bancos de Dados: Oracle, SQL Server e PostgreSQL. Disponível em: . Acesso em: 22 abril. 2015.

WATSON, John. OCA ORACLE DATABASE 11g Administração I: guia do exame 1Z0-052. 1.ed, Porto Alegre: Bookman, 2010.