none
Lock + Threads RRS feed

  • Pergunta

  • Boa tarde!

    Estou desenvolvendo um aplicação multithread que executa determinada procedure diversas vezes simultaneamente, estou com o seguinte problema. Para nao processar registros que já estão sendo processados pela minha proc, adicionei uma coluna na tabela de registros que identifica se esta sendo processado o registro ou não, para a proxima query não pegar os mesmos registros. O problema é que em determinado momento realizo um update deste campo em diversos registros da tabela, e se a proc executar novamente e for realizar um update deste status em outros registros é gerado um deadlock.

    Como posso solucionar este problema?

    Obrigado a todos!


    "O pior naufrágio é daquele que não saiu do porto"
    terça-feira, 23 de março de 2010 20:10

Respostas

  • Boa Tarde,

    Até acho que o ROWLOCK possa ajudar, mas não acho que ele irá resolver em definitivo o seu problema. Veja que o ROWLOCK apenas irá promover o bloqueio em nível de registro, mas se não houver índices suficientes para eleger os registros necessários, o bloqueio será escalado para página ou tabela (mesmo que haja o HINT ROWLOCK) e nesse caso o problema permanece. Pode parecer óbvio, mas eventualmente você pode ter dificuldades para eleger índices úteis no caso de dados aleatórios. Veja o roteiro abaixo que demonstra como o ROWLOCK sozinho é ineficaz sem índices úteis:

    -- Abra três janelas no SQL Server Management Studio (J1, J2, J3)
    -- Na J1 execute o código abaixo:
    CREATE TABLE T (C INT)
    INSERT INTO T VALUES (1)
    INSERT INTO T VALUES (2)
    -- Na J2 execute o código abaixo:
    -- Veja que o UPDATE só "usa" a linha 1 já que o ROWLOCK está ativo
    -- A transação está aberta e por isso "a linha está bloqueada"
    BEGIN TRAN
    UPDATE T WITH (ROWLOCK) SET C = 3 
    WHERE C = 1
    -- Na J3 execute o código abaixo:
    SELECT * FROM T WITH (ROWLOCK) WHERE C = 2
    -- Embora J3 queira a linha 2 e J1 queira a linha 1, J3 continua esperando...
    -- Na janela J2 execute o código abaixo:
    ROLLBACK
    -- Logo após o ROLLBACK J3 consegue prosseguir
    -- Agora repita o mesmo exemplo usando o seguinte código em J3
    SELECT * FROM T WITH (ROWLOCK, READPAST) WHERE C = 2

    Sugiro combinar o HINT ROWLOCK com o HINT READPAST nesse caso, mas eventualmente não deixe de rever seus índices. Não adiantará muito utilizar os HINTs se não houver índices para limitar os registros corretamente. Se possível dê uma olhada também na possibilidade de utilizar lógicas de concorrência otimistas.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Michel Pérez quinta-feira, 25 de março de 2010 11:58
    quarta-feira, 24 de março de 2010 17:04

