|
|
Publicado em 20/07/2016 12:37:53
|
|
|
Visão geral das eclusas
1. Por que introduzir fechaduras
Quando múltiplos usuários realizam operações simultâneas no banco de dados ao mesmo tempo, ocorrem as seguintes inconsistências nos dados:
Atualizações ausentes
Dois usuários, A e B, leem os mesmos dados e os modificam, e o resultado da modificação de um usuário destrói o resultado da outra modificação, como o sistema de reserva de bilhetes
Leitura suja
O Usuário A modifica os dados, e então o Usuário B lê os dados, mas o usuário A cancela a modificação dos dados por algum motivo, e os dados retornam ao seu valor original
Não leia repetidamente
O Usuário A lê os dados, e então o Usuário B lê os dados e os modifica
O principal método de controle de concorrência é o bloqueio, que consiste em proibir usuários de realizarem certas operações por um período de tempo para evitar inconsistências nos dados
2. Classificação das eclusas
Existem duas divisões em categorias de eclusas:
1 . Do ponto de vista do sistema de banco de dados: ele é dividido em bloqueios exclusivos (ou seja, bloqueios exclusivos), bloqueios compartilhados e bloqueios de atualização
O MS - SQL Server utiliza os seguintes padrões de bloqueio de recursos.
Descrição do Modo de Trava
Share(s) é usado para operações que não alteram ou atualizam dados (operações somente leitura), como instruções SELECT.
Atualização (U) é usada em recursos atualizáveis. Previne formas comuns de bloqueios quando múltiplas sessões são lidas, travadas e possivelmente uma atualização de recursos que pode ocorrer.
Exclusivo (X) é usado para operações de modificação de dados, como INSERT, UPDATE ou DELETE. Certifique-se de que múltiplas atualizações não sejam realizadas no mesmo recurso ao mesmo tempo.
Bloqueios de Intenção são usados para estabelecer uma hierarquia de fechaduras. Os tipos de bloqueios de intenção são: Intenção Compartilhada (IS), Intenção Exclusiva (IX) e Intenção Exclusiva (SEIS).
Bloqueios de esquema são usados ao realizar operações que dependem do esquema da tabela. Os tipos de bloqueios de esquema são: modificação de esquema (Sch -M) e estabilidade de esquema (Sch -S).
Atualizações em massa (BUs) são usadas quando grandes volumes de dados são copiados para uma tabela e uma dica TABLOCK é especificada.
Fechaduras compartilhadas
Um bloqueio compartilhado(s) permite que transações simultâneas leiam (SELECIONEM) um recurso. Quando existe um bloqueio compartilhado (S) em um recurso, nenhuma outra transação pode modificar os dados. Libere o bloqueio compartilhado (S) no recurso assim que os dados forem lidos, a menos que o nível de isolamento da transação esteja configurado para repetível ou superior, ou que o bloqueio compartilhado (S) seja mantido com uma dica de bloqueio durante toda a vida útil da transação.
Atualizar bloqueio
As fechaduras Update (U) evitam deadlocks em sua forma usual. Um padrão típico de atualização consiste em uma transação que lê um registro, recebe um bloqueio compartilhado (S) para um recurso (página ou linha) e então modifica uma linha, o que exige que o bloqueio seja convertido em um bloqueio exclusivo (X). Se duas transações adquirirem um lock em modo compartilhado em um recurso e tentarem atualizar os dados ao mesmo tempo, uma transação tenta converter o lock em um lock exclusivo (X). A transição do modo compartilhado para o bloqueio exclusivo deve esperar um tempo porque o bloqueio exclusivo de uma transação não é compatível com o bloqueio de modo compartilhado de outra transação; Ocorre uma espera por eclusas. A segunda transação tenta obter um bloqueio exclusivo (X) para uma atualização. Ocorre um deadlock porque ambas as transações são convertidas em locks exclusivos (X), e cada transação espera que a outra libere o lock em modo compartilhado.
Para evitar esse possível problema de bloqueio, use um bloqueio atualizado (U). Apenas uma transação por vez pode obter um bloqueio (U) atualizado para um recurso. Se a transação modificar o recurso, o lock de atualização (U) é convertido em um lock exclusivo (X). Caso contrário, a fechadura é convertida em uma fechadura compartilhada.
Fechaduras exclusivas
Locks exclusivos (X) impedem transações concorrentes de acessar recursos. Outras transações não podem ler ou modificar os dados bloqueados pelo bloqueio exclusivo (X).
Trava de intenção
Um bloqueio de intenção indica que o SQL Server precisa adquirir um bloqueio compartilhado (S) ou um bloqueio exclusivo (X) em alguns dos recursos subjacentes na hierarquia. Por exemplo, um bloqueio de intenção de compartilhamento colocado no nível da tabela indica que a transação pretende colocar um bloqueio de compartilhamento em uma página ou linha da tabela. Definir um bloqueio de intenção no nível da tabela impede que outra transação adquira posteriormente um lock exclusivo (X) na tabela que contém essa página. Bloqueios de intenção podem melhorar o desempenho porque o SQL Server só verifica o bloqueio de intenção no nível da tabela para determinar se uma transação pode adquirir um bloqueio com segurança naquela tabela. Em vez de verificar os bloqueios em cada linha ou página da tabela para determinar se uma transação pode travar toda a tabela.
Bloqueios de intenção incluem Compartilhamento de Intenção (IS), Intenção Exclusiva (IX) e Compartilhamento Exclusivo de Intenção (SEIS).
Descrição do Modo de Trava
O Compartilhamento de Intenção (IS) indica que a intenção da transação é alguns, não todos, os recursos subjacentes na hierarquia de leitura, ao colocar S-locks em cada recurso.
A Intenção Exclusiva (IX) indica que a intenção da transação é modificar alguns, mas não todos, os recursos subjacentes na hierarquia colocando um X-lock em cada recurso. IX é um superconjunto do IS.
O compartilhamento exclusivo com intenção (SEIS) indica que a intenção da transação é ler todos os recursos subjacentes na hierarquia e modificar alguns, mas não todos, os recursos subjacentes ao colocar bloqueios IX em cada recurso. Permitir bloqueios IS concorrentes em recursos de nível superior. Por exemplo, o bloqueio SIX de uma tabela coloca um bloqueio SIX na tabela (permitindo bloqueios IS concorrentes) e um bloqueio IX na página atualmente modificada (um bloqueio X na linha modificada). Enquanto cada recurso pode ter apenas um bloqueio SIX por um período de tempo para impedir que outras transações atualizem o recurso, outras transações podem ler os recursos subjacentes na hierarquia adquirindo bloqueios IS em nível de tabela.
Bloqueio exclusivo: Somente o programa que realiza a operação de bloqueio pode usá-lo, e quaisquer outras operações nele não serão aceitas. Quando você executa um comando de atualização de dados, o SQL Server usa automaticamente um bloqueio exclusivo. Quando existem outros bloqueios em um objeto, você não pode adicionar um bloqueio exclusivo a ele.
Bloqueio compartilhado: O recurso bloqueado pelo bloqueio compartilhado pode ser lido por outros usuários, mas outros usuários não podem modificá-lo.
Bloqueio de atualização: Quando o SQL Server está pronto para atualizar dados, ele primeiro trava o objeto de dados para que os dados não possam ser modificados, mas sim lidos. Quando o SQL Server determina que deseja atualizar os dados, ele automaticamente substituirá o bloqueio de atualização por um bloqueio exclusivo, e não pode adicionar um bloqueio de atualização a ele quando outros bloqueios existirem no objeto.
2 . Do ponto de vista do programador: ele é dividido em bloqueio otimista e bloqueio pessimista.
Fechadura Optimism: Depende inteiramente do banco de dados para gerenciar o funcionamento da fechadura.
Bloqueios pessimistas: Programadores gerenciam o tratamento de locks em dados ou objetos.
O MS-SQLSERVER usa bloqueios para implementar controle pessimista de concorrência entre múltiplos usuários que realizam modificações no banco de dados ao mesmo tempo
3. O tamanho das partículas da fechadura
A granularidade do bloqueio é o tamanho do alvo bloqueado, a granularidade de bloqueio pequena é de alta concorrência, mas a sobrecarga é grande, e a granularidade de bloqueio grande é de baixa concorrência, mas a sobrecarga é pequena
O SQL Server suporta granularidade de bloqueio para linhas, páginas, chaves, intervalos de chaves, índices, tabelas ou bancos de dados
Descrição do recurso
Identificador de linha RID. Costumava travar uma linha em uma tabela individualmente.
Chave de chave no índice. Usado para proteger a faixa de chaves em transações serializáveis.
8 kilobytes (KB) de páginas de dados ou páginas de índice.
Disco Estendido Um conjunto de oito páginas de dados adjacentes ou páginas de índice.
Tabela A tabela completa incluindo todos os dados e índices.
Banco de dados do banco de dados.
4. O tempo de bloqueio
O tempo que uma eclusa permanece é o tempo necessário para proteger o recurso no nível solicitado.
O tempo de espera do lock compartilhado usado para proteger operações de leitura depende do nível de isolamento da transação. Com o nível padrão de isolamento de transação READ COMMITTED, o bloqueio compartilhado é controlado apenas durante a duração da página de leitura. Em uma varredura, a fechadura não é liberada até que a fechadura seja adquirida na página seguinte dentro da varredura. Se você especificar um prompt HOLDLOCK ou definir o nível de isolamento da transação para LEITURA REPETIBLE ou SERIALIZÁVEL, o bloqueio não é liberado até o término da transação.
Dependendo da opção de concorrência definida para o cursor, o cursor pode adquirir um travamento de rolagem no modo compartilhado para proteger a extração. Quando é necessário um travamento de rolagem, ele só é liberado na próxima extração ou fechamento do cursor, o que ocorrer primeiro. No entanto, se você especificar um HOLDLOCK, o scroll lock só é liberado ao final da transação.
O bloqueio exclusivo usado para proteger a atualização só será liberado ao final da transação.
Se uma conexão tentar adquirir um bloqueio que conflita com um bloqueio controlado por outra conexão, a conexão que tenta adquirir o bloqueio será bloqueada até:
O bloqueio conflitante é liberado e a conexão adquire o bloqueio solicitado.
O tempo de extinção da conexão expirou. Não há intervalo de tempo por padrão, mas alguns aplicativos definem intervalos de tempo para evitar a espera indefinida
Cinco customizações de travas no SQL Server
1 Lidar com deadlocks e definir prioridades de deadlock
Deadlock é a espera interminável causada por múltiplos usuários solicitando bloqueios diferentes, porque o candidato tem parte do direito de bloqueio e espera pelo bloqueio parcial pertencente a outros usuários
Você pode usar o DEADLOCK_PRIORITY SET para controlar como a sessão reage em caso de impasse (deadlock). Se ambos os processos bloquearem os dados e cada processo não puder liberar seu próprio bloqueio até que o outro processo libere seu próprio bloqueio, ocorre uma situação de deadlock.
2 Gerencie os tempos e defina a duração dos bloqueios.
@@LOCK_TIMEOUT retorna a configuração atual de tempo limite de trava para a sessão atual em milissegundos
A configuração SET LOCK_TIMEOUT permite que a aplicação defina o tempo máximo que a instrução espera para bloquear o recurso. Quando o tempo de espera da instrução é maior que a configuração de LOCK_TIMEOUT, o sistema cancela automaticamente a instrução bloqueante e retorna à aplicação uma mensagem de erro 1222 informando que o período de tempo limite para a solicitação de bloqueio foi ultrapassado
exemplo
No exemplo a seguir, o período de tempo de bloqueio é definido para 1.800 milissegundos.
CONJUNTO LOCK_TIMEOUT1800
3) Definir o nível de isolamento da transação.
4) Usar dicas de bloqueio em nível de tabela para as instruções SELECT, INSERT, UPDATE e DELETE.
5) Configurar a granularidade de travamento do índice
Você pode usar sp_indexoption procedimentos armazenados do sistema para definir a granularidade do lock para indexação
6. Visualize as informações da fechadura
1 Realizar o EXEC SP_LOCK reportar informações sobre a fechadura
2 Pressione Ctrl + 2 no analisador de consultas para ver as informações do bloqueio
7. Precauções para uso
Como evitar impasses
1. Ao usar transações, tente encurtar o processo lógico de processamento das transações e envie ou reverta as transações antecipadamente.
2 Definir o parâmetro de tempo de bloqueio para uma faixa razoável, como: 3 minutos - 10 minutos; Após esse tempo, a operação será automaticamente abandonada para evitar que o processo trave;
3. Otimizar o programa, verificar e evitar o fenômeno do impasse;
4. Teste cuidadosamente todos os scripts e SPs antes da versão exata.
5 Todos os SPs devem ter tratamento de erros (via @error)
6 Não modifique o nível padrão das transações do SQL SERVER. Travamento forçado não é recomendado
Resolver o problema Como bloquear um banco de dados de tabela de linhas
8. Várias perguntas sobre fechaduras
1 Como travar uma fileira de uma tabela
DEFINIR NÍVEL DE ISOLAMENTO DE TRANSAÇÕES RE-UNCOMMITTED
SELECT *FROM table ROWLOCKWHEREWHERE id = 1
2 Bloquear uma tabela no banco de dados
SELECT *FROM tabela COM( HOLDLOCK )
Declaração do bloqueio:
sybase:
Conjunto de tabela atualizar col1 = col1 onde 1= 0 ;
MSSQL:
selecione col1 da tabela (tablockx) onde 1= 0 ;
oracle:
TABELA DE BLOQUEIO EM MODO EXCLUSIVO ;
Depois que o bloqueio é bloqueado, ninguém mais pode operá-lo até que o usuário bloqueado o desbloqueie, e ele é desbloqueado com commit ou rollback
Alguns exemplos ajudam você a aprofundar sua impressão
Mesa de preparação1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
A3 B3 C3
1) Fechadura exclusiva
Criar duas novas conexões
Execute a seguinte instrução na primeira conexão
Início de tran
Tabela de atualização1
conjunto A= ' aa '
onde B= ' b2 '
espera atraso' 00:00:30' --espera 30 segundos
commit tran
Execute a seguinte instrução na segunda conexão
Início de tran
select *da tabela1
onde B= ' b2 '
commit tran
Se as duas instruções acima forem executadas ao mesmo tempo, a consulta select deve esperar a atualização ser executada, ou seja, aguardar 30 segundos
2) Fechadura compartilhada
Execute a seguinte instrução na primeira conexão
Início de tran
select *from table1 holdlock - O holdlock é artificialmente adicionado ao lock.
onde B= ' b2 '
espera atraso' 00:00:30' --espera 30 segundos
commit tran
Execute a seguinte instrução na segunda conexão
Início de tran
selecione A,C da tabela 1
onde B= ' b2 '
Tabela de atualização1
conjunto A= ' aa '
onde B= ' b2 '
commit tran
Se as duas instruções acima forem executadas ao mesmo tempo, a consulta select na segunda conexão pode ser executada
A atualização deve esperar a primeira transação para liberar o bloqueio compartilhado e convertê-lo em um bloqueio exclusivo antes de poder ser executado, ou seja, esperar 30 segundos
3) Impasse
Adicionada tabela2(D,E)
D E
d1 e1
d2 e2
Execute a seguinte instrução na primeira conexão
Início de tran
Tabela de atualização1
conjunto A= ' aa '
onde B= ' b2 '
Espere atraso' 00:00:30'
Tabela de atualização2
conjunto D= ' d5'
onde E= ' e1 '
commit tran
Execute a seguinte instrução na segunda conexão
Início de tran
Tabela de atualização2
conjunto D= ' d5'
onde E= ' e1 '
espera atraso' 00:00:10'
Tabela de atualização1
conjunto A= ' aa '
onde B= ' b2 '
commit tran
Ao mesmo tempo, o sistema detecta o deadlock e aborta o processo
Para acrescentar:
Dicas de bloqueio em nível de tabela suportadas pelo SQL Server 2000
O HOLDLOCK mantém o bloqueio compartilhado até que toda a transação seja concluída e deve ser liberado assim que o objeto travado não for necessário, igual ao nível de isolamento da transação SERIALIZÁVEL
A instrução NOLOCK é executada sem emitir um bloqueio compartilhado, permitindo leituras sujas, que é igual ao nível de isolamento da transação READ UNCOMMITTED
O PAGLOCK usa múltiplos bloqueios de página onde um bloqueio de tabela é usado
O READPAST permite que o SQL Server pule linhas bloqueadas e execute transações, e para níveis de isolamento de transações READ UNCOMMITTED, pula apenas bloqueios RID, não travamentos de página, zona e tabela
O ROWLOCK impõe o uso de rowlocks
O TABLOCKX impõe o uso de um lock exclusivo em nível de tabela, que impede que qualquer outra transação utilize a tabela durante a transação
O UPLOCK força o uso de atualizações ao ler uma tabela sem um bloqueio compartilhado
Bloqueio de Aplicativo:
Um bloqueio de aplicação é um bloqueio gerado pelo código do cliente, não um bloqueio gerado pelo próprio SQL Server
Dois processos para lidar com bloqueios de aplicação
sp_getapplock Recursos da aplicação Lock
sp_releaseapplock Desbloquear os recursos da aplicação
Nota: A diferença entre bloquear uma tabela em um banco de dados
SELECT *FROM tabela WITH( HOLDLOCK ) Outras transações podem ler a tabela, mas não podem atualizar e excluir
SELECT *FROM tabela WITH(TABLOCKX) Outras transações não conseguem ler, atualizar e excluir a tabela
|
Anterior:Não havia ponto final de escuta em http://localhost:111/xxx.svc que c...Próximo:Bloqueios SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
