none
Cursores versus Lock's RRS feed

  • Pergunta

  • Olá amigos,

    Preciso fazer um processamento em uma Stored Procedure com vários cursores. Minha dúvida é em relação a declaração dos cursores, pois estes farão referencias a tabelas que estão com vários usuários conectados e fazendo alterações.

    OBS: Todos os cursores abertos NUNCA terão seus dados alterados na mesma instancia, ou seja, se eu alterar um dado que está em um dos registros do cursor não será (e não deve) ser atualizado no próprio cursor.

    Sendo assim, gostaria de saber o seguinte:

    1) Se eu declarar o cursor como static, é necessário colocar "with (nolock)" nas tabelas?

    2) Se um usário estiver com um registro lokado (editando-o), e eu tentar abrir um cursor statico que "enxergará" esse registro, o processamento vai ficar esperando esse regsitro ser liberado?

    Obrigado a todos.
    Leandro.
     
    Tytto
    quarta-feira, 4 de fevereiro de 2009 19:46

Respostas

  • Tytto,


    Você declara o NoLock, Readpast no select.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    • Marcado como Resposta Tytto quinta-feira, 5 de fevereiro de 2009 17:46
    quinta-feira, 5 de fevereiro de 2009 13:11
  •  

    Boa Tarde,

    Me parece que você está associando bloqueios a uma espera desnecessária e alguns pontos devem ser refletidos para que você não se surpreenda posteriormente.

    Antes de propriamente postar a sintaxe, preciso que você reflita sobre alguns pontos:

    Imagine que alguém está alterando um registro em CMRECINS e você quer lê-lo. Se optarmos pelo bloqueio você terá que esperar, mas se optarmos por não exercê-lo, você pode ler dados sujos, ou seja, um dado antes da alteração ser concluída, ou os dados de uma alteração que pode sofrer um rollback. Você está admitindo essa hipótese ?

    Se os dados do cursor não impor bloqueios, você está admitindo que qualquer um poderá alterar os dados enquanto você está lendo. Essa hipótese é admissível ?

    Acredito que sua dúvida não seja relacionada ao cursor propriamente dito. Talvez posteriormente você devesse procurar pelo termo "controle de concorrência"

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto quinta-feira, 5 de fevereiro de 2009 17:46
    quinta-feira, 5 de fevereiro de 2009 17:09
  • Olá Tytto,

    Se essas possibilidades são admitidas, então coloque apenas um NOLOCK no SELECT do cursor. Ex:

    1 declare c_insumo_recurso cursor 
    2 static 
    3 for 
    4 select rec.seq_alocacao,   
    5 rec.cd_material, rec.especif1, rec.especif2,   
    6 rec.especif3, rec.numeracao, rec.qtde_necessaria,   
    7 rec.demanda_geral, rec.lote, mat.campo85 as utiliza_grade  
    8 from 
    9 CMRECINS rec WITH (NOLOCK)  
    10 inner join ESMATERI mat WITH (NOLOCK) on mat.cd_material = rec.cd_material  
    11 where rec.cd_plano = @p_cd_plano  
    12 and rec.op = @p_op  
    13 order by rec.demanda_geral; 


    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com
    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:19
    quinta-feira, 5 de fevereiro de 2009 17:57
  • A unica maneira é colocar NOLOCK é em cada uma das tabelas? Não existe nada a nível de declaração do cursor que interprete que todas as tabelas são NOLOCK?

    Minha insistência no exemplo, era que no material que pesquisei, falava em colocar NOLOCK no cursor e não na tabela.

    Considerando o exemplo acima, a única maneira é em cada uma das tablas?

    Leandro.


    Tytto
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:18
    quinta-feira, 5 de fevereiro de 2009 18:54
  • Olá Tuiuiu,

    Não creio que utilizar o Read Commited apresente desvantagens nesse cenário. A leitura dele é atrasada, mas não é inconsistente. O único problema é que a ativação do Read Commited Snapshot não é por transação e sua aplicação é por banco.

    Na verdade eu estava avaliando essa possibilidade (mais precisamente o Snapshot Isolation Level desde o início). O problema é que essa feature é a partir do 2005 e como há a possibilidade de utilizar o 2000, não achei interessante mencioná-la.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:18
    sexta-feira, 6 de fevereiro de 2009 00:09

