Início / dba / Perguntas / 115260
Accepted
Jonesome Reinstate Monica
Asked: 2015-09-17 08:11:08 +0800 CST

Sinalizador lock_escalation do SQL Server em ALTER TABLE - posso realmente reestruturar sem um bloqueio de tabela?

  • 772

SQL Server 2014 Standard Edition

Na minha situação, tenho uma tabela enorme (mais de 100 milhões de linhas) e preciso:

  • adicionar coluna a ele
  • definir valor padrão nessa coluna

Vejo em https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx que há uma nova opção lock_escalation.

Isso me permite, por exemplo, fazer o seguinte sem um bloqueio de tabela:

por exemplo

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

No entanto, as preocupações são:

a) LOCK_ESCALATION = DISABLE -- para que serve exatamente, aplica-se aqui? (Os documentos do SQL Server são um pouco finos... talvez se aplique apenas a tabelas particionadas?)

b) LOCK_ESCALATION = DISABLE afeta o tamanho da transação? (Parece que ainda criará uma grande transação?)

c) LOCK_ESCALATION = DISABLE será aplicado e evitará um bloqueio de tabela?

d) Existe uma maneira de cortá-lo? (Não consigo imaginar, mas há tanta mágica nova nas versões mais recentes do SQL... devo perguntar!)

sql-server t-sql

1 respostas

  1. Best Answer

    Você diz que a documentação é escassa, mas as respostas estão todas lá.

    Escalação de bloqueio

    Você realmente precisa ler toda a página, mas essencialmente o escalonamento de bloqueio só se aplica a bloqueios 'regulares' (por exemplo, compartilhados, exclusivos). As operações que alteram a estrutura de um objeto usam bloqueios de esquema muito mais restritivos (por exemplo, Sch-M), que impedem todo acesso simultâneo ao objeto que está sendo modificado. Como tal, bloqueios 'comuns' menos restritivos não são necessários. Em última análise, a pergunta que você faz é discutível, porque ALTER TABLEnão aceita bloqueios de nível de linha ou página que podem ser escalados para uma partição ou bloqueio de nível de objeto.

    Em relação ALTER_TABLEa (na seção Observações):

    Bloqueios e ALTER TABLE

    As alterações especificadas em ALTER TABLE são implementadas imediatamente. Se as alterações exigirem modificações nas linhas da tabela, ALTER TABLE atualizará as linhas. ALTER TABLE adquire um bloqueio de modificação de esquema (SCH-M) na tabela para garantir que nenhuma outra conexão faça referência nem mesmo aos metadados da tabela durante a alteração, exceto operações de índice online que exigem um bloqueio SCH-M muito curto no final. Em uma operação ALTER TABLE…SWITCH, o bloqueio é adquirido nas tabelas de origem e destino. As modificações feitas na tabela são registradas e totalmente recuperáveis. Alterações que afetam todas as linhas em tabelas muito grandes, como descartar uma coluna ou, em algumas edições do SQL Server, adicionar uma coluna NOT NULL com um valor padrão, podem levar muito tempo para serem concluídas e gerar muitos registros de log.

    e:

    Adicionando colunas NOT NULL como uma operação online

    A partir do SQL Server 2012 Enterprise Edition, adicionar uma coluna NOT NULL com um valor padrão é uma operação online quando o valor padrão é uma constante de tempo de execução. Isso significa que a operação é concluída quase instantaneamente, independentemente do número de linhas na tabela. Isso ocorre porque as linhas existentes na tabela não são atualizadas durante a operação; em vez disso, o valor padrão é armazenado apenas nos metadados da tabela e o valor é pesquisado conforme necessário nas consultas que acessam essas linhas. Esse comportamento é automático; nenhuma sintaxe adicional é necessária para implementar a operação online além da sintaxe ADD COLUMN.

    Se você estiver usando o Enterprise Edition, a alteração que você propõe pode ser uma alteração instantânea, apenas de metadados (embora quaisquer alterações subsequentes nos dados na nova coluna exija alocações reais, movimentação de dados e registro, é claro).

    Do jeito que está, adicionar essa coluna será uma operação cara, de longa execução, totalmente registrada, transação única e tamanho dos dados. Não há como 'bloquear' essa alteração sem alterar a definição da coluna (uma coluna anulável pode ser adicionada como uma alteração somente de metadados e, em seguida, atualizada em partes).

    • 5

relate perguntas