Todas as Respostas

  • Michel,

        Talvez o melhor forum para tratar disso seja o forum de desenvolvimento .NET. De qualquer forma, vamos aguardar se alguém ajuda.


    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    terça-feira, 23 de março de 2010 21:42
    Moderador
  • Acredito que seria aqui, pois preciso de um auxilio na solução para execução da mesma procedure em concorrencia. Pois quando é executado um update em determinado trecho da procedure, ele da um lock na table e a aoutra procedure nao pode atualizar os outros dados. Acredito que seja a nível de banco esta dúvida mais do que de .net.


    "Quanto mais um homem se aproxima de suas metas, tanto mais crescem as dificuldades"
    quarta-feira, 24 de março de 2010 13:56
  • Bom Dia,

    A utilização de múltiplas threads no mundo da programação tem sido cada vez mais fácil e hoje desenvolver algo multithread é muito mais simples do que era no passado. Além da facilidade, não raras às vezes há muitas classes prontas Web a fora. A questão fundamental de uma solução multithread (e isso não é uma frase minha) é que não adiantará implementar múltiplas threads se essas threads disputam recursos em comum.

    Podemos desenvolver várias threads que consultam uma base simultaneamente, mas a partir do momento em que elas disputam um registro comum, a idéia de paralelismo fica em cheque, pois, no momento em um registro for utilizado por uma thread as demais terão necessariamente que esperar. Enquanto esperam, recursos estão alocados e no final das contas, o processamento tornar-se-á serializado, pois, nas situações de atualização e exclusão o acesso ao recurso é exclusivo e não compartilhado impedindo que de fato as coisas ocorram em paralelo. Nas situações mais críticas, nas quais há mais recursos envolvidos, além da serialização a possibilidade de DEADLOCK fica ainda mais próxima, pois haverá múltiplos recursos exclusivos por múltiplas threads de forma exclusiva.

    Na sua situação eu sugeriria rever se realmente haverá ganhos em utilizar uma solução multithread (ou que pelo menos diminua-se a quantidade de threads), pois, o acesso cruzado e exclusivo está serializando parte das transações e introduzindo problemas de DEADLOCK. Você poderia ainda tentar utilizar uma lógica de concorrência otimista através do uso do TIMESTAMP ou dos níveis de isolamento como SNAPSHOT ou o READ COMMITTED SNAPSHOT. Isso evitaria a contensão do UPDATE, mas pode introduzir outros conflitos de atualização em virtude do versionamento de linha em conjunto com os UPDATEs. Há também a possibilidade de efetuar um tratamento de erro e ressubmeter as transações, mas além do overhead de recursos e manutenção do código não é garantia de solucionar o problemas.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 24 de março de 2010 14:18
  • Cara, em 2000 participei de um projeto que faziamos o mesmo procedimento.. eram, 400 digitadores que tinham o mesmo procedimento.. (pegar 10 registros cada um, diferentes dos outros e atualizar um campo)

    A gente instruia eles que quando tivessem problemas de demora (sabiamos que era travamento) levantassem a mão..

    Quando colocamos em produção esse prog, lembro-me que foram 400 maos levantadas hahahaha..locura..

    Foi resolvido colocando o hint Rowlock no update..nao tivemos mais problemas

     


    www.laertejuniordba.spaces.live.com www.simple-talk.com/author/laerte-junior/
    quarta-feira, 24 de março de 2010 14:50
  • Obrigado pelo auxilio Roberto e Gustavo, porem o ganho será muito se o processamento que a proc realizar fizer em paralelo. Na verdade o cenário é o seguinte:

    Tenho um tabela com diversos registros, a procedure irá selecionar vamos supor 1000 registros desta tabela e irá realizar um processamento que leva aproximadamente 2 minutos, enquanto é processado estes 1000 registros, posso processar em concorrencia outros 1000 registros desta tabela que possui aproximadamente 5.000.000 de registros, os dados processados não serão os mesmos, porem preciso pegar registros diferentes dos que já estão sendo processados, então criei uma coluna de status para identificar se está ou nao sendo processado o registro, desta forma o proximo processamento pode pegar registros diferentes do que esta sendo processado. O problema esta no momento de fazer o update do campo status, ele gera um deadlock se a o 1º processo esta realizando o update o 2º processo tenta realizar o update do campo status para os outros registros. Posso realizar o processamenteo em paralelo, porem somente neste trecho preciso realizar um update em uma tabela que os outros processamentos tambem utilizam, porem o update é para registros diferentes. Como proceder neste caso?!

    Obrigado novamente.


    MCTS .NET Framework 2.0 - Web Applications
    quarta-feira, 24 de março de 2010 14:58
  • Huummm...certo...acho ke era isso que precisava Laerte...vo ver fazer uns testes com o rowlock e retorno os resultados...

    Vlw!


    MCTS .NET Framework 2.0 - Web Applications
    quarta-feira, 24 de março de 2010 16:40
  • Boa Tarde,

    Até acho que o ROWLOCK possa ajudar, mas não acho que ele irá resolver em definitivo o seu problema. Veja que o ROWLOCK apenas irá promover o bloqueio em nível de registro, mas se não houver índices suficientes para eleger os registros necessários, o bloqueio será escalado para página ou tabela (mesmo que haja o HINT ROWLOCK) e nesse caso o problema permanece. Pode parecer óbvio, mas eventualmente você pode ter dificuldades para eleger índices úteis no caso de dados aleatórios. Veja o roteiro abaixo que demonstra como o ROWLOCK sozinho é ineficaz sem índices úteis:

    -- Abra três janelas no SQL Server Management Studio (J1, J2, J3)
    -- Na J1 execute o código abaixo:
    CREATE TABLE T (C INT)
    INSERT INTO T VALUES (1)
    INSERT INTO T VALUES (2)
    -- Na J2 execute o código abaixo:
    -- Veja que o UPDATE só "usa" a linha 1 já que o ROWLOCK está ativo
    -- A transação está aberta e por isso "a linha está bloqueada"
    BEGIN TRAN
    UPDATE T WITH (ROWLOCK) SET C = 3 
    WHERE C = 1
    -- Na J3 execute o código abaixo:
    SELECT * FROM T WITH (ROWLOCK) WHERE C = 2
    -- Embora J3 queira a linha 2 e J1 queira a linha 1, J3 continua esperando...
    -- Na janela J2 execute o código abaixo:
    ROLLBACK
    -- Logo após o ROLLBACK J3 consegue prosseguir
    -- Agora repita o mesmo exemplo usando o seguinte código em J3
    SELECT * FROM T WITH (ROWLOCK, READPAST) WHERE C = 2

    Sugiro combinar o HINT ROWLOCK com o HINT READPAST nesse caso, mas eventualmente não deixe de rever seus índices. Não adiantará muito utilizar os HINTs se não houver índices para limitar os registros corretamente. Se possível dê uma olhada também na possibilidade de utilizar lógicas de concorrência otimistas.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta Michel Pérez quinta-feira, 25 de março de 2010 11:58
    quarta-feira, 24 de março de 2010 17:04
  • UPDATE T WITH (ROWLOCK, READPAST) SET C = 0 
    WHERE C = 2

    Boa Tarde,

    Até acho que o ROWLOCK possa ajudar, mas não acho que ele irá resolver em definitivo o seu problema. Veja que o ROWLOCK apenas irá promover o bloqueio em nível de registro, mas se não houver índices suficientes para eleger os registros necessários, o bloqueio será escalado para página ou tabela (mesmo que haja o HINT ROWLOCK) e nesse caso o problema permanece. Pode parecer óbvio, mas eventualmente você pode ter dificuldades para eleger índices úteis no caso de dados aleatórios. Veja o roteiro abaixo que demonstra como o ROWLOCK sozinho é ineficaz sem índices úteis:

    -- Abra três janelas no SQL Server Management Studio (J1, J2, J3)
    
    -- Na J1 execute o código abaixo:
    
    CREATE TABLE T (C INT)
    
    INSERT INTO T VALUES (1)
    
    INSERT INTO T VALUES (2)
    
    -- Na J2 execute o código abaixo:
    
    -- Veja que o UPDATE só "usa" a linha 1 já que o ROWLOCK está ativo
    
    -- A transação está aberta e por isso "a linha está bloqueada"
    
    BEGIN TRAN
    
    UPDATE T WITH (ROWLOCK) SET C = 3 
    
    WHERE C = 1
    
    -- Na J3 execute o código abaixo:
    
    SELECT * FROM T WITH (ROWLOCK) WHERE C = 2
    
    -- Embora J3 queira a linha 2 e J1 queira a linha 1, J3 continua esperando...
    
    -- Na janela J2 execute o código abaixo:
    
    ROLLBACK
    
    -- Logo após o ROLLBACK J3 consegue prosseguir
    
    -- Agora repita o mesmo exemplo usando o seguinte código em J3
    
    SELECT * FROM T WITH (ROWLOCK, READPAST) WHERE C = 2
    
    

    Sugiro combinar o HINT ROWLOCK com o HINT READPAST nesse caso, mas eventualmente não deixe de rever seus índices. Não adiantará muito utilizar os HINTs se não houver índices para limitar os registros corretamente. Se possível dê uma olhada também na possibilidade de utilizar lógicas de concorrência otimistas.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível


    Perfeito Gustavo!

    Funcionou quase como preciso, porem no meu cenário exige que enquanto a transacao nao estiver commitada, possa ser realizado outro update na mesma tabela, neste caso vc demonstrou com um comando select, se eu alterar o comando para um update conforme o comando abaixo que utilizei:

    UPDATE T 
    WITH (ROWLOCK, READPAST) 
    SET C = 0 
    WHERE C = 2

    Ele aguarda o outro update ser commitado. Existe de alguma forma um jeito de realizar este outro update enquanto não for liberado o 1º?

    Obrigado novamente!!


    MCTS .NET Framework 2.0 - Web Applications

    • Marcado como Resposta Michel Pérez quinta-feira, 25 de março de 2010 11:57
    • Não Marcado como Resposta Michel Pérez quinta-feira, 25 de março de 2010 11:58
    quarta-feira, 24 de março de 2010 20:14
  • Olá Michel,

    Esse é o ponto. Você não conseguirá fazer esse update com HINTs como ROWLOCK, READPAST, etc. A chave está na cláusula WHERE. É preciso que as colunas nessa cláusula possuam um índice eficiente para filtrar os registros. Sem um índice eficiente, o UPDATE irá escalar o lock para um nível de página ou tabela e aí múltiplas threads encontrarão um recurso em comum impedindo o paralelismo e colocando-as em processo serializado. Se as transações necessitarem atualizar pelo menos um registro em comum será impossível de chegar na implementação desejada. Se os registros para cada thread forem realmente diferentes, então você precisará criar um índice para que eles possam ser filtrados com eficiência.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    quinta-feira, 25 de março de 2010 00:30
  • Olá Michel,

    Esse é o ponto. Você não conseguirá fazer esse update com HINTs como ROWLOCK, READPAST, etc. A chave está na cláusula WHERE. É preciso que as colunas nessa cláusula possuam um índice eficiente para filtrar os registros. Sem um índice eficiente, o UPDATE irá escalar o lock para um nível de página ou tabela e aí múltiplas threads encontrarão um recurso em comum impedindo o paralelismo e colocando-as em processo serializado. Se as transações necessitarem atualizar pelo menos um registro em comum será impossível de chegar na implementação desejada. Se os registros para cada thread forem realmente diferentes, então você precisará criar um índice para que eles possam ser filtrados com eficiência.

    [ ]s,

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

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível

    HUmmmm...entendi!!!...muito bom auxilio...obrigado Gustavo e Laerte!!
    MCTS .NET Framework 2.0 - Web Applications
    quinta-feira, 25 de março de 2010 11:57