Correcção do 4º Mini-Projecto
AOBD
2007-2008
Pergunta 1
•
Considere o SGBD SQL Server 2005, embora limitado à utilização dos níveis de isolamento
serializable e snapshot. Considere ainda o seguinte esquema relacional:
SalaEspetaculos(numero_espetaculo, localidade, numero_lugar, lugar_ocupado)
•
A tabela contém cerca de 1,000,000 tuplos, correspondendo a cerca de 500 espetáculos com
2,000 lugares para cada. Considere ainda as seguintes transacções sobre o sistema:
/* T1: 1 transacção por hora */
/* T2: 100 transacções por segundo */
BEGIN TRANSACTION
select avg(lugares_disponíveis) from ( select
numero_espetaculo, count(numero_lugar) as
lugares_disponíveis from SalaEspetaculos
where lugar_ocupado = false group by
numero_espetaculo );
COMMIT
BEGIN TRANSACTION
select numero_lugar from SalaEspetaculos where
lugar_ocupado = false and
numero_espetaculo = ‘ABC123’;
/* código para interagir com utilizador por forma
a ele escolher um lugar (@lugar) */
update SalaEspetaculos set lugar_ocupado = true
where numero_espetaculo = ‘ABC123’ and
numero_lugar = @lugar;
COMMIT
•
Descreva em Português, por palavras suas, o que se pretende obter com a transacção T1
Resposta para pergunta 1
• A transacção T1 vai consultar o conjunto de
todos os concertos e retornar o número
médio de lugares ainda disponíveis.
Pergunta 2.1
• Em termos de controlo de concorrência, qual
o problema existente com a transacção T2?
Resposta para pergunta 2.1
• O problema existente relaciona-se com a
interacção com o utilizador realizada a meio
da transacção, que introduz contenção no
acesso à tabela.
Pergunta 2.2
• Em termos do isolamento e da contenção no
acesso aos dados, este problema manifesta-se
de forma igualmente importante nos dois
níveis de isolamento suportados pelo SGBD
(i.e. serializable e snapshot)? Justifique a sua
resposta.
Resposta para pergunta 2.2
• No nível de isolamento "serializable" os locks são mantidos
até ao fim da transacção. A interacção com o utilizador
pode demorar um periodo arbitrário de tempo, durante o
qual existem recursos que se encontram "locked".
• Com o nível de isolamento "snapshot," cada transacção vai
executar as suas alterações numa cópia local dos dados. O
problema da contenção não se manifesta, mas por outro
lado existe uma forte probabilidade de termos reservas
efectuadas em duplicado (i.e. durante o período de
interacção com o utilizador, outra transacção faz a reserva
para o mesmo lugar).
Pergunta 2.3
• Como se poderia resolver o problema?
Descreva potenciais problemas introduzidos
pela solução proposta.
Resposta para pergunta 2.3
• Uma forma de resolver o problema seria deixar na
transacção T2 apenas o código responsável pela reserva do
lugar, retirando-se a interacção com o utilizador. Contudo,
isto pode trazer o problema de se fazerem reservas para
lugares que já não se encontram disponíveis. A aplicação
teria de verificar, aquando da reserva do lugar, que o
mesmo continua disponível, havendo a possibilidade de
ocorrerem rollbacks.
• Outra solução possível seria atribuir o lugar
automaticamente, em lugar de permitir que seja o
utilizador a fazer a escolha (semelhante à transacção T2'
apresentada no enunciado).
Pergunta 3.1
• Que indice(s) seria(m) apropriado(s) para a
execução eficiente da transação T1? E para a
transacção T2? O(s) mesmo(s) indice(s)
seria(m) vantajoso(s) para a execução das
duas transacções? Justifique a sua resposta.
Resposta para pergunta 3.1
• Um índice clustered na coluna
numero_espetaculo, o qual seria
simultaneamente útil para a querie na transacção
T1 e para a querie/update realizado em T2.
• Outra resposta possível seria um índice clustered
nas colunas (lugar_ocupado,
numero_espetaculo), ainda que esta não seja tão
interessante devido à manutenção acrescida.
Pergunta 3.2
• Considere o nível de isolamento serializable e
assuma que não existem indices que possam
ajudar à execução da transacção T1 (i.e. a query é
sempre executada com base num table scan).
Indique qual dos 3 query hints relativos à
colocação de locks (i.e. NOLOCK, ROWLOCK ou
TABLOCK) faria sentido usar para optimizar a
performance do sistema. Justifique a sua resposta
e indique potenciais problemas com a utilização
do query hint sugerido.
Resposta para pergunta 3.2
• Pode fazer sentido utilizar-se um hint TABLOCK,
eliminando-se assim o custo de manutenção relacionado
com a gestão de locks de uma granularidade menor (e.g.
tuplos ou páginas) ou o “escalamento” dos locks para um
nível de granularidade superior.
• Outra resposta possível seria a utilização do hint NOLOCK,
se assumirmos que a interrogação envolvida na transacção
T1 teria apenas de retornar um valor aproximado (i.e. não é
crítico que T1 faça a leitura de valores ainda não
"commited" para o calculo da média).
Pergunta 3.3
• Considere que a frequência das transacções
associada aos espetáculos localizados em
Lisboa é muito superior à das restantes
regiões do país. Apresente uma proposta para
optimizar a execução de transacções
relativamente aos espetáculos em Lisboa.
Justifique a sua resposta.
Resposta para pergunta 3.3
• Podemos considerar um esquema de particionamento
horizontal, por exemplo através de uma partição na
tabela, colocando os tuplos associados aos
espectáculos em Lisboa numa estrutura de dados
separada (e.g. fisicamente noutro local). Desta forma, a
"tabela" contendo estes tuplos seria mais pequena e as
operações relacionadas especificamente com estes
tuplos iriam ter um custo de processamento menor.
Também o acesso aos restantes tuplos seria
optimizado, visto existir menos contenção relacionada
com as transacções frequentes para os tuplos de
Lisboa.
Pergunta 4
• Considere que a transacção T2 é substítuida pela transacção T2’ que se
apresenta abaixo, eliminando a interacção com o utilizador:
/* T2’: Executam-se 100 transacções por segundo */
BEGIN TRANSACTION
select @lugar = min(numero_lugar) from SalaEspetaculos
where lugar_ocupado = false and numero_espetaculo = ‘ABC123’;
if (@lugar != NULL) {
update SalaEspetaculos set lugar_ocupado = true
where numero_espetaculo = ‘ABC123’ and numero_lugar = @lugar;
} else display(“não existem lugares disponíveis”);
COMMIT
• Descreva vantagens e desvantagens na utilização de cada um dos dois
níveis de isolamento suportados pelo SGBD, aquando da execução da nova
transacção T2’ (i.e. apenas a transacção T2’ é executada). Indique qual dos
níveis de isolamento lhe parece ser o mais indicado.
Resposta para pergunta 4
• A nível de isolamento SERIALIZABLE garante o isolamento
correcto das transacções, à custa de um decréscimo na
concorrência.
• O nível de isolamento SNAPSHOT leva a uma maior
concorrência, à custa da potencial utilização de mais recursos
(i.e. os muitos snapshots para transacções frequentes).
• O nível de isolamento SNAPSHOT pode levar a anomalias
write-skew (ou non-repetable reads/phantoms se
considerarmos o nível de isolamento
READ_COMMITTED_SNAPSHOT do SQL Server 2005)
• Assumindo que nunca podem existir reservas para
espectáculos em duplicado, o nível de isolamento mais
indicado seria o SERIALIZABLE.
Pergunta 5.1
• Supondo que a BD já se encontra
optimizada em termos de locking e de
indices, mas que se chega à conclusão
que a performance ainda não é
satisfatória.
• Indique duas hipóteses para este
problema de performance.
Resposta para pergunta 5.1
• Duas hipóteses possíveis seriam pouca
memória disponível na cache do SGBD ou
planos de execução pouco eficientes (i.e. não
utilizando os índices criados).
Pergunta 5.2
• Para uma das razões apresentadas, descreva
como poderia verificar que é essa
efectivamente a causa do baixo desempenho.
Indique que indicadores de performance iria
observar e que valores esperaria para esses
indicadores.
Resposta para pergunta 5.2
• Para testar a hipótese da cache insuficiente, podia-se
usar o comando SET STATISTICS IO ON por forma a
verificar, para cada querie, o número de acessos
efectuados ao disco, o número de acessos efectuado à
cache, e o número de "pre-fetches". Seria de esperar
que uma percentagem significativa de acessos feitos à
cache (i.e. logical reads).
• Para testar a hipótese do plano de execução pouco
eficiente, pode-se fazer a análise do plano de execução
produzido para a querie. Seria de esperar que os
índices criados sobre as tabelas fossem usados.
Pergunta 5.3
• Suponha que a razão que indicou na alínea
anterior é, de facto a causa do problema de
performance. Como o poderia então resolver?
Resposta para pergunta 5.3
• Assumindo que a memória no SGBD é insuficiente, pode-se
configurar o SGBD por forma a reservar para sí uma
quantidade de memória superior. Pode-se ainda aumentar
a quantidade de memória atribuída ao processamento de
cada querie e, numa solução extrema, forçar a tabela
SalaEspetaculos a permanecer em cache.
• Assumindo que o problema é um plano de execução pouco
eficiente, podem-se fornecer hints na querie por forma a
forçar determinado plano de execução. Actualizar as
estatísticas associadas aos índices também pode ser uma
estratégia a seguir, levando a que o optimizador possa
escolher informadamente o melhor plano de execução.