Entendendo como trabalhar com a concorrencia de dados ( locks )

Versão para impressãoEnviar para amigoVersão PDF

  1. Identificando como trabalhar
  2. Granularidade de locks
    1. Tabela
    2. Página / Linha
    3. Lock de página / Page Lock
    4. Lock de linha / Row Lock
    5. Lock de indice / Key Lock
  3. Tipos de locks
    1. SHARED LOCK
    2. UPDATE LOCK / PROMOTABLE
    3. KEY LOCK
    4. EXCLUSIVE
    5. INTEND LOCK
  4. Comportamento e tipo de transações
    1. Comportamento
    2. Tipo de transações
  5. Recomendações e sugestões


Identificando como trabalhar


Para se trabalhar com a menor concorrência de accesso aos dados deve-se sempre considerar algumas variáveis. Segue abaixo:
  • A definição de como acessar o banco deve ser feito no contexto da funcionalidade e não de toda aplicação, isto é, um relatório deve trabalhar de um jeito, um cadastro de outro, um processo batch de outra forma, etc. Abaixo segue vários questionamentos destas variáveis que podem influenciar o modo como a aplicação deverá acessar os dados.
  • Deve-se analisar e definir qual o "modis operandis" da funcionalidade:
    • Irá fazer inclusão?
    • Irá fazer atualização?
    • Irá fazer leitura massissa de dados?
    • Em caso de inclusão/atualização/deleção, as transações podem ser quebradas em pequenas partes ou necessita que seja feito tudo em uma unica transação?
    • Os dados a serem alterados são préviamente conhecidos?
    • Necessita que os dados não possam ser modificados durante execução da transação?
    • Necessita que os dados não possam ser modificados durante processos de apenas leitura dos dados?
    • Os dados lidos , precisam ser dados "commitados" ou podem ser dados alterados porém não confirmados (leitura suja)?
    • A funcionalidade irá fazer o acesso de leitura por cursor?
    • A funcionalidade irá fazer inclusão/atualização por cursor?
  • O desenvolvedor sempre deve ter o conhecimento técnico de:
    • Quais as opções de acesso existentes de acesso ao banco ( set isolation )
    • Tipos de locks existentes ( exclusive, shared, intend, etc )
    • Qual o comportamento que uma aplicação deverá ter conforme lock/modo de acesso utilizado no desenvolvimento.
  • No banco de dados , os comandos que afetam o comportamento de uma transação são:
    • BEGIN WORK / COMMIT WORK / ROLLBACK
    • [CURSOR] FOR UPDATE
    • [CURSOR] FOR READ ONLY
    • [CURSOR] SCROLL
    • [SELECT] FOR UPDATE (exige que esteja em transação)
    • [SELECT] FOR READ ONLY
    • SET ISOLATION / SET TRANSACTION
    • SET LOCK MODE
    • LOCK TABLE / UNLOCK TABLE
    • Método de acesso a tabela por indice ou leitura sequencial

Para alguns exemplos de comportamento de locks leia este artigo

Granularidade de locks


Tabela


Locks em tabelas são colocados automaticamente quando é feito qualquer alteração em registro ou estrutura da tabela. O objetivo do banco colocar um lock de tabela quando um usuário trava/altera/inclui/exclui um registro é evitar que a estrutura da tabela seja modificada durante este processo.
Este tipo de lock pode ser colocado explicitamente com o comando LOCK TABLE [SHARED/EXCLUSIVE].

Página / Linha


A definição se um lock será na pagina ou na linha está fora do controle do usuário. A definição de qual lock será utilizado é feito pelo DBA que desenhou e definiu a tabela. Para definir qual lock utilizar deve-se considerar:
volume de dados X volume de modificações X volume de concorrência X recursos disponiveis.

Lock de página / Page Lock


O lock de página, como o próprio nome diz é feito na pagina da tabela (*** para maiores informações sobre a estrutura de tabela ver "Estrutura das tabelas" ***) e extente o mesmo conceito para o(s) indice(s) da tabela em questão.
Ao travar um registro em uma tabela que seu lock é por pagina, por consequência todos os outros registros que estão na mesma pagina serão travados juntos. A quantidade de registros por pagina pode variar conforme o tamanho de pagina e tamanho do registro da tabela. O usuário não tem como ter controle sobre quais registros estão na mesma página.
Quando um lock é colocado em um registro de um indice de uma tabela muito acessada , isso comumente gera um grande "engarrafamento" nos acessos, pois devido o registro do indice ser naturalmente pequeno a proporção de registros/pagina de indice é muito superior ao da tabela, aumentando a chances de outro usuário precisar ler/alterar um dado na pagina travada.
O lock de página consome menos recurso do banco de dados pois a quantidade de memória para controlar a quantidade de locks é bem menor.

