none
Evitar bloqueio em tabela RRS feed

  • Pergunta

  • Ola, pessoal

    tenho uma query que lê alguns dados que envolvem a tabela de movimentação de Notas. Ela já foi otimizada mas assim mesmo é meio demorada a execução, leva em torno de 50 segundos , as vezes um pouco mais de 1 minuto, dependendo do contexto.

    Enquanto isso, usuários não podem emitir nova movimentação, pois conforme verifiquei com o sp_who2, enquanto está rodando o SQL , o processo da consulta fica na coluna blkByProcess do processo do INSERT.

    Também já reconstrui os índices das tabelas envolvidas na consulta.

    Eu sei que não é recomendado o with (nolock) neste contexto, pela possibilidade de leitura suja. 

    Mas o usuário não pode ficar impedido de lançar NF enquanto o SQL Server está processando esse SQL.

    Tentei utilizar também o with (readpast) esperando que fosse funcionar como o (nolock) mas não funcionou... acho que não entendi bem o funcionamento.

    Alguém teria alguma sugestão para esta situação?

    --

    Outra dúvida, o que significa o SUSPENDED ?



    • Editado Julio Costi terça-feira, 22 de outubro de 2019 18:03 correção texto
    terça-feira, 22 de outubro de 2019 18:03

Respostas

  • Tentei utilizar também o with (readpast) esperando que fosse funcionar como o (nolock) mas não funcionou... acho que não entendi bem o funcionamento.

    Júlio, a dica de tabela READPAST é algo como "se a linha estiver bloqueada por outro processo, ignore-a e siga adiante". Ou seja, a leitura que se obtém pode ser de somente parte das linhas.

    Essa dica de tabela é útil, por exemplo, quando se está apagando linhas em blocos e se a linha estiver bloqueada no momento do DELETE ela é pulada e na próxima execução do laço tenta-se novamente apagá-la. Algo assim:

    -- código #1
    -- Autor: José Diz/BH

    declare @QtdLinhas int, @dataCorte date; set @QtdLinhas= 4000; set @dataCorte= convert (date, '1/1/2011', 103); declare @Apagados int; set @Apagados= 1; while @Apagados > 0 begin BEGIN TRANSACTION; DELETE top (@QtdLinhas) tbPONTO with (READPAST, HOLDLOCK) where Data_ref < @dataCorte; set @Apagados= @@rowcount; COMMIT;

    -- verifica se ainda há pendências causadas pelo READPAST
    IF @Apagados = 0
    IF exists (SELECT * from tbPONTO where Data_ref < @dataCorte)
    set @Apagados= 1; -- pausa entre apagamento de blocos waitfor delay '00:00:05'; -- 5 segundos end;

    Isto faz com que o processo de apagamento não fique parado em estado de transação, aguardando por liberação de bloqueios de linhas. Detalhes no artigo “Apagar conjunto de linhas em tabelas enormes”.

    Nem sempre é a linha a apagar que está bloqueada mas sim a página em que a linha está armazenada; neste caso o READPAST não atua.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 23 de outubro de 2019 09:27
    • Marcado como Resposta Julio Costi quinta-feira, 24 de outubro de 2019 12:41
    quarta-feira, 23 de outubro de 2019 01:02

