none
Lock - SQL2008 RRS feed

  • Pergunta

  • Boa noite,

    Gostaria de um apoio de vocês,pois tive uma procedure relativamente simples que contem apenas um select,usando (NOLOCK) e a equipe de banco de dados da empresa informou que esta procedure gerou lock no banco,gerando indisponibilidade da aplicação na empresa.Dúvdas:

    - Mesmo usando o (NOLOCK) na procedure,poderia gerar um lock no banco?

    - Considerando que tenho uma tabela "populada" e em produção,é aconselhável criar indices para esta tabela?

    - Quais são os principais motivos para gerar lock?

    Desde já agradeço o apoio,

    Abraço,


    Eduardo

    quarta-feira, 27 de agosto de 2014 00:00

Respostas

  • - Mesmo usando o (NOLOCK) na procedure,poderia gerar um lock no banco?

    • SIM, o uso da HINT NOLOCK no SELECT faz com que esse SELECT possa ler dados que não foram COMMITADOS ainda, mas o próprio SELECT irá solicitar LOCKS, compartilhados por exemplo, para sua execução. O melhor a se fazer nesse caso é listar quais LOCKS foram solicitados e concedidos para ver o que aconteceu pra gerar a informação dos DBAS.

    - Considerando que tenho uma tabela "populada" e em produção,é aconselhável criar indices para esta tabela?

    • SIM, indices são necessários em todos os ambientes, principalmente em PRODUÇÃO para poder melhorar o plano de execução das consultas, é claro que é preciso analisar cada caso para se criar somente os indices necessários.

    - Quais são os principais motivos para gerar lock?

    • O SQL Server, como outros SGDBs, usam LOCKs a todo momento para poder garantir a integridade das transações e dados, ACID.
    • Existem dois tipos de LOCKS, pessimistas e otimistas, isso tem relação ao nivel de isolamento usado.
    • Existem também dois mecanismos, um que afeta os dados em memória (consistencia lógica) aplicando locks de linha, pagina ou tabela, e outro que afeta as operações fisicas (consistencia fisica) e é usado internamente como Latches e Spinlocks.

    Em resumo, quando se fala que um TSQL gerou um Lock, isso é normal, mas é preciso ver qual tipo de lock foi gerado e qual consequencia foi gerada, exemplo: bloqueios por um tempo muito longo e/ou deadlocks.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.



    quarta-feira, 27 de agosto de 2014 22:15
  • Outra dúvida: Locks podem ser gerados por questões de infraestrutura,por exemplo,memória de servidor,rede,processamento de servidor,etc?

    Não serão GERADOS por questões de infraestrutura, mas a quantidade de memória disponivel pode afetar mais rapido ou menos rapido um mecanismo interno que se chama Lock Escalation, que faz vários Locks menores serem transformados em um Lock maior somente. E isso pode gerar bloqueios indesejados se não monitorado. E a capacidade de processamento, pode influenciar no tempo de liberação dos Locks.

    Sobre a SP vs Indice, a questão não é tanto em relação ao código que a SP possue, e sim ao plano de execução que está sendo gerado para ela.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    quinta-feira, 28 de agosto de 2014 12:18