Lock de linha / Row Lock


Como o proporio nome diz, é feito a nivel de linha da tabela e extente o mesmo conceito para o(s) indice(s) da tabela em questão.

Lock de indice / Key Lock


Este lock é colocado na linha ou pagina de um indice.
Normalmente é colocado apenas quando há modificação no dado que faz parte do indice, quando o registro é excluido ou quando utilizado o tipo de transação REPEATABLE READ (mais abaixo segue informações sobre os tipos de transações).

Tipos de locks


Os tipos de locks e suas caracteristicas que devem ser conhecidos pelos desenvolvedores são:

SHARED LOCK


  • Não permite que ninguem altere o objeto
  • Mais de uma sessão pode por um SHARED LOCK no mesmo objeto
  • Pode por um LOCK em um objeto que já possui um PROMOTABLE/UPDATE LOCK
  • Não pode ser colocado em um objeto que já possui um EXCLUSIVE LOCK
  • É colocado normalmente em processos de leituras onde a transação está configurada para tal comportamento.

UPDATE LOCK / PROMOTABLE


  • Não permite que ninguem altere o objeto
  • Pode-se considera-lo como um passo entre SHARED LOCK e EXCLUSIVE LOCK
  • Define que há intenção de modificar o objeto
  • Não pode ser colocado onde já existe um lock EXCLUSIVE LOCK
  • Não pode ser posto em objeto que já possui um PROMOTABLE/UPDATE LOCK (esta é unica e grande diferença do SHARED LOCK)
  • Pode ser colocado em objeto que já tenha um SHARED LOCK
  • É colocado por CURSOR FOR UPDATE
  • Um dos motivos de sua existencia é melhorar a performance pois através deste lock o banco já sabe que a aplicaçõa irá alterar aquele dado e quando o update que promova o lock para exclusive, o banco irá apenas mudar o tipo de lock.

KEY LOCK


  • Utilizado apenas em indices.
  • Sua principal função é controlar registros excluidos e evitar uma situação onde um registro excluido que possua um indice primário ou unico não tenha sua exclusão confirmada (rollback) e nesse meio tempo outro usuário inclui um registro com o mesmo campo chave.
  • É colocado principalmente em registros excluidos.

EXCLUSIVE


  • Não permite que ninguem altere o objeto
  • Não pode ser colocado onde já exista qualquer outro tipo de lock
  • É colocado apenas em objetos que foram explicitamente modificados por algum destes comandos: INSERT/UPDATE/DELETE ou DDL

INTEND LOCK


  • Pode ser INTEND SHARED LOCK ou INTEND EXCLUSIVE LOCK
  • Indica que há possibilidade de ser colocado um Shared ou Exclusive lock no objeto
  • Utilizado normalmente para identificar locks em niveis de granularidade, por exemplo quando é colocado um lock em um registro, automaticamente é colocado um Intend na tabela.
  • É colocado automaticamente pelo banco de dados.

Tabela com relação de qual lock um objeto possui e qual lock pode ser colocado.
Copiado do Manual do Informix.

X - Exclusive Lock
U - Update Lock
S - Shared Lock
IS- Intend Shared
IX- Intend Exclusive
SIX- Shared Intend Exclusive

                 Hold X Hold U Hold S Hold IS Hold SIX Hold IX
                   Lock   Lock   Lock   Lock    Lock     Lock
  Request X lock   No     No     No     No      No       No
  Request U lock   No     No     Yes    Yes     No       No
  Request S lock   No     Yes    Yes    Yes     No       No
  Request IS lock  No     Yes    Yes    Yes     Yes      Yes
  Request SIX lock No     No     No     Yes     No       No
  Request IX lock  No     No     No     Yes     No       Yes

Comportamento e tipo de transações

Comportamento


SET LOCK MODE TO [WAIT | NOT WAIT | WAIT n]
Este comando define qual será o comportamento do banco se em uma leitura/alteração encontrar um lock em um objeto que a sessão precisará alterar. Este comando deve ser executado sempre no incio de uma sessão com o banco ou no incio de uma funcionalidade. As opções são:
* WAIT: Esperar eternamente na fila até que o objeto seja liberado
* NOT WAIT: Não espera, retornando erro (exceção) para a aplicação
* WAIT n: Espera n segundos, se até o tempo finalizar o objeto não for liberado é retornando erro (exceção) para aplicação