Todas as Respostas

  • Olá amigo,

    Como você pôde ver , a querie esta com um lock devido a um insert que foi executado por outra sessão . 

    Este tipo de processo normalmente é tratamento de aplicação. 

    Vocé é da Equipe de Desenvolvimento ? poderá passar para eles averiguarem . talvez uma alteração na lógica da querie poderá resolver a questão.


    Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    terça-feira, 22 de outubro de 2019 21:19
  • Tentei utilizar também o with (readpast) esperando que fosse funcionar como o (nolock) mas não funcionou... acho que não entendi bem o funcionamento.

    Júlio, a dica de tabela READPAST é algo como "se a linha estiver bloqueada por outro processo, ignore-a e siga adiante". Ou seja, a leitura que se obtém pode ser de somente parte das linhas.

    Essa dica de tabela é útil, por exemplo, quando se está apagando linhas em blocos e se a linha estiver bloqueada no momento do DELETE ela é pulada e na próxima execução do laço tenta-se novamente apagá-la. Algo assim:

    -- código #1
    -- Autor: José Diz/BH

    declare @QtdLinhas int, @dataCorte date; set @QtdLinhas= 4000; set @dataCorte= convert (date, '1/1/2011', 103); declare @Apagados int; set @Apagados= 1; while @Apagados > 0 begin BEGIN TRANSACTION; DELETE top (@QtdLinhas) tbPONTO with (READPAST, HOLDLOCK) where Data_ref < @dataCorte; set @Apagados= @@rowcount; COMMIT;

    -- verifica se ainda há pendências causadas pelo READPAST
    IF @Apagados = 0
    IF exists (SELECT * from tbPONTO where Data_ref < @dataCorte)
    set @Apagados= 1; -- pausa entre apagamento de blocos waitfor delay '00:00:05'; -- 5 segundos end;

    Isto faz com que o processo de apagamento não fique parado em estado de transação, aguardando por liberação de bloqueios de linhas. Detalhes no artigo “Apagar conjunto de linhas em tabelas enormes”.

    Nem sempre é a linha a apagar que está bloqueada mas sim a página em que a linha está armazenada; neste caso o READPAST não atua.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 23 de outubro de 2019 09:27
    • Marcado como Resposta Julio Costi quinta-feira, 24 de outubro de 2019 12:41
    quarta-feira, 23 de outubro de 2019 01:02
  • Ola, @Jefferson

    Eu acho que na verdade se trata de um lock NO insert, causado pelo Select.

    o comando insert está sendo bloqueado (blocked by) [processo da query]

    nos testes eu estou fazendo, assim que a consulta finaliza, ou que paro ela com STOP (em outra sessão no management) , o insert é executado imediatamente.

    --

    @José, 

    entendi a explicação sobre o READPAST 

    realmente eu acho que não faz sentido para a minha situação.

    Bem.... eu estava lendo sobre isolation levels de transactions, e eu achei sobre snapshot.

    ele é aplicado somente à transação, é isso? e não a um comando diretamente (como o uso do nolock nas tabelas, nas querys), por isso não tem como coloca-lo na query, como o "nolock" ??

    Eu acho que seria o ideal (snapshot), pois é exatamente este o cenário. "Preciso executar uma query SQL com os dados "atuais" do momento em que inicia a consulta (não sendo necessário considerar novas inserções, deletes, ou updates que ocorrem durante a execução, .seria totalmente irrelevante) e não bloquear os insert,

    por isso nao faria sentido BLOQUEAR toda uma tabela durante a execução de 30s, ou q seja, 1min... (consulta esta que pode demorar, sem problemas)...... e correr o risco de atrasar ou PERDER uma inserção de registro importante e que DEVE ser imediata.

    Para aplicar uma das formas de snapshot, é no SqlCommand?


    Julio C.


    • Editado Julio Costi quarta-feira, 23 de outubro de 2019 02:55 texto
    quarta-feira, 23 de outubro de 2019 02:34
  • Uma pergunta: é considerado "inaceitável" uma query durar 1 ou 2min, em um BD transacional? (desde que ela não afete os demais processos... é claro).

    há indícios claros de que ela tem problemas, por causa desse tempo de execução?



    • Editado Julio Costi quarta-feira, 23 de outubro de 2019 11:10
    quarta-feira, 23 de outubro de 2019 02:58
  • Julio,

    Dizer que é inaceitável é algo muito genérico e abstrato, pois não conhecemos o seu ambiente e suas regras de negócio, talvez este é um questionamento que você deva fazer a si mesmo.

    A dica Readpast, é conhecida por muitos como lêr o passado, ou seja, realizar acesso e leitura dos dados que já foram confirmados e encontra-se neste momento isolados e armazenados na tabela independente do que esta sendo processado.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 23 de outubro de 2019 23:30