CONCORRÊNCIA DE TRANSAÇÕES
- Transação é a forma por um grupo, lote, ou uma série de alterações em uma fonte de dados seja todas atualizadas com sucesso.
- Caso uma delas venha a falhar, todos as alterações são desfeitas.
- Para preserver a consistência dos dados envolvidos no lote de atualizações, o SQL Server mantém bloqueios nas linhas que estão sendo atualizadas.
- O período de tempo que estes bloqueios são mantidos é conhecido como escopo de transação (transaction scope).
- Uma forma de garantir a concorrência do banco de dados é garantir que os escopos de transação sejam matidos o menor tempo possível, permitindo o SQL Server liberar os bloqueios que estão sendo segurados e permitir que outros usuários acessem as linhas que foram modificadas.
- As transações são uma forma de garantir as propriedade de atomicidade, consistência, isolamento e durabilidade (ACID) de um banco de dados. (pág. 468)
GERENCIAMENTO DE LOCK DO SQL SERVER
- SQL Server utiliza, um mecanismo de bloqueio dinâmico cooperativo para proteger a consistência dos dados na base de dados.
- Os muitos objetos que o SQL Server pode bloquear estão listadas na Tabela seguinte:
- SQL Server irá travar o número mínimo de recursos necessários para atingir o seu objetivo;
- Isto é conhecido como bloqueio de multi-granular.
- Devido a este esquema, o SQL Server não deve apenas assumir os bloqueios que ele necessita (como um bloqueio em uma linha de uma página), mas também deve anunciar a sua intenção de níveis mais elevados.
- Um exemplo pode ajudar a tornar isso claro.
- Duas transações, T1 e T2, estão cada um tentando modificar diferentes linhas.
- Para modificar uma linha, o SQL Server deve ter bloqueios exclusivos na linha que pretende modificar.
- Se o SQL Server levou apenas o bloqueio exclusivo para T1 em R1, em seguida, T2 pediu um bloqueio em R2, o SQL Server teria que examinar a estrutura da árvore para determinar se essa linha pode ser bloqueado.
- Isso seria muito custoso (processamento), por isso, SQL Server anuncia a sua intenção de níveis mais altos da árvore, colocando bloqueios de intenção acima da linha e navegar na árvore até a raiz.
- Como você pode ver na figura seguinte, ao examinar os Locks, a intenção do SQL Server pode determinar rapidamente que há uma intenção de bloqueio exclusivo sobre a tabela, mas a linha de T1 está em uma parte separada da árvore, de modo que SQL Server podem agora adquirir o Lock que requer T2.
- O SQL Server tem os seguintes bloqueios:
- Os bloqueios de intenção pode ser considerado como um modificador para os outros modos de bloqueio.
- Você colocaria um bloqueio compartilhado (S) em uma linha e ler essa linha, e um bloqueio de intenção compartilhado (IS) seriam colocados ao longo da navegação da raiz da B-Tree.
COMPATIBILIDADES DE LOCK
- Embora alguns tipos de bloqueio impedem de outras transações adquirirem bloqueios no mesmo recurso, muitos bloqueios são compatíveis uns com os outros.
- Uma tabela de compatibilidade de bloqueio abreviado é mostrada na Tabela 10-4.
- Lendo este quadro pode ser confuso, por isso um exemplo vai ajudar.
- Transação T1 atualmente detém um bloqueio de atualização (U) em uma linha.
- Transação T2 concede um bloqueio compartilhado nessa linha.
- Ao examinar a matriz, você pode ver que T2 será concedido o bloqueio compartilhado.
- Com base na discussão anterior sobre bloqueios exclusivos, você pode perguntar por que um bloqueio IX atualmente concedido seria compatível com o pedido de um outro bloqueio IX.
- Isso ocorre porque os bloqueios de intenção nunca são realizadas no nível do recurso real (como uma linha); eles são, pelo menos, a um nível acima na árvore. (Pág. 471)
- SQL Server usa o gerenciamento de bloqueio dinâmico para aumentar a concorrência no banco de dados.
- Por exemplo, se sua transação modifica uma linha em uma tabela, o SQL Server irá fazer um bloqueio de linha para a duração da modificação.
- No entanto, em grandes tabelas em que seriam necessários muitos bloqueios de linha, o SQL Server, ao contrário, terá uma página ou a tabela de bloqueio.
- Esta é dependente de todas as operações que levam bloqueios em uma tabela.
- Se o otimizador de consulta decide que um grande número de linhas serão afetadas, os bloqueios de nível superior podem ser tomadas no início da transação.
- No entanto, o SQL Server também pode escalar dinamicamente os tipos de bloqueio durante a execução.
- Se o número total de bloqueios de linha tomadas por consultas por 100 usuários excede o limite, o SQL Server terá um bloqueio de tabela e, em seguida, irá liberar todos os bloqueios de leitura.
- Isso simplifica o gerenciamento dos bloqueios, mas resulta em diminuição de simultaneidade. (Pág. 472)
NOTE TUNING TIP
Microsoft best practices specify that all tables should have a clustered index created because queries against heaps generally lead to table locks (because all leaf pages have to be scanned for results that match the query predicate) and reduce concurrency. (Pág 472)
NÍVEIS DE ISOLAMENTO DA TRANSAÇÃO
- A American Nations Standards Institute (ANSI) especifica quatro níveis de isolamento de transação, cada um dos quais tem características muito específicas. As características dos níveis de isolamento são as seguintes:
- Dirty read – Uma leitura suja ocorre quando uma transação tem permissão para ler dados de uma linha que foi modificada por outra transação em execução que ainda não foi comitada.
- Non-repeatable read – A leitura não repetitiva ocorre quando, durante o curso de uma transação, uma linha é recuperada duas vezes e os valores dentro da linha diferem entre as leituras.
- Phantom read – A leitura fantasma ocorre quando, no curso de uma operação, duas consultas idênticas são executadas, e a coleção de linhas retornadas pela segundo consulta é diferente da retornada pela primeira.
- Os níveis de isolamento da transação são:
READ UNCOMMITTED – Os dados que não foram efetivados podem ser lidos. Embora um bloqueio exclusive ainda obstrua outro bloqueio exclusívo, as operações de leitura ignoram um bloqueio exclusívo.
READ COMMITTED (Padrão do SQL Server) – Um bloqueio exclusívo obstrui bloqueios compartilhados, assim como bloqueios exclusívos. Os bloqueios compartilhados serão liberados assim que os dados forem lidos.
REPEATABLE READ – Os bloqueios exclusívos obstruem os bloqueios compartilhados e exclusívos. Os bloqueios compartilhados obstruem os bloqueios exclusívos. Os bloqueios compatilhados são mantidos pela duração da transação.
READ SERIALIZABLE – Igual a todas as constraints do nível de isolamento Repeatable Read. Além disso, voce não pode inserir uma nova linha dentro do intervalo do conjunto de chaves (keyset) correntemente bloqueado pela transação. Bloqueios são mantidos pela duração da transação.
SNAPSHOT – Usa o recurso de controle de versão de linha para impeder que bloqueis compartilhados e exclusívos obstruam uns aos outros, enquanto mantém a consistência dos dados. Uma operação de leitura recupera dados da versão da linha antes de iniciar uma operação de modificação de dados.
EXEMPLOS DOS NÍVEIS DE ISOLAMENTO
–Verifica nivel de isolamento corrente
DBCC USEROPTIONS
GO
--Criando um banco de dados
CREATE DATABASE IL
GO
USE IL
GO
--Criacao da tabela 1
CREATE TABLE tbIsolationLevel
(
Id INT IDENTITY,
Col1 varchar(10),
Col2 varchar(10),
Col3 varchar(10)
)
GO
--Inserindo dados na tabela 1
INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('AAAAA','BBBBB','CCCCC')
GO 5
--Criacao da tabela 2
CREATE TABLE tbDeadlock ( id INT)
GO
--Inserindo dados de tabela 2
INSERT INTO tbDeadlock(ID) values (1),(2)
GO
READ COMMITED
/********* RODAR NA CONEXÃO 1 *********/ BEGIN TRAN UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1 WAITFOR DELAY '00:00:10' ROLLBACK TRAN /********* RODAR NA CONEXÃO 2 *********/
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM tbIsolationLevel
READ UNCOMMITED
/********* RODAR NA CONEXÃO 1 *********/ BEGIN TRAN UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1 WAITFOR DELAY '00:00:10' ROLLBACK TRAN /********* RODAR NA CONEXÃO 2 *********/ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM tbIsolationLevel -- Usando o hint NOLOCK SELECT * FROM tbIsolationLevel with (nolock)
REPETEATABLE READ
/********* RODAR NA CONEXÃO 1 *********/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM tbIsolationLevel
WAITFOR DELAY '00:00:10'
SELECT * FROM tbIsolationLevel
ROLLBACK TRAN
/********* RODAR NA CONEXÃO 2 *********/
UPDATE tbIsolationLevel set Col1 = 'DDDDD' where id = 1
--select * from tbIsolationLevel
INSERT INTO tbIsolationLevel(Col1,Col2,Col3)
VALUES ('DDDDD','EEEEE','FFFFF')
SNAPSHOT ISOLATION LEVEL
- Em Snapshot Isolation, as versões de linha atualizados para cada transação são mantidos em TempDB. Uma vez que a transação foi iniciada, ignora todas as linhas mais recentes inseridos ou atualizados na tabela.
USE AdventureWorks2012 GO --Aciona permissão de uso do SnapShot no banco
/ ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON GO SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE HumanResources.Shift SET ModifiedDate = GETDATE() GO -- Session 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT ModifiedDate FROM HumanResources.Shift GO -- Session 1 COMMIT -- Session 2 SELECT ModifiedDate FROM HumanResources.Shift GO --AINDA OS DADOS NAO FORAM ALTERADOS -- Session 2 COMMIT SELECT ModifiedDate FROM HumanResources.Shift GO
SNAPSHOT (INSTANTÂNEOS)
- O recurso Database SnapShot foi introduzido no SQL Server 2005 para oferecer aos usuários um método para criar cópias somente leitura de dados rapidamente.
- Esse recurso somente está disponível no SQL Server Enterprise.
- O DataBase SnapShot não é compatível com FILE STREAM.
RECURSO DE CRIAÇÃO DE SNAPSHOTS
- A criação de um snapshot de banco de dados é muito parecida com a criação de qualquer banco de dados.
- Para criar um snapshot de banco de dados, você usa o comando CREATE DATABASE com a cláusula AS SNAPSHOT OF.
- Como um snapshot é somente um cópia de leitura de dados de um banco de dados em um ponto no tempo, você não especifica um log de transação;
REQUISITOS PARA CRIAÇÃO DE SNAPSHOT
- Voce deve incluir uma entrada para cada arquivo de dados especificado no banco de dados de origem;
- O nome lógico de cada arquivo deve corresponder exatamente ao nome no banco de dados de origem;
RESTRIÇÕES PARA UM SNAPSHOT
- Voce não pode fazer backup, restaurar ou remover um snapshot de banco de dados;
- O snapshot de banco de dados deve existir na mesma instância do banco de dados de origem;
- Índices full text não são suportados;
- FILESTREAM não é suportado e quaisquer dados FILESTREAM serão inacessíveis por meio do snapshot de banco de dados;
- Voce não pode criar um snapshot de banco de dados em um banco de dados de sistema;
- Voce não pode eliminar, restaurar ou remover um banco de dados de origem que tenha um snapshot de banco de dados;
- Você não pode fazer referencia a grupos de arquivos que estejam off-line, extintos ou sem restauração;
- Para que um aplicativo acessa o snapshot é da mesma forma que acessasse um banco de dados normalmente;
- No momento da criação do snapshot, o banco de dados não contém dados. Quando uma instrução SELECT é executada, o SQL Server usa o banco de dados de origem para recuperar dados que não mudaram desde que você criou o snapshot;
REVERTENDO DADOS DE UM SNAPSHOT
- É possível fazer o retorno dos dados até o momento da criação do snapshot.
- Em casos extremos, você pode usar o snapshot para retornar todo o conteúdo do banco de dados de origem para o estado do snapshot.
- Por exemplo, se você precisar descartar todas as alterações ocorrida dentro do banco de dados desde que o snapshot de banco de dados foi criado;
- Uma reversão de banco de dados é uma categoria especial de restauração de dados que pode ser executada quando você tiver criado um snapshot;
- Você pode reverter apenas uma linha ou parte do banco de dados, podendo usar os comandos INSERT, UPDATE, DELETE ou MERGE, ou também reverter um banco de dados por completo.
- Quando você usa o snapshot para reverter o banco de dados inteiro, o banco de dados de origem volta exatamente para como estava no momento em que o snapshot foi criado.
- Todas as transações executadas no banco de dados são perdidas.
- Quando você reverte um banco de dados de origem existem várias restrições:
- Pode existir apenas um snapshot de banco de dados para o banco de dados origem;
- Catálogos full text no banco de dados de origem devem ser eliminados e então recriados, depois que a reversão terminar;
- Como o log de transação é reconstruído, o encadeamento de logs de transação é desfeito;
- O banco de dados de origem e o snapshot de banco de dados ficam offline durante o processo de reversão;
- O banco de dados de origem não pode ser ativado para FileStream;
EXEMPLO DE CRIAÇÃO DE SNAPSHOT
USE master GO CREATE DATABASE AdventureWorks2012Snap on (NAME=N'AdventureWorks2012_Data', FILENAME=N'c:\temp\AdventureWorks2012.ds') AS SNAPSHOT OF AdventureWorks2012 GO use AdventureWorks2012Snap select * from person.Address use master go --Restaurando o banco de dados do snapshot RESTORE DATABASE AdventureWorks2012 FROM DATABASE_SNAPSHOT = 'AdventureWorks2012Snap' SELECT * FROM AdventureWorks2012.sys.database_files SELECT * FROM AdventureWorks2012Snap.sys.database_files SELECT * FROM master.sys.databases
REFERÊNCIAS
BIBLIOGRAFIA BÁSICA
- HOTEK, M. MCTS Self-Paced Training Kit (Exam 70-432) Microsoft SQL Server 2008 – Implementation and Maintenance. EUA: Microsoft Press, 2009.
- STANEK, William R. Microsoft SQL Server 2008 – Guia de Bolso do Administrador. Porto Alegre: Bookman, 2010.
- HOTEK, M. Microsoft SQL Server 2008 – Passo a Passo. Porto Alegre: Bookman, 2010.
Video 01
Video 02