Todas as Respostas

  • Eduardo,

    - Mesmo usando o (NOLOCK) na procedure,poderia gerar um lock no banco?

    Sugiro uma leitura dos links abaixo:

    http://blogs.msdn.com/b/fcatae/archive/2010/04/28/efeitos-colaterais-do-with-nolock-parte-i.aspx

    http://blogs.msdn.com/b/fcatae/archive/2010/06/02/efeitos-colaterais-do-with-nolock-parte-ii.aspx

    Concerteza responderam a sua pergunta, pois irão descrever as determinantes dos prós/contras e também quais as melhores situações de usar.

    - Considerando que tenho uma tabela "populada" e em produção,é aconselhável criar índices para esta tabela?

    O recomendado é utilizar um ambiente de desenvolvimento ou homologação, pois a manutenção de índices sempre geram um consumo de recurso a mais (tanto pra criação quanto pra exclusão), mesmo que seja pra um índice pequeno.

    - Quais são os principais motivos para gerar lock?

    Bem, lock é preciso acontecer num ambiente relacional, porém existem suas diferenças de bloqueios.

    Dar uma lida no artigo: http://angmaximo.wordpress.com/2012/06/11/locks-blocks-deadlocks/.

    Espero ter ajudado.

    Abraços!


    quarta-feira, 27 de agosto de 2014 00:32
  • - Mesmo usando o (NOLOCK) na procedure,poderia gerar um lock no banco?

    • SIM, o uso da HINT NOLOCK no SELECT faz com que esse SELECT possa ler dados que não foram COMMITADOS ainda, mas o próprio SELECT irá solicitar LOCKS, compartilhados por exemplo, para sua execução. O melhor a se fazer nesse caso é listar quais LOCKS foram solicitados e concedidos para ver o que aconteceu pra gerar a informação dos DBAS.

    - Considerando que tenho uma tabela "populada" e em produção,é aconselhável criar indices para esta tabela?

    • SIM, indices são necessários em todos os ambientes, principalmente em PRODUÇÃO para poder melhorar o plano de execução das consultas, é claro que é preciso analisar cada caso para se criar somente os indices necessários.

    - Quais são os principais motivos para gerar lock?

    • O SQL Server, como outros SGDBs, usam LOCKs a todo momento para poder garantir a integridade das transações e dados, ACID.
    • Existem dois tipos de LOCKS, pessimistas e otimistas, isso tem relação ao nivel de isolamento usado.
    • Existem também dois mecanismos, um que afeta os dados em memória (consistencia lógica) aplicando locks de linha, pagina ou tabela, e outro que afeta as operações fisicas (consistencia fisica) e é usado internamente como Latches e Spinlocks.

    Em resumo, quando se fala que um TSQL gerou um Lock, isso é normal, mas é preciso ver qual tipo de lock foi gerado e qual consequencia foi gerada, exemplo: bloqueios por um tempo muito longo e/ou deadlocks.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.



    quarta-feira, 27 de agosto de 2014 22:15
  • Bom dia a todos,

    Muito obrigado pelas respostas até o momento. Em conversa com  a equipe de banco de dados da empresa,eles sugeriram colocar um índice,mas a procedure em si é bem simples,não sei se vale o esforço para inserir índice.

    SET

    ANSI_NULLS ON

    GO

    SET

    QUOTED_IDENTIFIER ON

    GO

    ALTER

    PROCEDURE [dbo].[Stpprv_AplSISTEMA_PARAMETRO_Selecionar]

    @vchrCodSistemaParametro

    varchar(50) = null,


    @vchrDscSistemaParametro

    varchar(255) = null,


    @vchrValor

    varchar(255) = null,


    @dateDataRegistro

    datetime = null,


    @tinyAtivo

    tinyint = null



    AS

    SET

    NOCOUNT ON

    DECLARE


    @intReturnValue

    int

    BEGIN



    SELECT


    SISTEMA_PARAMETRO

    .CodSistemaParametro,


    SISTEMA_PARAMETRO

    .DscSistemaParametro,


    SISTEMA_PARAMETRO

    .Valor,


    SISTEMA_PARAMETRO

    .DataRegistro,


    SISTEMA_PARAMETRO

    .Ativo

    FROM SISTEMA_PARAMETRO WITH(NOLOCK)


    WHERE


    CodSistemaParametro

    = ISNULL(@vchrCodSistemaParametro, CodSistemaParametro) AND


    DscSistemaParametro

    = ISNULL(@vchrDscSistemaParametro, DscSistemaParametro) AND


    --DscSistemaParametro like '%' +  ISNULL(@vchrDscSistemaParametro, DscSistemaParametro) + '%' or @vchrDscSistemaParametro IS NULL AND


    (@vchrValor IS NULL or Valor = @vchrValor) AND


    DataRegistro

    = ISNULL(@dateDataRegistro, DataRegistro) AND


    Ativo

    = ISNULL(@tinyAtivo, Ativo)



    END

    SET

    NOCOUNT OFF

    RETURN

    Outra dúvida: Locks podem ser gerados por questões de infraestrutura,por exemplo,memória de servidor,rede,processamento de servidor,etc?

    Desde já agradeço o apoio de todos,

    Abraço,


    Eduardo

    quinta-feira, 28 de agosto de 2014 11:06
  • Outra dúvida: Locks podem ser gerados por questões de infraestrutura,por exemplo,memória de servidor,rede,processamento de servidor,etc?

    Não serão GERADOS por questões de infraestrutura, mas a quantidade de memória disponivel pode afetar mais rapido ou menos rapido um mecanismo interno que se chama Lock Escalation, que faz vários Locks menores serem transformados em um Lock maior somente. E isso pode gerar bloqueios indesejados se não monitorado. E a capacidade de processamento, pode influenciar no tempo de liberação dos Locks.

    Sobre a SP vs Indice, a questão não é tanto em relação ao código que a SP possue, e sim ao plano de execução que está sendo gerado para ela.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    quinta-feira, 28 de agosto de 2014 12:18
  • Obrigado pelo retorno Alex,

    Uma última dúvida: Existe alguma forma de fazer um levantamento de quais procedures possuem a tratativa NOLOCK e indices? Se sim,como eu faço?

    Grato

    Abraço,


    Eduardo

    quinta-feira, 28 de agosto de 2014 13:38
  • Você pode usar a SYS.SQL_MODULE para filtrar os registros que tenham NOLOCK no texto.

    http://technet.microsoft.com/en-us/library/ms175081(v=sql.105).aspx


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 29 de agosto de 2014 14:46
  • Outra dúvida: Locks podem ser gerados por questões de infraestrutura,por exemplo,memória de servidor,rede,processamento de servidor,etc?

    Não serão GERADOS por questões de infraestrutura, mas a quantidade de memória disponivel pode afetar mais rapido ou menos rapido um mecanismo interno que se chama Lock Escalation, que faz vários Locks menores serem transformados em um Lock maior somente. E isso pode gerar bloqueios indesejados se não monitorado. E a capacidade de processamento, pode influenciar no tempo de liberação dos Locks.

    Sobre a SP vs Indice, a questão não é tanto em relação ao código que a SP possue, e sim ao plano de execução que está sendo gerado para ela.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    Alex,

    Perfeito, concordo com você, mas um detalhe é importante, não significa que quanto mais memória nosso servidor tiver melhor sera, nada disso o SQL Server como qualquer outro SGBD é louco e adora memória, então temos que sempre dimensionar o melhor possível este uso, algo que Buffer Pool é envolvido.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    sexta-feira, 5 de setembro de 2014 14:44
    Moderador