Todas as Respostas

  • Boa Tarde,

    Eu primeiro avaliaria se é necessário de fato utilizar cursores. Talvez exista uma outra forma de expressar sua lógica de forma mais eficiente e sem utilizá-los.

    O cursor do tipo STATIC irá gravar os dados no TempDB e não irá mais referenciar os dados de origem. Ele irá incorrer em locks, mas somente no momento em que é aberto, posteriormente o lock não fará diferença, pois, será imposto sobre a cópia e não os dados originais.

    Se o usuário estiver atualizando um registro que seu cursor tente acessar, como qualquer outro processo, o mesmo ficará bloqueado e deverá esperar que o registro seja liberado. Você poderá evitar isso se utilizar opções como NOLOCK, READPAST, Snapshot Isolation ou Read Commited Snapshot (cada uma com sua vantagem e desvantagem).

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 4 de fevereiro de 2009 20:25
  • Obrigado pela resposta.

    Em relação ao uso de cursores relamente será necessário, pois preciso abrir um determinado "pacote de registros" e processá-los um a um (sob testes de várias condições antes).

    Em relação ao NOLOCK, READPAST... que vc sugeriu, esses atributos são colocados nas tabelas usadas nos cursores ou na declaração do cursor?

    Qual seria a syntaxe (por exemplo) de declaração de um cursor que abre uma copia dos dados (static) de acordo com a ultima transação comitada (não esperar os regsitros lokados independente de algum estar em edição)?

    Muito obrigado.
    Leandro.


    Tytto
    quinta-feira, 5 de fevereiro de 2009 12:40
  • Olá Tytto,

    NOLOCK e READPAST são HINTs que são colocados sobre a consulta e não constituem propriedades do cursor. Sua utilização pode relaxar a consistência, mas melhorar a concorrência (sugiro procurar o Books OnLine para entender mais sobre esses HINTs ou abrir uma nova thread mais específica).

    Sua segunda pergunta está um pouco "dúbia", qual sua versão do SQL Server ?

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com
    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 5 de fevereiro de 2009 13:03
  • Tytto,


    Você declara o NoLock, Readpast no select.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    • Marcado como Resposta Tytto quinta-feira, 5 de fevereiro de 2009 17:46
    quinta-feira, 5 de fevereiro de 2009 13:11
  • Obrigado a todos,

    Se a segunda pergunta não ficou clara, o que preciso é de um exemplo da declaração de um cursor que não faça lock e não fique esperando registros lokados, ou seja, que traga o registro de acordo com a ultima transação comitada.

    Exemplo:

     declare @c_insumo_recurso cursor;
     set @c_insumo_recurso = cursor static for
                              select rec.seq_alocacao,
                                     rec.cd_material,
                                     rec.especif1,
                                     rec.especif2,
                                     rec.especif3,
                                     rec.numeracao,
                                     rec.qtde_necessaria,
                                     rec.demanda_geral,
                                     rec.lote,
                                     mat.campo85 as utiliza_grade
                              from CMRECINS rec
                              inner join ESMATERI mat on mat.cd_material = rec.cd_material
                              where rec.cd_plano = @p_cd_plano
                              and rec.op = @p_op
                              order by rec.demanda_geral;

    Considerando o exemplo acima:

    1) Se algum registro da tabela CMRECINS estiver lokado, ao abrir o cursor (ou fazer um fetch next) o processamento vai ficar aguardado o registro liberar? E se for na tabela ESMATERI? (considerando que o registro lokado seja selecionado de acordo com a clausula where do cursor, é claro!)

    2) Qual seria a alteração a ser feita para que o cursor do exemplo acima NÃO faça lock nos registros e NÃO fique aguardando registros lokados?

    Me desculpem por tantas perguntas, mais realmente preciso entender bem como funciona isso no MSSQL para definir meu padrão de código/regra e sinceramente, acredito que este site seja o melhor lugar.

    Também fiz algumas pesquisas e encontrei algum material, mais na prática não consegui nenhum exemplo.

    OBS: Utilizo o MSSQL 2000 e 2005.

    Leandro. 

     


    Tytto
    quinta-feira, 5 de fevereiro de 2009 16:45
  •  

    Boa Tarde,

    Me parece que você está associando bloqueios a uma espera desnecessária e alguns pontos devem ser refletidos para que você não se surpreenda posteriormente.

    Antes de propriamente postar a sintaxe, preciso que você reflita sobre alguns pontos:

    Imagine que alguém está alterando um registro em CMRECINS e você quer lê-lo. Se optarmos pelo bloqueio você terá que esperar, mas se optarmos por não exercê-lo, você pode ler dados sujos, ou seja, um dado antes da alteração ser concluída, ou os dados de uma alteração que pode sofrer um rollback. Você está admitindo essa hipótese ?

    Se os dados do cursor não impor bloqueios, você está admitindo que qualquer um poderá alterar os dados enquanto você está lendo. Essa hipótese é admissível ?

    Acredito que sua dúvida não seja relacionada ao cursor propriamente dito. Talvez posteriormente você devesse procurar pelo termo "controle de concorrência"

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto quinta-feira, 5 de fevereiro de 2009 17:46
    quinta-feira, 5 de fevereiro de 2009 17:09
  • Sim, estou considerando todas essas hipóteses e minha dúvida é somente em relação a declaração do cursor com essa característca.

    Também não tenho dúvidas sobre concorrencia de dados.

    Agradeço a preocupação e os lembretes mais por hora não se faz necessário.

    Obrigado.

    Leandro.


    Tytto
    quinta-feira, 5 de fevereiro de 2009 17:45
  • Olá Tytto,

    Se essas possibilidades são admitidas, então coloque apenas um NOLOCK no SELECT do cursor. Ex:

    1 declare c_insumo_recurso cursor 
    2 static 
    3 for 
    4 select rec.seq_alocacao,   
    5 rec.cd_material, rec.especif1, rec.especif2,   
    6 rec.especif3, rec.numeracao, rec.qtde_necessaria,   
    7 rec.demanda_geral, rec.lote, mat.campo85 as utiliza_grade  
    8 from 
    9 CMRECINS rec WITH (NOLOCK)  
    10 inner join ESMATERI mat WITH (NOLOCK) on mat.cd_material = rec.cd_material  
    11 where rec.cd_plano = @p_cd_plano  
    12 and rec.op = @p_op  
    13 order by rec.demanda_geral; 


    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com
    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:19
    quinta-feira, 5 de fevereiro de 2009 17:57
  • A unica maneira é colocar NOLOCK é em cada uma das tabelas? Não existe nada a nível de declaração do cursor que interprete que todas as tabelas são NOLOCK?

    Minha insistência no exemplo, era que no material que pesquisei, falava em colocar NOLOCK no cursor e não na tabela.

    Considerando o exemplo acima, a única maneira é em cada uma das tablas?

    Leandro.


    Tytto
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:18
    quinta-feira, 5 de fevereiro de 2009 18:54
  • Olá Tytto,

    Desconheço o uso do NOLOCK na declaração de um cursor e o Books OnLine também não tem nenhum menção a esse parâmetro. Você poderia informar qual foi o material lido para eu analisasse ?
    Se você não deseja especificar o NOLOCK para cada tabela, sugiro colocar o SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Lembre-se de revertê-lo se necessário, pois, o mesmo é válido por sessão.

    O DECLARE CURSOR tem o parâmetro OPTIMISTIC para trabalhar com concorrência otimista e não utiliza bloqueios. Embora já tenha trabalhado com situações de concorrência otimista (TimeStamp Columns, Snapshot e Read Commited Snapshot) eu nunca utilizei esse parâmetro com cursores.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 5 de fevereiro de 2009 19:16
  • Gustavo,


    Qual seria a desvantagem de usar o Read Commited Snapshot neste caso?

    Acredito que a principal vantagem seria não fazer leitura suja, correto?  Quanto a desvantagens... ainda não vi quais seriam.


    Roberto.
    quinta-feira, 5 de fevereiro de 2009 21:18
  • Olá Tuiuiu,

    Não creio que utilizar o Read Commited apresente desvantagens nesse cenário. A leitura dele é atrasada, mas não é inconsistente. O único problema é que a ativação do Read Commited Snapshot não é por transação e sua aplicação é por banco.

    Na verdade eu estava avaliando essa possibilidade (mais precisamente o Snapshot Isolation Level desde o início). O problema é que essa feature é a partir do 2005 e como há a possibilidade de utilizar o 2000, não achei interessante mencioná-la.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Tytto sexta-feira, 6 de fevereiro de 2009 10:18
    sexta-feira, 6 de fevereiro de 2009 00:09
  • Ok, fou revisar o código.

    Muito obrigado a todos pela ajuda,

    Leandro.
    Tytto
    sexta-feira, 6 de fevereiro de 2009 10:19