Hugo Pedro Proença, © 2007 Transações z z z A compreensão do mecanismo de transações é essencial, sempre que a b base d de d dados d servir i várias á i clientes li t simultaneamente. Em SQL é possível definir explicitamente os limites de uma transação. Está normalmente associado o conceito d bloqueio de bl i (Locking), (L ki ) também bé d definido fi id explicitamente por instruções SQL. Bloqueio q de p páginas. g z Bloqueio de tabelas z Bloqueio de instâncias. z Hugo Pedro Proença, © 2007 Transações - Necessidade Cliente 2 Cliente 1 SELECT * FROM Produto; Cód. Nome SELECT * FROM Produto; Preço 1 P1 2,5 2 P2 2,8 3 P3 22 4 P4 17 SGBD UPDATE Produto UPDATE Produto SET Preço=Preço * 1.1; SET Preço=Preço * 1.1; ? Hugo Pedro Proença, © 2007 Transações - Necessidade z Ás 10:00:00, uma aplicação cliente (operada pelo “João”) efectua uma pesquisa sobre a quantidade de produtos “P1” em stock. z Ás 10:00:01, outra aplicação (operada pela “Maria”) efectua uma pesquisa semelhante. – O SGBD responde p a ambos “25” z O “João” deseja reservar 20 unidades, pelo que efectua uma dedução da respectiva quantidade à BD z A “Maria” Maria efectua uma operação semelhante... – A Base de dados regista agora “-5” como valor para a quantidade do produto “P1” em Stock. Hugo Pedro Proença, © 2007 Transações z Definição z Uma transação consiste numa unidade lógica de trabalho (código SQL) que deverá ser executado pelo SGBD. z Pode P d iincluir l i múltiplos últi l blocos bl SQL. SQL Início da transação Instrução 1 I t Instrução ã 2 ... Instrução N Fim da transação OK (Commit) ou Erro! (Rollback) Hugo Pedro Proença, © 2007 Transações z Propriedades Fundamentais - ACID z As transações possuem quatro propriedades fundamentais que as caracterizam: z Atomicidade ÆA transação deve ser executada na totalidade ou então nenhuma linha o será. Este comportamento “tudo-ou-nada” serve para garantir a consistência em operações que só fazem sentido juntas. z O exemplo clássico que pode ilustrar a necessidade desta propriedade é o processo de transferência de fundos entre duas contas bancárias. Hugo Pedro Proença, © 2007 Transações z Propriedades Fundamentais - ACID z Consistência Æ Uma transacção deve iniciar a sua execução tendo o sistema um estado conhecido e ao terminar deixá-lo num estado igualmente consistente. consistente Não se podem deixar operações em suspenso para serem tratados por outros blocos / transações. z Caso a transação aborte, o sistema volta ao estado anterior ao início da execução da transação e, por este facto, consistente. z Hugo Pedro Proença, © 2007 Transações z Propriedades Fundamentais - ACID z Isolamento ÆAs transações não devem depender de outras ou influenciar a execução de outras transações. Cada transacção deve ter a percepção de que está a executar isoladamente no sistema. sistema Quando tal não acontece é vulgar atingir situações de bloqueio mútuo (deadlocks). (deadlocks) z Diferentes transacções estão à espera de outra(s) para poderem prosseguir a sua execução. z Situação irreversível que pode conduzir à instabilidade do sistema. z Hugo Pedro Proença, © 2007 Transações z Propriedades Fundamentais - ACID z Durabilidade Æ Após a sua completa execução o objectivo da transação foi atingido e não deverão existir razões para que algumas das suas instruções sejam anuladas. anuladas z Qualquer alteração ao estado da base de dados deverão ser efectuadas por outras transações explicitamente construídas com esse objectivo Hugo Pedro Proença, © 2007 Transações z Uma transação pode ser iniciada automaticamente pelo SGBD sem que o cliente o explicite. z ALTER TABLE z CREATE z DELETE z DROP z FETCH z GRANT z INSERT z OPEN z REVOKE z SELECT z UPDATE Hugo Pedro Proença, © 2007 Transações z Limitações z Numa Transação não se podem efectuar todos os tipos de operações. Elas são indicadas para proteger grupos de clientes do processamento simultâneo e não para a administração do sistema. z Criar ou alterar uma base de dados z Alterar privilégios de utilizadores z Ler bases de dados (LOAD) z Configurar ou reconfigurar parâmetros z Actualizar estatísticas da base de dados z Criar ficheiros de DUMP Hugo Pedro Proença, © 2007 Locking z Ao escrever aplicações multi-utilizador que interagem com uma base de dados, pode-se escolher uma de duas abordagens no controlo de transacções: z Bloqueio Optimista (Optimistic Locking) Æ Delega no SGBD a responsabilidade de garantir a consistência e funcionalidade dos registos afectados pelos blocos que compõem a transação. transação z Permite a concentração na componente lógica das aplicações z Bloqueio Pessimista (Pessimistic Locking) ÆAssumese que outros clientes podem alterar a consistência da informação e explicita-se as metodologias de bloqueio desejadas z Maximização da segurança e fiabilidade dos sistemas. Hugo Pedro Proença, © 2007 Locking z Tipos de Lock z Normalmente os SGBD permitem a criação de diferentes tipos po de bloqueios oq o (LOCKS) ( ) em páginas p g ou tabelas de uma base de dados. z Uma tabela possui normalmente várias páginas (unidades básicas de trabalho, 2K) z SHARED LOCKÆ Normalmente associados a operações de leitura, tal como o nome indica pode ser atribuído simultaneamente a múltiplos utilizadores. utilizadores z EXCLUSIVE LOCKÆSó passível de atribuição a um único utilizador. Normalmente associado a p ç de escrita de informação, ç , nem sequer q operações permite qua qualquer outros clientes possam visualizar a informação. Hugo Pedro Proença, © 2007 Locking z Tipos de Lock UPDATE LOCK Æ Nível intermédio pensado para a utilização de cursores. Atribui um SHARED LOCK a um cliente com “prazo de validade”. z Esse prazo de validade esgotar-se-á logo que for efectuada uma operação de UPDATE nas tabelas ou páginas afectadas. z Existe ainda um tipo de LOCK adicional (INTENT LOCK) que funciona como um estado intermédio até à atribuição dos tipos anteriores. z Sempre que algum cliente pediu um LOCK que não pôde ser atribuído, fica temporariamente com este tipo. z Hugo Pedro Proença, © 2007 Locking – Níveis de Isolamento z Níveis de Isolamento z Normalmente os clientes de uma base de dados não explicitam p o tipo po de LOCK para p cada bloco o o ou o acção ç oa executar na base de dados. z Em vez disso é referido o nível de isolamento que deverá ser garantido à transação. z Este vai especificar ao SGBD quão protegidos (isolados) devem estar os dados a serem processados pela transação. z Solução de compromisso “Segurança-Eficiência” “Segurança Eficiência” z Explicitação do Nível de Isolamento: z SET TRANSACTION ISOLATION LEVEL <nível> Hugo Pedro Proença, © 2007 Locking – Níveis de Isolamento z Níveis de Isolamento z READ COMMITTED z É o método de funcionamento por omissão. Não permite que seja recebida informação para a qual ainda não tenha sido executado o COMMIT. Este tipo de isolamento adquire SHARE LOCKS para todas as páginas onde passará durante a transação. transação Hugo Pedro Proença, © 2007 Locking – Níveis de Isolamento z Níveis de Isolamento z READ UNCOMMITTED Tem o mesmo efeito que a palavra “NOLOCK” antes de um bloco SQL de selecção. Neste tipo de isolamento não são efectuados SHARED LOCKS, nem sequer tidos em consideração os bloqueios que eventualmente outros utilizadores terão pedido. pedido z Maximização da eficiência em detrimento da integridade e segurança do sistema. z Hugo Pedro Proença, © 2007 Locking – Níveis de Isolamento z Níveis de Isolamento z REPEATABLE READ É o tipo de bloqueio possível no SQLServer. Garante que os dados não serão afectados por quaisquer outras transações durante o período em que estiverem a ser lidos. z Reduz o nível de concorrência da base de dados e minimiza a eficiência ê da aplicação, ã garantindo a integridade e consistência absoluta da informação. z Hugo Pedro Proença, © 2007 Transações z Definição de Transacções z Compete ao cliente da base de dados definir explicitamente p o os pontos po o de inicio o e fim de cada transacção. z BEGIN TRAN <id> z É a instrução que define o inicio da execução da transação. A partir deste momento, o SGBD guarda o estado actual da base de dados de forma a que, se alguma anomalia ocorrer, seja possível o retorno a um estado anterior consistente. z As instruções que se seguem serão mantidas p p pelo SGBD até encontrar um temporariamente ponto de COMMIT ou ROLLBACK. Hugo Pedro Proença, © 2007 Transações z Definição de Transacções z COMMIT TRAN <id> z Serve para explicitar que todos os blocos SQL da transação foram executados correctamente e que o novo estado da base de dados passa a definitivo. Desta forma nenhuma outra condição poderá d á anular l as alterações l õ feitas f i pela l transação. ã z ROLLBACK TRAN <id> z Ao contrário da anterior, explicita que alguma excepção ocorreu durante a execução da transação, pelo que todas as operações efectuadas desde o seu início devem ser anuladas e restaurado o estado anterior da base de dados. Hugo Pedro Proença, © 2007 Transações z Definição de Transacções - Exemplo BEGIN TRAN p primeira_transação ç UPDATE Autores SET Validade=’12/12/2005’ WHERE Codigo=12; UPDATE Titulos SET Lucro=Lucro * 1.25 WHERE CodAutor=12 if @@error != 0 Begin ROLLBACK TRAN primeira_transação RETURN end COMMIT TRAN primeira_transação i i t ã Detecção de erros Hugo Pedro Proença, © 2007 Transações z Encadeamento de Transacções z Podem ser colocadas múltiplas transações umas dentro o de outras. o Este procedimento p o o não oé aconselhável porque: z A informação só é realmente registada na base de dados quando a transação mais externa termina. z Obriga a que um volume maior de informação seja mantido para poder restaurar o estado anterior da base de dados z Leva à perca das propriedades ACID por parte das transacções anteriores. z A variável @@trancount contém informação sobre o número de transacções activas em cada momento. Hugo Pedro Proença, © 2007 Bloqueios z Sempre que, por erro de uma aplicação cliente, não for executado igual número de instruções “BEGIN TRAN” como “COMMIT TRAN”+”ROLLBACK TRAN” o sistema pode entrar num estado de bloqueio indefinido. z Diferentes aplicações estão à espera que outra liberte o bloqueio sobre páginas ou tabelas Compete ao Administrador do SGBD detectar e resolver esta anomalia. z Existem duas formas de visualizar a informação sobre bloqueios de objectos: z SQL Enterprise Manager: Após a selecção do servidor e “Actividade Actividade Actual Actual”,, pode pode-se se receber “servidor” informação sobre “Objectos bloqueados”. z Hugo Pedro Proença, © 2007 Bloqueios sp_lock [<processo>] Æ Procedimento que devolve informação sobre os objectos bloqueados numa particular base de dados ou por um determinado processo. z Pode ser executado a partir de qualquer aplicação de interacção com a base de dados. z Após ó a identificação id ifi ã dos d processos, pode-se d resolver l a situação de bloqueio através do comando “KILL” z z KILL <processo> ÆServe para terminar um processo (transacção) que está activo no SGBD. Hugo Pedro Proença, © 2007 Bloqueios z Manutenção de consistência z Quando o próprio SGBD interrompe abruptamente o seu funcionamento, o o, ou o em situações çõ como o o a descrita anteriormente onde os processos são explicitamente terminados, os mecanismos de final de transação (COMMIT ou ROLLBACK) não são executados. z Nesta situação i ã o SGBD SG vaii automaticamente i à informação de registo (log) da base de dados e anula todas as operações efectuadas para as quais não tenha sido executado o correspondente COMMIT. z Este procedimento garante a consistência da informação. Hugo Pedro Proença, © 2007 Bloqueios z Prevenção de Bloqueios – Regras práticas: Nunca requerer a interacção do utilizador a partir de uma transação. z Se alguma verificação ao formato dos dados tiver que ser efectuada, deve-o ser antes do início da transação. z A execução de procedimentos guardados no servidor (stored procedures) pode maximizar a eficiência de praticamente todos os tipos de operações. operações z