Tipo de transações


O tipo de transação é definido pelo comando SET ISOLATION e SET TRANSACTION (ANSI/SQL-92). O comando SET ISOLATION é especifico do Informix e permite utilizar os recursos especificos do banco, os parametros abaixo são do comando SET ISOLATION:

  • DIRTY READ [LAST COMMITTED] [RETAIN UPDATE LOCKS]
    Lê todos os registros não importando se estão sendo modificados e que a transação do registro lido ainda não tenha sido confirmada.
    Não coloca nenhum lock durante o processo de leitura.
    Variações:
    • Quando utilizado LAST COMMITTED + SELECT FOR READ ONLY
      Se encontrar um objeto travado com um lock exclusivo, é lido o seu "before image" ao invés de esperar pela liberação do lock.
    • Quando utilizado TRANSAÇÃO + SELECT FOR UPDATE
      Poe UPDATE LOCK apenas na linha lida
      Poe INTEND EXCLUSIVE LOCK na tabela
    • Quando utilizado TRANSAÇÃO + RETAIN + SELECT FOR UPDATE
      Poe UPDATE LOCK em cada linha lida, mantendo o lock até final da transação
      Poe INTEND EXCLUSIVE LOCK na tabela
  • COMMITED READ [LAST COMMITTED] [RETAIN UPDATE LOCKS]
    Lê apenas registros não alterados (SHARED ou UPDATE LOCK)
    Não coloca nenhum lock durante o processo de leitura.
    Variações:
    • Quando utilizado LAST COMMITTED + SELECT FOR READ ONLY
      Se encontrar um objeto travado com um lock exclusivo, é lido o seu "before image" ao invés de esperar pela liberação do lock.
    • Quando utilizado TRANSAÇÃO + SELECT FOR UPDATE
      Poe UPDATE LOCK apenas na linha lida
      Poe INTEND EXCLUSIVE LOCK na tabela
    • Quando utilizado TRANSAÇÃO + RETAIN + SELECT FOR UPDATE
      Poe UPDATE LOCK em cada linha lida, mantendo o lock até final da transação
      Poe INTEND EXCLUSIVE LOCK na tabela
  • CURSOR STABILITY [RETAIN UPDATE LOCKS]
    Lê apenas registros não alterados (SHARED ou UPDATE LOCK)
    Coloca um lock apenas no registro lido (fetched), assim quando o próximo registro é lido, o lock do registro anterior é liberado.
    Quando o registro é alterado, neste caso o lock irá ficar até o final da transação.
    Variações:
    • Quando utilizado SEM TRANSAÇÃO
      Não põe nenhum lock
    • Quando utilizado TRANSAÇÃO + SELECT FOR UPDATE
      Poe UPDATE LOCK apenas na linha lida
      Poe INTEND EXCLUSIVE LOCK na tabela
    • Quando utilizado TRANSAÇÃO + SELECT FOR READ ONLY
      Poe SHARED LOCK apenas na linha lida
      Poe INTEND SHARED LOCK na tabela
    • Quando utilizado TRANSAÇÃO + RETAIN + SELECT FOR READ ONLY
      Não põe nenhum LOCK na linha lida
      Poe INTEND SHARED LOCK na tabela
    • Quando utilizado TRANSAÇÃO + RETAIN + SELECT FOR UPDATE
      Poe UPDATE LOCK em cada linha lida, mantendo o lock até final da transação
      Poe INTEND EXCLUSIVE LOCK na tabela
  • REPEATABLE READ
    Lê apenas registros não alterados (SHARED ou UPDATE LOCK)
    Coloca um lock em cada registro analisado e não apenas os registros lidos (fetched), mantém estes locks até o final da transação. Exemplo.:
    Em uma tabela X que nao possui indice e tem 100.000 registros, se for feito um SELECT (FOR UPDATE + TRANSAÇÃO) que deverá retornar apenas o ultimo registro, o banco irá fazer uma leitura sequencial até encontrar este registro. Todas as linhas que forem analisadas para satisfazer o filtro serão travadas.
    Se a tabela possuir indice e o banco utilizar o indice para pesquisar então as chaves + linhas serão travados.
    Variações:
    • Quando utilizado SELECT + SEM TRANSAÇÃO Não põe nenhum lock
    • Quando utilizado TRANSAÇÃO + SELECT FOR READ ONLY Não põe nenhum LOCK na linha lida
      Poe INTEND SHARED LOCK na tabela
      Poe SHARED LOCK BY RR no indice (quando utilizado pela pesquisa)
    • Quando utilizado TRANSAÇÃO + SELECT FOR UPDATE Poe UPDATE LOCK em cada linha/indice analisado, mantendo o lock até final da transação
      Poe SHARED INTEND EXCLUSIVE LOCK na tabela
      Poe SHARED LOCK BY RR no indice (quando utilizado pela pesquisa)

