CONTROLE DE CONCORRÊNCIA EM BANCO DE DADOS: Estudo

CONTROLE DE CONCORRÊNCIA EM BANCO DE DADOS:
Estudo de Caso – Microsoft SQL Server 2008
GERALDA SILVIA DE VASCONCELOS JARDIM 1
IREMAR NUNES DE LIMA 2
Resumo: Este artigo descreve a importância do mecanismo de controle de
concorrência de transações nos SGBDs relacionais, enfatizando as técnicas de
controle de concorrência implementadas pelo Microsoft SQL Server 2008
Palavras-chave: Banco de Dados,Transação,Controle de Concorrência, Microsoft
SQL Server 2005.
1
2
Pós Graduada em Banco de Dados e Business Intelligence ([email protected]).
Mestre em informática e professor do centro universitário Newton Paiva ([email protected]).
2
1. INTRODUÇÃO
A disseminação global da tecnologia possibilitou a todas as pessoas, em qualquer parte do
mundo, o uso de informações geradas pelos sistemas computacionais multiprocessados,
com isto o paralelismo é inevitável. Em um Sistema Gerenciador de Banco de Dados
(SGBD) multiusuário, os dados são acessados simultaneamente por usuários ou por
programas de aplicação, recuperando e modificando o banco de dados, muitas vezes
alterando a mesma informação, tornando as transações concorrentes. Desta forma torna-se
relevante discutir qual o papel dos SGBDs no controle desta concorrência num banco de
dados.
Segundo ELMASRI (2005, p.400), a concorrência de transações não controlada pode levar
o banco de dados a um estado inconsistente, com diversas anomalias. Visando demonstrar
a real necessidade do controle de concorrência e como ele é feito, este artigo utiliza como
estudo de caso o SGBD Microsoft SQL Server 2008.
O artigo tem como objetivo geral demonstrar o mecanismo de controle de concorrência nos
SGBDs relacionais, enfatizando as técnicas de controle de concorrência implementadas
pelo Microsoft SQL Server 2008. Como objetivos específicos o artigo aborda os conceitos
relacionados a controle de concorrência de transações dos Sistemas Gerenciadores de
Banco de Dados, discute porque o controle de concorrência é necessário em sistemas
multiusuários, aponta as propriedades e técnicas básicas no processamento de transações
acerca de controle de concorrência e restauração e demonstra como os conceitos de
transação são implementados no Microsoft SQL Server 2008.
O restante deste trabalho está organizado da seguinte forma: a seção 2 aborda como a
execução concorrente de transações se comporta em sistemas multiusuários; a seção 3 trata
3
de como o controle de concorrência acontece no Microsoft SQL Server 2008; na seção 4
será apresentada a conclusão deste trabalho.
2. PROCESSAMENTO DE TRANSAÇÕES
O controle de concorrência em um SGBD está relacionado diretamente com os comandos
de acesso a banco de dados feitos por uma transação. Segundo Battisti (2005, p.244),
transação é um conjunto de operações sobre os dados, que deve acontecer como um todo.
Todas as operações devem ser finalizadas com sucesso, ou nenhuma delas deve ser
realizada. Caso uma das operações, contida na transação, venha a falhar, as operações
ainda pendentes devem ser canceladas e as operações já realizadas devem ser revertidas.
Desta forma uma transação sempre estará completa ou não foi realizada. Transações
submetidas por vários usuários podem ser executadas concorrentemente e diversos
problemas podem ocorrer no banco de dados se esta concorrência não for devidamente
controlada.
De acordo com ELMASRI (2005, p.400), dentre os principais problemas, podem ser
destacados os seguintes:
•
Atualização perdida: ocorre quando duas transações que acessam os mesmos itens de
bancos de dados tiveram suas operações intercaladas.
•
Atualização temporária (Leitura Suja): ocorre quando uma transação atualizar um item
de banco de dados e, a seguir, falhar por alguma razão. Este item pode ser acessado por
uma outra transação antes que ele retorne ao seu valor original.
•
Sumário Incorreto: se uma transação aplicar uma função agregada para sumário de um
número de registros enquanto outras transações estiverem atualizando alguns desses
4
registros, pode acontecer da função agregada calcular alguns valores antes de eles
serem atualizados e outros depois de feita a atualização.
Para efeito de restauração, o sistema precisa manter o controle de quando a transação
inicia, termina e de suas efetivações ou interrupções. De acordo com (ELMASRI, 2005,
p.403), durante sua execução, uma transação, percorre os seguintes estados:
•
Estado Ativo: imediatamente após o início de sua execução.
•
Estado de efetivação parcial: quando a transação termina, mas ainda precisa garantir
que nenhuma falha impossibilite a gravação permanente das mudanças.
•
Estado de efetivação: as gravações serão gravadas permanentemente no banco de dados
(commit)
A figura 1 abaixo ilustra os estados de uma transação.
Figura 1 – Diagrama de transição de estados de execução de uma transação.
Fonte: Sistemas de Bancos de Dados. 4ª ed. São Paulo: Person Addison Wesley, 2005, 724 p.
O controle de concorrência e métodos de restauração do SGBD deverão impor algumas
propriedades às transações que são chamadas propriedades ACID (ELMASRI, 2005,
p.404), descritas a seguir:
9 Atomicidade: uma transação é uma unidade atômica de processamento; ou ela será
executada em sua totalidade ou não será de modo nenhum.
9 Consistência: uma transação será preservadora de consistência se sua execução
5
completa fizer o banco de dados passar de um estado consistente para outro.
9 Isolamento: uma transação dever ser executada como se estivesse isolada das
demais. Isto é, a execução de uma transação não deve sofrer interferência de
quaisquer outras transações concorrentes.
9 Durabilidade: as mudanças aplicadas ao banco de dados por uma transação
efetivada devem persistir no banco de dados. Essas mudanças não devem ser
perdidas em razão de uma falha.
Conforme ressaltado por ELMASRI (2005, p.415), para assegurar a propriedade de não
interferência ou isolamento de transações concorrentes, os SGBDs utilizam protocolos. O
protocolo de controle de concorrência mais usado é o bloqueio. O usuário pode indicar o
grau de isolamento desejado entre transações, definindo como será feito o bloqueio dos
dados. O nível de isolamento é especificado pela declaração ISOLATION LEVEL
<isolamento>, onde o valor de <isolamento> pode ser READ UNCOMMITTED (leitura
não eftivada), READ COMMITTED (leitura efetivada), REPEATABLE READ (leitura
repetível) ou SERIALIZABLE (serializável). O nível de isolamento padrão é
SERIALIZABLE, entretanto, alguns sistemas usam como padrão o READ COMMITTED.
O nível SERIALIZABLE não permite violações que causam leitura de sujeira, leitura não
repetível e fantasmas.
Se uma transação for executada em um nível de isolamento mais baixo que o
SERIALIZABLE, podem ocorrer as seguintes violações:
9 Leitura suja ( dirty read ): uma transação T1 pode ler a atualização de uma
transação T2, que ainda não tenha sido committed. Se T2 falhar e for abortada,
então T1 terá lido um valor que não existe e é incorreto.
6
9 Leitura não repetitiva ( nonrepeatable read ): uma transação T1 pode ler um dado
valor de uma tabela. Se outra transação T2 posteriormente atualizar este valor e T1
ler novamente esse valor, T1 verá um novo valor.
9 Fantasmas ( phantoms ): uma transação T1 pode ler um conjunto de linhas de uma
tabela, com base, talvez, em alguma condição especificada na cláusula WHERE da
SQL. Suponha que uma transação T2 insira uma nova linha que também satisfação
à condição da cláusula WHERE empregada em T1, na tabela utilizada por T1. Se
T1 for repetida, então T1 verá um fantasma, uma linha que antes não existia.
Segue abaixo a figura 2 apresentando as possíveis violações baseadas em níveis de
isolamento.
Nível de isolamento
Leitura não efetivada
Leitura efetivada
Leitura Repetitiva
Serializável
Leitura Suja
Sim
Não
Não
Não
Não-Repetível
Sim
Sim
Não
Não
Fantasmas
Sim
Sim
Sim
Não
Figura 2 – Possíveis violações baseadas em Níveis de Isolamento
Fonte: Sistemas de Bancos de Dados. 4ª ed. São Paulo: Person Addison Wesley, 2005, 724 p.
3. CONTROLE DE CONCORRÊNCIA NO SQL SERVER 2008
O SGBD Microsoft SQL Server 2008 usa dois mecanismos para garantir a integridade de
transações e manter a consistência dos bancos de dados quando vários usuários estão
acessando os dados ao mesmo tempo (Microsoft SQL Server 2008 Books Online - July
2009).
O primeiro mecanismo é o bloqueio: um mecanismo usado para sincronizar o acesso de
vários usuários à mesma parte dos dados simultaneamente. Antes que uma transação
7
adquira uma dependência de uma parte dos dados no estado atual, como ler ou modificar
os dados, ela deve se proteger contra os efeitos de outra transação que modifique os
mesmos dados. A transação faz isso, solicitando um bloqueio na parte dos dados. O modo
de bloqueio define o nível de dependência que a transação tem nos dados. Nenhuma
transação pode receber um bloqueio que conflite com o modo de um bloqueio já atribuído
àqueles dados por outra transação. Se uma transação requisitar um modo de bloqueio que
conflite com um bloqueio que já tenha sido atribuído aos mesmos dados, a instância do
mecanismo de banco de dados fará uma pausa na transação requerida até que o primeiro
bloqueio seja liberado.
Quando uma transação modifica uma parte dos dados, ela mantém o bloqueio protegendo a
modificação até o fim da transação. O tempo que uma transação mantém os bloqueios
adquiridos, para proteger as operações de leitura, depende da configuração do nível de
isolamento da transação. Todos os bloqueios mantidos por uma transação são liberados
quando a transação for concluída. Os bloqueios são administrados internamente pelo
gerenciador de bloqueio do SQL Server. Ao processar uma instrução Transact-SQL, o
processador de consulta determina quais recursos devem ser acessados. O processador de
consulta determina que tipos de bloqueio são necessários para proteger cada recurso
baseado no tipo de acesso e no nível configurado de isolamento da transação. O
processador de consulta solicita os bloqueios apropriados ao gerenciador de bloqueio. O
gerenciador de bloqueio concede os bloqueios caso não haja bloqueios conflitantes
mantidos por outras transações (Microsoft SQL Server 2008 Books Online - July 2009).
O SQL Server 2008 tem bloqueio multigranular que permite uma transação bloquear
diferentes tipos de recursos. Para minimizar o custo de bloqueio, ele bloqueia recursos
automaticamente, em um nível apropriado para a tarefa. Bloquear em uma granularidade
8
menor como, por exemplo, linhas, aumenta a concorrência, mas tem uma sobrecarga maior
de memória, devido à exigência de mais bloqueios mantidos, se muitas linhas forem
bloqueadas. Bloqueando em uma granularidade maior, como tabelas, diminui a
concorrência, porque bloquear a tabela inteira restringe o acesso a qualquer parte da tabela
por outras transações. No entanto, tem uma sobrecarga menor de memória, porque menos
bloqueios serão mantidos.
A tabela 1 seguinte mostra os recursos que podem ser bloqueados pelo SQL Server 2008.
Recurso
Descrição
RID
Um identificador de linha usado para bloquear uma única linha dentro de um
heap.
KEY
Um bloqueio de linha dentro de um índice usado para proteger um intervalo de
chaves em transações serializáveis.
PAGE
Uma página de 8 quilobytes (KB) em um banco de dados, como dados ou
páginas de índice.
EXTENT
Um grupo contíguo de oito páginas, como dados ou páginas de índice.
HoBT
Um heap ou árvore B. Um bloqueio protegendo uma árvore B (índice) ou o
heap de páginas de dados que não tem um índice clusterizado.
TABLE
A tabela inteira, inclusive todos os dados e índices.
FILE
Um arquivo de banco de dados.
APPLICATION
Um recurso de aplicativo especificado.
METADATA
Bloqueios de metadados.
ALLOCATION_UNIT
Uma unidade de alocação.
9
DATABASE
O banco de dados inteiro.
Tabela 1 – Modos de Bloqueios utilizados pelo SQL Server 2008
Fonte: Microsoft SQL Server 2008 Books Online, Bloqueio de Granularidade e Hierarquia
A tabela 2 a seguir, mostra os modos de bloqueio que o SQL Server 2008 utiliza.
Modos de
bloqueio
Descrição
Compartilhado
Usado para operações de leitura que não alteram ou atualizam dados, como uma
(S)
instrução SELECT.
Atualização (U)
Usado em recursos que podem ser atualizados. Evita uma forma comum de
deadlock que ocorre quando várias sessões estão lendo, bloqueando e atualizando
recursos.
Exclusivo (X)
Usado para operações da modificação de dados, como INSERT, UPDATE ou
DELETE. Assegura que várias atualizações não sejam realizadas no mesmo recurso
e ao mesmo tempo.
Intencional
Usado para estabelecer uma hierarquia de bloqueio. Os tipos de bloqueios
intencionais são: tentativa compartilhada (IS), exclusivo de tentativa (IX) e
compartilhado com exclusivo de tentativa (SIX).
Esquema
Usado quando uma operação dependente do esquema de uma tabela está
executando. Os tipos de bloqueios de esquema são: modificação de esquema (SchM) e estabilidade de esquema (Sch-S).
Atualização em
Usado quando para copiar dados em massa em uma tabela e o hint TABLOCK foi
massa (BU).
especificado.
Intervalo de chave
Protege o intervalo de leitura de linhas lido por uma consulta ao usar o nível de
10
isolamento da transação serializável. Assegura que outras transações não possam
inserir linhas que se qualifiquem para consultas da transação serializável se as
consultas forem executadas novamente.
Tabela 2 – Modos de Bloqueios utilizados pelo SQL Server 2008
Fonte: Microsoft SQL Server 2008 Books Online, Modos de Bloqueio
A tabela 3 a seguir mostra a compatibilidade dos modos de bloqueio mais comumente
encontrados.
Modo concedido existente
Modo solicitado
IS
S
U
IX SIX X
Tentativa compartilhada (IS)
Sim
Sim Sim Sim Sim Não
Compartilhado (S)
Sim
Sim Sim Não Não Não
Atualização (U)
Sim
Sim Não Não Não Não
Exclusivo da tentativa (IX)
Sim
Não Não Sim Não Não
Compartilhado com exclusivo da tentativa (SIX)
Sim
Não Não Não Não Não
Exclusivo (X)
Não
Não Não Não Não Não
Tabela 3 – Compatibilidade dos modos de bloqueio no SQL Server 2008
Fonte: Microsoft SQL Server 2008 Books Online, Compatibilidade de Bloqueios
O segundo mecanismo é o Controle de Versão de Linha. Ao habilitar um nível de
isolamento baseado em controle de versão de linha, o SQL Server mantém versões de cada
linha modificada. Os aplicativos podem especificar que uma transação use as versões da
linha para exibir dados da forma como eram no início da transação ou consulta ao invés de
11
proteger todas as leituras com bloqueios. Usando o controle de versão de linha, a
possibilidade de uma operação de leitura bloquear outras transações é muito reduzida. Os
usuários podem controlar a implementação do controle de versão de linha habilitando ou
desabilitando as opções do banco de dados. O SQL Server apresenta dois tipos de níveis
de isolamento de transação ao se usar o controle de versão de linha, descritos a seguir:
• Confirmado por leitura: É gerado um instantâneo no nível da instrução.
• Instantâneo: É gerado um instantâneo no nível da transação.
O isolamento confirmado por leitura que usa controle de versão de linha é habilitado pela
configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT como ON.
O isolamento do instantâneo é habilitado pela configuração da opção de banco de dados
ALLOW_SNAPSHOT_ISOLATION como ON. Sempre que uma transação modifica uma
linha, uma imagem da linha antes da modificação é copiada em uma página no
armazenamento de versão. O armazenamento de versão é uma coleção de páginas de dados
localizada no BD tempdb. Se várias transações modificarem uma linha, várias versões da
linha serão vinculadas em uma cadeia de versão. As operações de leitura que usam o
controle de versão de linha recuperam a última versão de cada linha confirmada quando a
transação ou instrução foi iniciada. Para transações que usam um nível de isolamento
baseado no controle de versão de linha, as operações de leitura não solicitam bloqueios
compartilhados nos dados. Isso significa que os leitores que usam controle de versão de
linha não bloqueiam outros leitores ou gravadores que estiverem acessando os mesmos
dados. Da mesma maneira, os gravadores não bloqueiam os leitores. Porém, os gravadores
bloquearão uns aos outros até mesmo durante a execução em níveis de isolamento com
base em controle de versão de linha, pois mais de uma operação de gravação não podem
12
modificar os mesmos dados ao mesmo tempo (Microsoft SQL Server 2008 Books Online July 2009).
Ao alterar a opção de banco de dados READ_COMMITTED_SNAPSHOT ou
ALLOW_SNAPSHOT_ISOLATION para ON, o SQL Server atribuirá um número de
seqüência da transação (XSN) para cada transação que manipular dados usando o controle
de versão. As transações iniciam quando uma instrução BEGIN TRANSACTION é
executada. Porém, o número de seqüência da transação inicia com a primeira operação de
leitura ou gravação depois da instrução BEGIN TRANSACTION. O número de seqüência
da transação é incrementado um por vez sempre que é atribuído (Microsoft SQL Server
2008 Books Online - July 2009).
Cada versão é marcada com o número de seqüência da transação que fez a alteração. As
versões das linhas modificadas são encadeadas usando uma lista de links. O valor da linha
mais recente sempre é armazenado no banco de dados atual e encadeado às linhas com
controle de versão armazenadas no tempdb. As versões da linha são mantidas por tempo
suficiente para atender os requisitos das transações executadas em níveis de isolamento
com base em controle de versão de linha. O SQL Server localiza o número útil mais antigo
de seqüência da transação e exclui periodicamente todas as versões da linha carimbadas
com números de seqüência da transação inferiores ao número útil mais antigo da seqüência
(Microsoft SQL Server 2008 Books Online - July 2009).
Ao iniciar uma transação que usa o nível de isolamento do instantâneo, o SQL Server
registra todas as transações atualmente ativas. Quando a transação de instantâneo lê uma
linha que tem uma cadeia de versão, o SQL Server segue a cadeia e recupera a linha em
que o número de seqüência da transação está mais próxima, mas inferior ao número de
seqüência da transação de instantâneo que está lendo a linha e não está na lista das
13
transações ativas quando a transação de instantâneo é iniciada (Microsoft SQL Server 2008
Books Online - July 2009).
As operações de leitura executadas por uma transação de instantâneo recuperam a última
versão de cada linha confirmada quando a transação de instantâneo foi iniciada. Isso
fornece um instantâneo transacional consistente dos dados, da mesma forma como existiam
no início da transação (Microsoft SQL Server 2008 Books Online - July 2009).
As transações de leitura confirmada que usam controle de versão de linha funcionam de
forma semelhante. A diferença é que a transação de leitura confirmada não usa o seu
próprio número de seqüência da transação ao escolher versões de linha. Cada vez que uma
instrução é iniciada, a transação de leitura confirmada lê o último número de seqüência da
transação emitido para aquela instância. Esse é o número de seqüência da transação usado
para selecionar as versões de linha corretas para aquela instrução. Isso permite que as
transações de leitura confirmada veja um instantâneo dos dados como ele era no início de
cada instrução (Microsoft SQL Server 2008 Books Online - July 2009).
Em uma transação de leitura confirmada que usa o controle de versão de linha, a seleção de
linhas a serem atualizadas é feita usando uma verificação de bloqueio em que um bloqueio
de atualização (U) é feito na linha de dados conforme os valores dos dados são lidos. Isso é
igual à transação confirmada por leitura que não usa controle de versão de linha. Se a linha
de dados não atender os critérios de atualização, o bloqueio de atualização será liberado
naquela linha e a próxima linha será fechada e verificada (Microsoft SQL Server 2008
Books Online - July 2009).
As transações executadas em isolamento de instantâneo usam uma abordagem otimista
para modificação de dados adquirindo bloqueios em dados antes de executar a modificação
somente para impor restrições. Do contrário, os bloqueios não são adquiridos em dados até
14
que os dados sejam modificados. Quando uma linha de dados atende os critérios de
atualização, a transação de instantâneo verifica se a linha de dados não foi modificada por
uma transação simultânea que foi confirmada depois do início da transação de instantâneo.
Se a linha de dados tiver sido modificada fora da transação de instantâneo, ocorrerá um
conflito de atualização e a transação de instantâneo será finalizada. O conflito de
atualização é controlado pelo SQL Server e não há como desabilitar a detecção de conflito
de atualização (Microsoft SQL Server 2008 Books Online - July 2009).
A tabela 4 a seguir, resume as diferenças entre o isolamento de instantâneo e o isolamento
de leitura confirmada usando o controle de versão de linha.
Nível de isolamento de leitura
confirmada usando o controle de versão
Propriedade
de linha
Nível de isolamento do instantâneo
A opção de
READ_COMMITTED_SNAPSHOT
ALLOW_SNAPSHOT_ISOLATION
Como uma
Use o nível padrão de isolamento de
Exige a execução de SET
sessão solicita
leitura confirmada ou execute a instrução
TRANSACTION ISOLATION LEVEL
o tipo
SET TRANSACTION ISOLATION
para especificar o nível de isolamento
específico de
LEVEL para especificar o nível de
SNAPSHOT antes do início da transação.
banco de
dados que
deve ser
definida
como ON
para habilitar
o suporte
exigido.
15
controle de
isolamento READ COMMITTED. Isso
versão de
pode ser feito depois do início da
linha.
transação.
A versão dos
Todos os dados que foram confirmados
Todos os dados que foram confirmados
dados lidos
antes do início de cada instrução.
antes do início de cada transação.
Como as
Faz a reversão de versões de linha a dados
Usa versões de linha para selecionar
atualizações
reais para selecionar as linhas que devem
linhas a serem atualizadas. Tenta adquirir
são
ser atualizadas e usa os bloqueios de
um bloqueio exclusivo na linha de dados
controladas.
atualização nas linhas de dados
reais a ser modificada e, se os dados
selecionadas. Adquire bloqueios
tiverem sido modificados por outra
exclusivos em linhas de dados atuais a
transação, ocorrerá um conflito de
serem modificadas. Nenhuma detecção de
atualização e a transação de instantâneo
conflito de atualização.
será finalizada.
Nenhum.
Suporte integrado. Não pode ser
por
instruções.
Atualização
de detecção
desabilitado.
de conflito.
Tabela 4 – Diferenças entre o isolamento de instantâneo e o isolamento de leitura confirmada
Fonte: Microsoft SQL Server 2008 Books Online
4. CONCLUSÃO.
Transações concorrentes necessitam ser controladas pelos SGBDs de forma que a
execução de uma transação não pode ser afetada por outra. O mecanismo de controle de
concorrência de um SGBD, deve garantir que todas as operações de uma transação sejam
completadas com sucesso para ter seu efeito gravado permanentemente no Bando de
16
Dados, caso contrário, se uma das operações da transação falhar, todas as operações já
executadas deverão ser canceladas. O SGBD Microsoft SQL Server 2008 apresenta dois
mecanismos para garantir a integridade de transações e manter a consistência dos bancos
de dados: o bloqueio e o controle de versão de linhas. Antes de decidir usar o isolamento
com base em controle de versão de linha é necessário verificar o benefício de
simultaneidade de minimizar o bloqueio contra o custo do aumento de recurso necessário
para manter e ler estas versões.
REFERÊNCIAS
BATTISTI, Julio. SQL Server 2005: administração & desenvolvimento: curso completo.
Rio de Janeiro: Axcel Books, 2005, 990 p.
ELMASRI, Rames; NAVATHE, Shamkant B.Sistemas de Banco de dados. 4ª ed. São
Paulo: Person Addison Wesley, 2005, 724 p.
Microsoft SQL Server 2008 Books Online - July 2009