Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Programa de TABD 2004/2005
• Componente teórica
–
–
Data Warehousing e OLAP
•
Conceitos básicos
Projecto de data warehouses
Particionamento de dados
Noções básicas de administração
•
•
•
Modelo multidimensional
Desempenho e optimização de queries
Discos RAID
Área de estágio e ETT
Henrique Madeira, DEI-FCTUC, 2001
2005/2006
Instituto Politécnico da Guarda, ESTG
1
–
Desenvolvimento de uma aplicação de base de dados (parcialmente feita fora do espaço das
aulas)
–
Aulas práticas
para consolidação de aspectos específicos da matéria básica de bases de dados:
•
•
Exercícios práticos sobre transacções e controlo de concorrência
Exercícios práticos sobre indexação
para consolidação de aspectos específicos da matéria de Data Warehousing e OLAP:
•
Definição de esquemas em estrela
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Conversar sobre bases de dados:
Henrique Madeira, DEI-FCTUC, 2001
• Componente prática
Henrique Madeira
Instituto Politécnico da Guarda, ESTG
2
Conversar sobre bases de dados:
algumas perguntas de diagnóstico
algumas perguntas de diagnóstico (cont.)
• O que é uma base de dados?
• O que é SQL?
• O que é um SGBD?
• O que é uma base de dados relacional?
• Quais os quatro comandos SQL mais importantes para
manipulação de dados?
• O que é o modelo relacional?
• O que são transacções?
• O que é integridade dos dados?
• Quais as propriedades das transacções?
• Como se garante a integridade dos dados?
• Como funciona o controlo de concorrência (bloqueios)?
Henrique Madeira, DEI-FCTUC, 2001
• O que é normalização?
• O que são formas normais?
• Quais os inconvenientes de as tabelas não estarem normalizadas?
• Como se normaliza tabelas?
• O que são modelos de Entidade-Relacionamento (ER)?
• O que são operações relacionais?
• Quais os principais métodos de acesso a dados?
Henrique Madeira, DEI-FCTUC, 2001
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Revisão e complemento de bases de dados relacionais
Revisão de conceitos básicos
Transacções e controlo de concorrência
Indexação
Tópicos Avançados
de Bases de Dados
• O que são índices e para que servem?
• Quais as regras básicas para definição de índices?
• O que é projecto lógico e projecto físico em bases de dados?
• Quais as principais operações relacionais?
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
3
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Conversar sobre bases de dados:
Instituto Politécnico da Guarda, ESTG
4
Transacções
algumas perguntas de diagnóstico (cont.)
• O que é programação no servidor (PL/SQL no caso do Oracle)?
• Mecanismo que valida (ou anula) conjuntos de operações básicas na
base de dados de uma maneira atómica:
• Quais as principais vantagens e desvantagens de programar no
servidor de bases de dados?
ou todas as operações elementares pertencentes à
transacção são confirmadas, ou são todas anuladas e
é como se nunca tivessem existido.
• O que são excepções e porque é que são muito importantes?
• O que são cursores e para que servem?
• O que são triggers?
Henrique Madeira, DEI-FCTUC, 2001
• Quais os grandes grupos de tarefas na administração de bases de
dados
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
5
INSERT
TRANSACÇÃO
INSERT DELETE UPDATE INSERT
COMMIT
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
UPDATE
INSERT
Henrique Madeira, DEI-FCTUC, 2001
TRANSACÇÃO
• Quando se fala em “administração de bases de dados” de que é
que se está a falar?
Tempo
COMMIT
OU
ROLLBACK
Instituto Politécnico da Guarda, ESTG
6
1
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Propriedades das transacções (ACID)
Dois requisitos particularmente importantes
• Atomicidade — todas as operações associadas à transacção
são executadas ou nenhuma o é
os efeitos de uma transacção devem ser
entendidos sem ter em conta as restantes
transacções
• Persistência – os resultados de uma transacção devem
permanecer na base de dados, mesmo que haja
uma falha após a conclusão da transacção.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
7
• Transacções DDL —
Apenas podem conter uma
instrução DDL.
INSERT
TRANS. DDL
INSERT DELETE CREATE TABLE
• Uma transacção termina com:
TRANS. DML
INSERT
Termina transacção
corrente e inicia
nova transacção
UPDATE
Termina transacção
DDL e inicia nova
transacção
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
8
• Uma transacção começa quando é encontrado o primeiro
comando DML (a seguir ao fim da transacção anterior);
Henrique Madeira, DEI-FCTUC, 2001
TRANS. DML
Instituto Politécnico da Guarda, ESTG
Processamento de transacções
(Caso do Oracle)
Podem conter qualquer
número de instruções DML.
(é da responsabilidade do SGBD)
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instruções DML, DDL e transacções
• Transacções DML —
• Atomicidade — todas as operações associadas à transacção são executadas ou nenhuma o é
Henrique Madeira, DEI-FCTUC, 2001
• Isolamento –
(é da responsabilidade do programador)
9
– COMMIT
(a transacção é confirmada);
– ROLLBACK
(a transacção é abortada);
– Comando DDL
(a transacção é confirmada);
– Detecção de ERROS
(a transacção é abortada);
– Saída (EXIT) da sessão SQL
(a transacção é confirmada).
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Savepoints
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
a execução de uma transacção leva a base de
dados de um estado consistente para um
novo estado consistente
Henrique Madeira, DEI-FCTUC, 2001
• Consistência –
• Consistência — garantia de correcção da informação
10
Durante uma transacção
• Permitem guardar todas as alterações efectuadas desde o início da
transacção corrente, ou desde o último Savepoint;
Enquanto as alterações introduzidas durante a transacção corrente não
forem confirmadas (Commit), estas alterações:
• Não terminam a transacção, apenas guardam as alterações efectuadas;
• Úteis em transacções muito longas;
– são visíveis para o dono da transacção;
• Pode-se fazer Rollback para um Savepoint, anulando assim apenas as
alterações introduzidas depois desse Savepoint;
Henrique Madeira, DEI-FCTUC, 2001
• Quando a transacção termina todos os Savepoints são eliminados.
COMMIT
SAVEPOINT S1
SAVEPOINT S2
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
ROLLBACK TO S2
COMMIT
Instituto Politécnico da Guarda, ESTG
11
Henrique Madeira, DEI-FCTUC, 2001
– são invisíveis para as outras transacções (estes vêem o estado após o
último Commit);
• Depois de Rollback To Savepoint a transacção continua activa;
– podem ser anuladas através de Rollback;
– podem ser anuladas parcelarmente através de Rollback para um
Savepoint.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
12
2
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Transacções e transparência para o programa
Atomicidade e consistência de dados: um exemplo
Transferência de 5000 contos entre duas contas bancárias:
O controlo (início/fim) das transacções é explícito e
visível para o programador/utilizar.
1) Débito de 5000 contos da conta origem;
2) Crédito de 5000 contos da conta destino.
O programador é responsável por definir as operações
que devem ser executadas atomicamente através do
mecanismos das transacções.
Instituto Politécnico da Guarda, ESTG
13
Instituto Politécnico da Guarda, ESTG
14
Rollback ao nível do comando SQL
• Se, durante a fase de execução de um comando DML, for
detectado um erro é feito Rollback do comando (é como se o
• A execução bem sucedida de um comando DML
comando nunca tivesse sido iniciado)
—> todo o comando foi executado sem erros e de uma
maneira atómica (e.g., todos os registos seleccionados foram actualizados);
Henrique Madeira, DEI-FCTUC, 2001
• Possíveis erros detectados:
• As alterações efectuadas por um comando só ficam
permanentes quando ocorrer o Commit da transacção.
Instituto Politécnico da Guarda, ESTG
As transacções devem englobar conjuntos de comandos que promovem alterações consistentes de dados
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Execução de comandos DML em SQL e
controlo das transacções
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacção
15
– tentativas de violação de integridade;
– incompatibilidade de dados;
– Esgotamento do espaço disponível para a tabela;
– etc
Henrique Madeira, DEI-FCTUC, 2001
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Crédito Commit
Henrique Madeira, DEI-FCTUC, 2001
Henrique Madeira, DEI-FCTUC, 2001
Commit Débito
• Todas as alterações provocadas pela execução parcial do
comando (até à detecção do erro) são desfeitas.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
16
COMMIT [WORK]
Instruções SQL para controlo de transacções
• Termina explicitamente transacção corrente:
– Torna definitivas as alterações efectuadas durante a transacção;
• COMMIT [WORK]
– Elimina todos os Savepoints efectuados durante a transacção;
Henrique Madeira, DEI-FCTUC, 2001
• ROLLBACK [WORK] TO [SAVEPOINT] <nome do savepoint>
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
17
Henrique Madeira, DEI-FCTUC, 2001
– Liberta os bloqueios da transacção.
• SAVEPOINT <nome do savepoint>
Recordar que:
A transacção também pode terminar de uma maneira implícita (sem o comando
Commit):
– Antes de uma instrução DDL;
– Depois de uma instrução DDL;
– Ao sair da sessão SQL.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
18
3
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
SAVEPOINT <nome do savepoint>
ROLLBACK [WORK] TO [SAVEPOINT] <nome_savepoint>
• ROLLBACK TO <nome do savepoint>
• Guarda as alterações efectuadas até esse ponto da execução
da transacção:
– Desfaz as alterações efectuadas desde o savepoint mencionado;
– Liberta bloqueios estabelecidos depois do savepoint.
– Usado para dividir uma transacção em partes mais pequenas;
– Em caso de engano é possível desfazer as alterações feitas e retomar
o estado a partir de um savepoint estabelecido anteriormente;
• ROLLBACK
– Há um limite para o número de savepoints por transacção (poucos
savepoints, normalmente).
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
19
– Termina a transacção abortando-a;
– Desfaz todas as alterações efectuadas desde o início da transacção;
– Apaga todos os savepoints;
– Liberta os bloqueios da transacção.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Exemplo de utilização de savepoints
Henrique Madeira, DEI-FCTUC, 2001
Henrique Madeira, DEI-FCTUC, 2001
– Se for criado um segundo savepoint com o mesmo nome de um
anterior o savepoint anterior é apagado;
Instituto Politécnico da Guarda, ESTG
20
Consistência da informação
• Dois tipos de acesso à base de dados:
INSERT INTO ELEITOR (BI, NOME, END, N_ELEITOR)
– Operações de leitura (SELECT);
VALUES (4537687, ‘António Silva’, ‘R. Carlos seixas, 29, 1º, Esq’, 2075);
– Operações de escrita (INSERT, UPDATE, DELETE).
SAVEPOINT INSERÇÃO_CONCLUIDA;
UPDATE ELEITOR
SET
NOME = ‘António Dias da Silva’, BI = 4537688;
Henrique Madeira, DEI-FCTUC, 2001
(Anula comando de actualização);
UPDATE ELEITOR
(Comando de actualização revisto);
SET
NOME = ‘António Dias da Silva’, BI = 4537688
WHERE N_ELEITOR = 2075;
COMMIT;
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
21
– As operações de leitura não visualizam dados em processo
de alteração;
– As operações de escrita são feitas de modo consistente, i.e.,
as alterações feitas por um utilizador não entram em conflito
com as alterações que outro utilizador esteja a fazer.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Segmentos de Rollback (Oracle)
Henrique Madeira, DEI-FCTUC, 2001
• Problemas: garantir que
ROLLBACK TO INSERÇÃO_CONCLUIDA
Instituto Politécnico da Guarda, ESTG
22
Segmentos de Rollback: utilização
• Parte da base de dados onde são registadas as acções de uma transacção
que podem vir a ser desfeitas (rolled back) em certas circunstâncias;
• Os segmentos de rollback são usados para:
– Assegurar a consistência de leituras;
• Os segmentos de rollback contêm os valores antigos relativos a todas as
alterações efectuadas a dados durante uma transacção;
Henrique Madeira, DEI-FCTUC, 2001
• Os segmentos de rollback são uma estrutura interna manipulada pelo
SGBD e não pode ser acedida pelos utilizadores nem pelo administrador
da base de dados.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
23
Henrique Madeira, DEI-FCTUC, 2001
– Efectuar o rollback de transacções;
• Cada base de dados contém um ou mais segmentos de rollback;
– Recuperação após falha (em conjunto com outros mecanismos).
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
24
4
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Uso de segmentos de Rollback
Consistência da leitura
Utilizador A
Transacção T1
• Garantir que cada utilizador vê os dados conforme eles
existiam no momento da última confirmação (commit).
Novo valor
do registo
Henrique Madeira, DEI-FCTUC, 2001
Identificação da
alteração e valor
antigo do registo
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
Leituras durante a transacção:
Leituras durante a transacção:
o utilizador é o dono da transacção
outros utilizadores
Utilizador A
Transacção T1
Utilizador B
Transacção T2
Blocos de
dados
Blocos de
dados
SELECT *
FROM ELEITOR
WHERE N_ELEITOR < 2000;
As leituras
durante T1 são
feitas sobre os
dados alterados
Segmentos de
rollback
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Dados não
alterados
SELECT *
FROM ELEITOR
WHERE N_ELEITOR < 2000;
Henrique Madeira, DEI-FCTUC, 2001
UPDATE ELEITOR (Nome)
SET NOME = ‘António Dias Silva’
WHERE N_ELEITOR = 2075;
Instituto Politécnico da Guarda, ESTG
27
“Imagem de
consistência
de leitura”
Dados
antigos
Início da transacção só de leitura
• Para garantir que os resultados obtidos por um dado conjunto de
consultas são consistentes é necessário incluir essas instruções numa
28
Fim da transacção só de leitura
Sessão X
transacção só de leitura.
COMMIT
COMMIT
Sessão A
• Numa transacção só de leitura todas as instruções vêem os dados no
estado (consistente) relativo ao início da transacção (alterações
confirmadas antes do início da transacção). Alterações efectuadas a esses
dados por outros utilizadores não são visíveis durante a transacção só de
leitura.
Henrique Madeira, DEI-FCTUC, 2001
COMMIT
• Estas transacções usam segmentos de rollback de modo semelhante ao
usado na consistência de leitura ao nível da instrução.
Instituto Politécnico da Guarda, ESTG
Instituto Politécnico da Guarda, ESTG
Consistência nas transacções só de leitura
• O SGBD garante, por defeito, que os resultados obtidos por uma
instrução SELECT (consulta) são sempre consistentes.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Segmentos de
rollback
Outros utilizadores vêem os
dados como no início da transacção T1 do utilizador A
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções só de leitura
26
Henrique Madeira, DEI-FCTUC, 2001
25
Segmentos
de rollback
29
COMMIT
Henrique Madeira, DEI-FCTUC, 2001
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
UPDATE ELEITOR (Nome)
SET NOME = ‘António Dias Silva’
WHERE N_ELEITOR = 2075;
• Só depois de confirmadas (pelo commit da transacção
corrente) é que as últimas alterações se tornam visíveis
para todos os utilizadores.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Blocos
de dados
Sessão B
COMMIT
COMMIT
Sessão C
Tempo
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
30
5
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Usar transacções só de leitura
Exemplo de transacção só de leitura
COMMIT;
• Começam pelo comando SET TRANSACTION READ ONLY.
SET TRANSACTION READ ONLY;
• Só admitem consultas (SELECT); comandos DML não são
permitidos.
SELECT N_DOCENT, NOME
FROM DOCENTES;
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
31
SELECT N_ALUNO, NOME
FROM ALUNOS;
Henrique Madeira, DEI-FCTUC, 2001
Henrique Madeira, DEI-FCTUC, 2001
• A transacção só de leitura termina quando for executado um Commit,
Rollback ou uma instrução DDL.
SELECT *
FROM AL_DOC;
COMMIT;
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
Segmentos de rollback: outros aspectos
Segmentos de rollback: commit
• Sempre que começa uma transacção é atribuído um segmento de
rollback a essa transacção.
• Quando uma transacção termina por commit a informação de rollback
é libertada;
32
• Contudo, permanecerá no segmento de rollback de modo a fornecer
uma visão consistente dos dados, no caso de haver consultas (de outros
utilizadores) sobre esses dados que tenham começado antes do commit;
• Durante a transacção, registo correspondente a cada nova alteração é
ligado ao registo correspondente à alteração anterior, de molde a
facilitar as leituras e/ou o rollback.
• Só quando a informação de rollback já não é necessária para garantir
a consistência de leituras pendentes é que é destruída.
Henrique Madeira, DEI-FCTUC, 2001
• Durante a transacção, por cada alteração efectuada é guardado (entre
outras coisas) o valor antigo no segmento de rollback correspondente.
• No caso de haver savepoints, as alterações são relacionadas com o
savepoint correspondente.
• Em caso de necessidade são atribuídos novos segmentos de rollback à
transacção.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
33
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Segmentos de rollback: rollback
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
• Cada segmento de rollback pode tratar mais do que uma transacção.
34
Concorrência e consistência
• Todas as alterações efectuadas durante a transacção são desfeitas
usando a informação de rollback armazenada no(s) segmento(s)
correspondente(s);
Controle da concorrência: objectivos
• Garantir a consistência dos dados;
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
35
• Maximizar a concorrência, i.e., permitir que os
dados sejam
utilizados/manipulados por um
grande número de utilizadores.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
Henrique Madeira, DEI-FCTUC, 2001
• No caso de ROLLBACK TO SAVEPOINT só são desfeitas as alterações
efectuadas depois do savepoint. A utilização dos segmentos de rollback
é semelhante ao caso anterior.
36
6
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Bloqueios (locking)
Problemas inerentes à concorrência
Quando os dados devolvidos por uma instrução
de consulta reflectem alterações introduzidas
e/ou confirmadas na base de dados durante a
execução da instrução de consulta;
• Leituras não reprodutíveis;
Quando, numa mesma transacção, duas
consultas aos mesmos dados mostram
informação diferente, devido a alterações
entretanto confirmadas por outras transacções;
Quando, numa transacção, são lidos dados
ainda não confirmados por outra transacção;
Quando a actualização de um registo numa
transacção é efectuada sobre valores alterados
mas ainda não confirmados por outra
transacção.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
37
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
38
Bloqueios: idéia básica
Necessidade de bloqueios
• Durante uma transacção, todos os registos (tabelas) alterados são
automaticamente bloqueados de modo a impedir que outras transacções
os possam alterar simultaneamente;
• Sempre que um utilizador tenta introduzir alterações na
base de dados (acessos de escrita);
• Nunca são necessários bloqueios quando quando o acesso à
base de dados é de leitura;
• Se uma transacção tentar alterar um objecto previamente bloqueado
por outra transacção ficará suspensa em lista de espera até que esse
objecto seja libertado;
Henrique Madeira, DEI-FCTUC, 2001
– Os acessos de leitura nunca bloqueiam os acessos de escrita;
– Os os acessos de escrita nunca bloqueiam os acessos de leitura;
– Só acessos de escrita podem bloquear outros acessos de escrita.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
• Impede alterações na definição de uma tabela ou de uma
coluna enquanto estiveram a decorrer transacções que
actualizem os dados da tabela ou coluna.
Instituto Politécnico da Guarda, ESTG
39
• No final de uma transacção todos os objectos bloqueados
instruções dessa transacção são libertados.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Henrique Madeira, DEI-FCTUC, 2001
• Perda de actualizações
• Impede a actualização simultânea dos mesmos dados por
dois ou mais utilizadores;
Henrique Madeira, DEI-FCTUC, 2001
• Leituras erróneas;
• Mecanismo utilizado para controlar o acesso concorrente
aos mesmos dados;
Henrique Madeira, DEI-FCTUC, 2001
• Leituras inconsistentes
pelas
Instituto Politécnico da Guarda, ESTG
40
Tipos de bloqueios
Funcionamento dos bloqueios: idéia base
• Bloqueio do dicionário de dados:
Func
UPDATE FUNC
SET
WHERE
SAL = SAL * 1.05
DEPT = 10;
Nome
Num
Dept
SAL
João
Ana
1002
986
10
10
254345
125300
Manuel
Maria
Arnaldo
1023
878
1068
40
30
10
234687
452873
98675
– é controlado automaticamente pelo SGBD.
Henrique Madeira, DEI-FCTUC, 2001
UPDATE FUNC
SET
NOME = ‘Tiago’
WHERE NUM = 1002;
COMMIT;
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
41
• Bloqueio de manipulação de dados:
Henrique Madeira, DEI-FCTUC, 2001
COMMIT
– permite controlar o acesso à definição de objectos na base de dados
(e.g. tabelas através de instruções CREATE TABLE, ALTER TABLE,
DROP TABLE);
– permite controlar o acesso aos dados nas tabelas;
– o SGBD bloqueia implicitamente as tabelas/registos que estão a ser
alterados por um utilizador;
– um utilizador pode adquirir explicitamente um bloqueio numa
tabela da base de dados.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
42
7
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Tipos de bloqueio implícito
Níveis de bloqueio (dados)
(caso do Oracle)
Permite consultas ao recurso bloqueado
(registo ou tabela) mas não permite qualquer
outro acção sobre o recurso;
• Exclusivo (X)
- aplica-se a toda a tabela;
• Exclusivo de
registos (RX)
Vários utilizadores podem bloquear registos
individuais na tabela e efectuar e confirmar
alterações simultaneamente;
Henrique Madeira, DEI-FCTUC, 2001
• Bloqueio de registo - aplica-se apenas a um registo de uma
dada tabela.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Permite o acesso concorrente a uma tabela
impedindo outros utilizadores de bloquearem
toda a tabela para acesso exclusivo;
Instituto Politécnico da Guarda, ESTG
43
Registos bloqueados podem ser consultados
mas só poderão ser alterados quando os
bloqueios forem libertados
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Aquisição implícita de bloqueio
Henrique Madeira, DEI-FCTUC, 2001
• Bloqueio de tabela
Instituto Politécnico da Guarda, ESTG
44
Exemplo de bloqueios implícitos
(Oracle)
Ambos utilizadores adquirem bloqueio exclusivo
de registos (RX) sobre a tabela
Utilizador A
Sem bloqueio
INSERT
X
RX
UPDATE
X
RX
DELETE
X
RX
DDL
—
X
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
45
Func
Bloqueio
exclusivo
do registo (X)
Nome
Num
Dept
SAL
João
Ana
Manuel
Maria
Arnaldo
1002
986
1023
878
1068
10
10
40
30
10
254345
125300
234687
452873
98675
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Bloqueio
exclusivo
do registo (X)
Instituto Politécnico da Guarda, ESTG
46
Aquisição de bloqueios explícitos
Bloqueio explícito
• Um utilizador pode adquirir explicitamente um bloqueio
quando:
Através das instruções SQL
SELECT ... FOR UPDATE OF
Henrique Madeira, DEI-FCTUC, 2001
– Pretende consultar dados em várias tabelas e quer ter a certeza de
obter uma visão consistente dos dados nas tabelas;
– Uma transacção necessita alterar dados com base em outros dados
que não podem ser alterados até a transacção estar concluída.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
UPDATE FUNC
SET SAL = SAL * 1.05
WHERE NUM = 1068;
Instituto Politécnico da Guarda, ESTG
47
Henrique Madeira, DEI-FCTUC, 2001
SELECT
Utilizador B
UPDATE FUNC
SET NOME = ‘Tiago’
WHERE NUM = 986;
Henrique Madeira, DEI-FCTUC, 2001
Nível de bloqueio
Registo Tabela
Henrique Madeira, DEI-FCTUC, 2001
Acção do
utilizador
LOCK TABLE
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
48
8
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
SELECT ... FOR UPDATE OF ...[NOWAIT]
LOCK Table
• Aquisição explícita de um bloqueio
• Utiliza-se quando:
– está previsto actualizar um dado conjunto de registos. O
comando efectua uma consulta para identificar todos os registos
a actualizar e, de seguida, bloqueia os registos seleccionados;
• LOCK TABLE <nome da tabela> IN <tipo de bloqueio>
– é necessário bloquear um registo (ou registos) porque se vai fazer
uma actualização para a qual é necessário garantir que esse
registo não é entretanto alterado por outro utilizador.
• Os tipos de bloqueio são:
– Exclusive
• A opção NOWAIT faz com que o SELECT termine, em vez de esperar, no
caso de um dos registos seleccionados já ter sido bloqueado por outro
utilizador.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
49
UPDATE FUNC
SET NOME = ‘Raul’
WHERE NUM = 1002;
UPDATE FUNC
SET SAL = SAL * 1.05
WHERE NUM = 986;
Instituto Politécnico da Guarda, ESTG
50
• Prevenir os impasses
– Use-se em sistemas em que a probabilidade de surgirem
impasses é muito elevada.
Transacção B
UPDATE FUNC
SET SAL = SAL * 1.05’
WHERE NUM = 1002;
– ...
Como lidar com impasses
• Podem gerar-se situações de impasse quando dois ou mais utilizadores
tentam aceder aos mesmos dados e cada utilizador fica à espera dos
objectos detidos pelo outro utilizador.
UPDATE FUNC
SET NOME = ‘Tiago’
WHERE NUM = 986;
– ...
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Impasses (dead locks)
Transacção A
Henrique Madeira, DEI-FCTUC, 2001
Henrique Madeira, DEI-FCTUC, 2001
– Row Exclusive
• Os bloqueios adquiridos por SELECT ... FOR UPDATE são libertados
quando for efectuado um COMMIT ou um ROLBACK.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
51
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Prevenir impasses
Instituto Politécnico da Guarda, ESTG
52
Detectar impasses
• Bloquear explicitamente, no início da transacção, todos os
recursos que se pretende vir a alterar:
• Grafo de esperas (wait-for graph)
– O SGBD mantém um grafo dirigido que representa as
transacções que estão à espera que recursos bloqueados por
outras transacções sejam libertados.
– Afecta a utilização partilhada dos recursos;
– Pode deixar transacções à espera durante periodos muito longos.
Henrique Madeira, DEI-FCTUC, 2001
• Impor uma ordem para o bloqueio dos recursos e garantir
que todas as transacções seguem essa ordem
– Pode ser de difícil concretização para certas aplicações.
• Há técnicas mais sofisticadas para prevenir impasses
baseadas em timestamps (e.g., wait-die e wound-die )
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Henrique Madeira, DEI-FCTUC, 2001
Não pode continuar porque
o registo do empregado de
NUM = 986 está bloqueado
pela transacção A
– Use-se em sistemas em que a probabilidade de surgirem
impasses é baixa.
Instituto Politécnico da Guarda, ESTG
53
• Este grafo é analisado periodicamente pelo sistema para
detectar situações de impasse.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Transacções e controle de concorrência
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
Não pode continuar porque
o registo do empregado de
NUM = 1002 está bloqueado
pela transacção B
Henrique Madeira, DEI-FCTUC, 2001
• Detectar os impasses e recuperar dessa situação
54
9
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, Escola Superior de Tecnologia e Gestão, 2005/2006
Grafo de esperas (wait-for graph)
Manutenção do grafo de esperas
• Cada nó representa uma transacção;
O SGBD vai mantendo actualizado o estado do grafo:
• Cada arco indica que a transacção representada pelo nó de partida está
à espera que seja libertado um bloqueio pela transacção representada
pelo nó de chegada.
• Sempre que uma transacção é iniciada o sistema acrescenta um
novo nó ao grafo;
• Sempre que uma transacção fica à espera que um recurso seja
libertado por outra transacção o sistema acrescenta um arco entre
os nós em causa;
T4
Henrique Madeira, DEI-FCTUC, 2001
- A transacção T1 está à espera da transacção T2
T3
- A transacção T1 está à espera da transacção T3
- A transacção T2 está à espera da transacção T4
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
55
– Se o recurso foi libertado porque a transacção que o bloqueava terminou, então
o nó correspondente a essa transacção é eliminado e com ele todos os arcos que
chegavam a esse nó;
– Se o recurso foi libertado porque a transacção que o bloqueava fez rollback to
savepoint, então, se isso implicar o fim da espera de uma transacção, o arco
correspondente é retirado do grafo.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
56
Quando deve o sistema examinar o grafo?
Detectar impasses através do grafo de esperas
Existe um impasse sempre que o grafo de esperas contém
um ciclo.
Exemplo:
• Sempre que um recurso é libertado o grafo é actualizado, se
necessário:
• Sempre que o bloqueio de um recurso não pode ser
satisfeito de imediato, se a probabilidade de se gerarem impasses é
elevada;
está à espera de
T4
à
Henrique Madeira, DEI-FCTUC, 2001
es
tá
es
pe
ra
T1
de
está à espera de
T2
T3
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
57
• Periodicamente,
se a probabilidade de se gerarem impasses é reduzida. O
periodo deve estar relacionado com a probabilidade de surgirem impasses e o
número de transacções potencialmente afectadas.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
Henrique Madeira, DEI-FCTUC, 2001
T2
T1
Henrique Madeira, DEI-FCTUC, 2001
Exemplo:
58
O que acontece quando é detectado um impasse?
• É necessário fazer rollback a um comando para quebrar o
impasse.
• Na prática, é normalmente necessário fazer o rollback de
uma transacção de modo a recuperar do impasse;
Henrique Madeira, DEI-FCTUC, 2001
• Critérios na escolha da vítima (minimizar os custos):
– Escolher a transacção que tenha alterado menos dados até então;
– Escolher a transacção que tenha mais dados ainda a manipular até
terminar;
– Escolher a transacção que tenha savepoints de tal modo que o
rollback para um savepoint possa quebrar o impasse e minimize a
perda de dados.
Tópicos Avançados de Bases de Dados, Henrique Madeira, 2005/20056
Instituto Politécnico da Guarda, ESTG
59
Transacções e controle de concorrência
10