Recomendações e sugestões


  • Se a linguagem de programação permitir, criar uma função global para tratar o retorno de qualquer comando enviado ao banco de dados para identificar o numero de mensagem retornado pelo banco e auxiliar na tomada de decisão de qual medida necessária como por exemplo alterar o tempo set lock mode to wait, tratando situações como registro travado
  • Programas de consulta simples e relatórios simples que exigem a leitura dos dados atuais no banco de dados, utilizar:
    • COMMITTED READ LAST COMMITTED
      Para evitar leitura de registros alterados e evitar travamento por registro em alteração.
    • SET LOCK MODE TO WAIT
      Neste tipo de programa teoricamente nunca será travado por outro usuário por estar utilizando o CRLC (descrito acima)
  • Programas de cadastro simples que fazem inclusão / atualização / deleção de dados.
    • COMMITTED READ LAST COMMITTED
      Para evitar leitura de registros alterados e ou travamento por registro em alteração.
    • Se a funcionalidade exige que ninguem altere os dados enquanto o usuário estiver consultando, ao selecionar os dados, faze-lo em transação e utilizando SELECT FOR UPDATE para colocar um UPDATE LOCK nos registros e assim evitar que outro usuário altere seu conteudo. Então executar o INSERT/UPDATE/DELETE necessáro.
      IMPORTANTE: A execução do INSERT/UPDATE/DELETE tem que ser na mesma transação
    • Se a funcionalidade não tem a exigencia de garantir que os dados que o usuário está consultando , a aplicação pode executar o UPDATE nos registros ao ter a confirmação do usuário
    • SET LOCK MODE TO WAIT 5
      Trata através de função global o retorno de registro travado e se viavel , identificar e exibir qual usuário está travando o registro. Redefinir WAIT para 10 segundos e rexecutar o comando.
  • Programas de cadastro complexos e processo batchs que fazem leitura / inclusão / atualização / deleção massissa de dados.
    • COMMITTED READ LAST COMMITTED RETAIN UPDATE LOCKS + SELECT FOR UPDATE
      Se a aplicação tem antecipadamente conhecimento de qual registros serão alterados , utilizar o SELECT FOR UPDATE na seleção dos dados para garantir que os mesmos poderão ser alterados.
    • Utilizar transações menor possivel para evitar que registros fiquem travados por muito tempo, gerando fila de espera por outros usuários e consumindo recursos desenecessáriamente do banco.
    • Incluir tratamento no retorno de todas as atualizações feitas dentro da transação, se possível incluir tratamento para lock waits onde se a aplicação começar a esperar muito tempo pelo registro, a transação é cancelada e reniciada.Para assim dar tempo e liberar os registros para os outros processos e evitar situações de deadlock.
    • SET LOCK MODE TO WAIT 5
      Trata através de função global o retorno de registro travado e se viavel , identificar e exibir qual usuário está travando o registro. Redefinir WAIT para 10 segundos e rexecutar o comando.

Exemplo de código:

  funcao check_SQL
    #   trata erros baseado no SQLCA.
    if sqlca.sqlcode != 0
      trata...
      se nao tratavel: return FALSE
  funcao A
    #   Função que busca TODOS os dados a serem alterados.
    set isolation to committed read last committed retain update locks
    select * from xyz,xtpo,onono where ... for update
    if not check_SQL: return FALSE
  funcao B
    #   Função que altera os dados
    update xyz set ... where ...
    if not check_SQL: return FALSE
    update xpto set ... where ...
    if not check_SQL: return FALSE
    update onono set ... where ...
    if not check_SQL: return FALSE

  funcao Principal

  input : filtro de dados a serem processados
  while
    #   executa busca principal
    while 1=1
      begin work
      if not funcao A (parametros filtro): rollback;continue;
      if not funcao B (parametros filtro): rollback;continue;
      commit work


4.95
Média: 5 (20 votos)
Sua avaliação: Nenhum
Tags:

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.