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