O Assunto que iremos abordar neste tutorial considro bastante interessante e sem nenhuma duvida é algo que todos os dias a grande parte dos DBA devem ter que fazer algum tipo de analise que envolva LOCK.
Para demonstrações neste tutorial estarei utilizando uma base de dados com o nome "bd_teste" que está na instancia "door" ,tenho 2 tabelas criadas (teste1 e teste2)
TOPICOS QUE VEREMOS NESTE TUTORIAL
* O que é LOCKS?
* Tipos de LOCKS / Siglas
* Explicação sobre cada tipo de Locks
* Granularidade de Locks
* Demonstração dos Locks na pratica
* Locks de pagina
* Locks de linha
* Log de base de dados
* Niveis de isolamento
- O que é LOCKS?
Posso definir Lock como sendo um método que o banco de dados utiliza para controlar os acessos a base de dados, tabelas e linhas (Colocando ordem e dando preferencias e exclusividades) , como o banco de dados normalmente tem diversos usuarios conectados de forma simultânea e cada usuário que chega até o banco de dados está com alguma intenção com algum dos objetos da base de dados, para garantir a integridade de dados para que não tenhamos conflitos e possa garantir a consistência dos dados o banco de dados então utiliza do recurso de LOCKS.
Comentario: Mesmo sendo fundamental e de extrema importância a utilização de LOCKS posso dizer que muitas vezes incomoda muito, e piora a situação quando o desenvolvedor desconhece o metodo de trabalhar com LOCKS no INFORMIX, mas nem pensem em imaginar ter um banco de dados sem utilização de LOCKS, isso seria praticamente terminar com toda a integridade de dados que existe.
Certamente todo o DBA vai ter varias situações que irá precisar monitorar LOCKS.
Inicialmente vamos perceber que é uma tarefa bastante complicada para ser feita no INFORMIX, após entendermos o funcionamento iremos mudar de opinião e achar simples e de facil visualização, porém não adianta tentar monitorar se não souber como monitorar e o que monitorar e se não houver algumas scripts ou ferramentas para esse monitoramento acontecer.
* TIPOS DE LOCKS / SIGLAS
Vai encontrar essas referencias no retorno do comando onstat -k na coluna "type"
- Share Lock - S
- Update Lock - U
- Exclusive Lock - X
- Itent Lock - IX
- Byte Lock - B
- Share Ineted exclusive Lock - SIX
- Exclusive key value held by a repeatable reade - XR
* Explicação sobre cada tipo de Locks
Estaremos vendo com frequencia referencias ao campo tblsnum no decorrer do TUTORIAL, para uma simples e facil compreensão abaixo uma referencia. (Abaixo teremos uma explicação de todos os campos de retorno do onstat -k)
* Se o rowid for igual a zero, o tblsnum se refere a um bloqueio de tabela.
* Se o rowid terminar em dois zeros, o bloqueio se refere a um bloqueio de pagina.
* Se o rowid estiver com até 6 digitos e não terminar em zero, o bloqueio se refere a linha.
* Se o rowid estiver com mais de 6 digitos, o bloquieo se refere a um indice
Comentario: Perceba que o significado da coluna "tblsnum" está relacionado com a coluna "rowid"
Share Lock Database - HDR-S ou S
O Share Lock de database é representado como HDR-S na primeira conexão que colocar o LOCK e como S para as demais conexões, toda conexão que chegar na database irá marcar um LOCK se não for definido nenhum especifico lock será automaticamente colocado o SHARE LOCK, esse lock é importante para que não exista alterações na database enquanto houver usuarios conectados.
- Só pode ser colocado um SHARE LOCK na database que não existir um EXCLUSIVE LOCK.
Vamos ver um pouco na pratica:
Como estamos com um banco de teste é possivél fechar todas as conexões existentes, então vamos lá encerre todas as conexões existentes e após isso faça
terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ onstat -k # Comando executado
Retorno: Veja abaixo o retorno em tela que mostra que não temos nenhum LOCK no momento.
IBM Informix Dynamic Server Version 10.00.UC4 -- On-Line -- Up 13 days 04:11:11 -- 3378356 Kbytes
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
0 active, 600000 total, 32768 hash buckets, 5 lock table overflows
Comentario: Não houve retorno nenhum de LOCK pois não existe nenhuma conexão estabelecida.
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbaccess bd_teste # Comando executado
terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ onstat -k # Comando executado
Retorno: veja abaixo que agora temos o retorno em tela que mostra que existe 1 LOCK tblsnum = 100002 e rowid = 208 do tipo HDR+S (lembra que HDR+S quer dizer que é um SHARE LOCK e é referente a primeira conexão por estar com HDR)
Comentario: Se quiser ver que a proxima conexão ficará apenas com "S" abra um terceiro terminal e se conecte também na bd_teste e execute novamente no terminal 1 o comando "onstat -k" e veja que terá um novo SHARE LOCK só que desta vezz com o type "S" pois é a segundo conexão .
IBM Informix Dynamic Server Version 10.00.UC4 -- On-Line -- Up 13 days 04:12:18 -- 3378356 Kbytes
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
a4c7d280 0 a14b3a70 0 HDR+S 100002 208 0
1 active, 600000 total, 32768 hash buckets, 5 lock table overflows
Bem você deve estar se perguntando , como sabemos que esse LOCK é referente a database e qual database ... calma ai ... vamos ver já isso ...
sempre que o tblsnum for 100002 representa que é um lock de database
Para saber qual database está em LOCK vamos fazer uma consulta no banco de dados na database sysmaster table sysdatabase
$dbaccess sysmaster # Comando executado
escrever a consulta abaixo (Pode utilizar o dbaccess para essa consulta)
select name , hex(rowid) hex_rawid_database from sysdatabase'
No meu caso retorna # Esse retorno vai variar de acordo com a quantidade de base que existir no servidor e nomes.
name sysmaster
hex_raw_id_tablee 0x00000201
name sysutils
hex_raw_id_tablee 0x00000202
name sysuser
hex_raw_id_tablee 0x00000203
name bd_teste
hex_raw_id_tablee 0x00000208
veja que o retorno do numero 0x00000208 representa o rowid 208 então concluimos que o lock do tblsnum = 100002 do rowid = 208 é referente a database "bd_teste", caso o LOCK estivesse indicando rowid 203 teriamos então um SHARE LOCK na tabela sysuser e assim por diante ...
Vamos ver um exemplo de como funciona o SHARE LOCK .
1° Tentando alterar o nome de uma tabela que esteja com SHARE LOCK.
terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbaccess bd_teste # Comando executado
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbaccess bd_sysmaster # Comando executado
rename database bd_teste to teste_bd # Linha digitada no dbaccess
Essa situação irá retornar os dois erros abaixo demonstrados, isso porque estamos tentando mudar o nome de uma base que existe conexões.
425: Database is currently opened by another user.
107: ISAM error: record is locked.
Share Lock table -- HDR-S ou S
O Share Lock de tabela é representado como HDR+S na primeira conexão que colocar o LOCK como "S" para as demais conexões, esse lock é importante para que não exista alterações na tabela enquanto consultas estejam sendo efetuadas.
Para identificar o SHARE LOCK da table com o comando onstat -k o rowid estará representado pelo numero "0" e o tblsnum estará exibindo de forma hexadecimal o numero da tabela.
- só é permitido colocar um Share Lock na tabela que não existir um exclusivo Lock
Vamos trabalhar na pratica um pouco:
Terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
Comentario: Vamos colocar o Lock explicitamente na tabela
$ dbaccess bd_teste # Comando executado
begin; lock table tabela1 in share mode # Linha digitada no dbaccess
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
Comentario: Vamos verificar o Share lock
$ onstat -k # Comando executado
retorno do onstat -k
IBM Informix Dynamic Server Version 10.00.UC4 -- On-Line -- Up 13 days 06:03:44 -- 3378356 Kbytes
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
101209bc 0 9ffed0ec 0 HDR+S 100002 208 0
105dad84 0 9ffed0ec 101209bc HDR+S 700264 0 0
2 active, 600000 total, 32768 hash buckets, 5 lock table overflows
analisando o retorno do comando onstat -k
Neste retorno temos 2 Locks do tipo Share onde os dois são os primeiros a serem colocados. (Sabemos que são os primeiros porque estão com HDR)
Agora pergunto a todos, porque 2 Locks se neste momento só estamos explicitamente colocando LOCK na "tabela1" ?
É claro que todos que estão acompanhando esse tutorial já sabem a resposta na ponta da lingua ... a resposta é porque para chegar na tabela precisa conectar na database e para toda a conexão aberta com a database é criado um share lock.
Bem como já definimos acima o SHARE LOCK de database sabemos que está conectado na database bd_teste então vamos agora analizar o SHARE LOCK da tabela.
tblsnum = 700264 # Essa é a tabela em hexadecimal então coloque na query abaixo e irá conseguir identificar o nome da tabela , lembrando acrescente 0x00 antes do código.
Sabemos que se trata de um SHARE LOCK na tabela porque o rowid é igual a 0
SELECT tabname FROM systables
WHERE hex(partnum) = "0x00700264"
Resultado da query:
tabname teste1 # Tabela "teste1"
Então concluimos que tblsnum = 700264 é o mesmo que tabname teste1 ou seja a tabela teste1 tem um SHARE LOCK.
outra maneira de conseguir informações de qual tabela é referido o numero hexadecimal retornado pelo onstat -k na coluna tblsnum será através do comando oncheck -pt
$ oncheck -pt "0x00700264" # Comando executado retorno do comando
TBLspace Report for bd_teste:gerenc.teste1
Physical Address 9:1169997
Creation date 10/05/2010 18:11:51
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 1
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 8
Number of pages used 1
Number of data pages 0
Number of rows 0
Partition partnum 7340644
Partition lockid 7340644
Extents
Logical Page Physical Page Size Physical Pages
0 9:2176832 8 8
Exclusive Lock DATABASE -- HDR+X ou X
Lock exclusivo em database pode ser colocado de modo explicito ou pode ser gerado através de utilitarios do tipo dbexport , exclusivo Lock na database será representado sempre na coluna de type como HDR+X ,detalhe neste caso de LOCK de database é que somente pode existir um unico processo com o exclusivo lock então não teremos situações de existir um Lock de database que não seja HDR+X que seja apenas "X" isso seria impossivél.
- Não é possivél colocar EXCLUSIVO LOCK quando já existir um SHARE LOCK , caso tente colocar será retornado erro -425 e -107
vamos provacar EXCLUSIVO LOCK efetuando um export da base de dados
Terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbexport bd_teste # Comando executado
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ onstat -k # Comando executado
Retorno do comando onstat -k
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
101209bc 0 9ffed0ec 0 HDR+X 100002 208 0
1 active, 600000 total, 32768 hash buckets, 5 lock table overflows
Analisando o comando, podemos relembrar um pouco o que foi explicado no SHARE LOCK para database.
"o numero Rowid é o hexadecimal que refere o nome da database na tabela sysdatabase da database sysmaster"
"encontraremos o nome da database correspondente ao rowid numero 208 através da consulta,
select name , hex(rowid) hex_rawid_database from sysdatabase
"tblsnum = 100002 referencia-se a LOCK de DATABASE"
no comando acima o type que nos retorna é "HDR+X" isso quer dizer que base de dados está com exclusivo Lock isso quer dizer que não será possivél nenhuma outra conexão com a base de dados que se referencia na sysdatabase da database sysmaster como hexadecimal "0x00000208" e se não conseguimos mais conectar por estar EXCLUSIVO LOCK isso quer dizer que nenhum outro tipo de LOCK será criado nesta base de dados. (Meio que obvio isso mas importante entender isso)
tentativa de conexão na base de dados bd_teste durante o exclusivo lock "HDR+X"
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbaccess bd_teste # Comando executado
Retorno de erro quando se tenta conectar em uma base de dados que esteja com "HDR+X"
425: Database is currently opened by another user.
107: ISAM error: record is locked.
Exclusive Lock TABLE -- HDR+X ou X
O exclusivo lock de uma tabela é utilizado para previnir que ninguem faça leitura ou escrita na tabela, logico que para algumas regras podem existir execeções, neste caso pode ser utilizado nivéis de isolamento "isolation level" do tipo leitura suja "dirty reader" porém essa execeção pode causar incosistencia dos dados que estejam sendo recuperados.
Vamos ver na pratica:
terminal 1 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ dbaccess bd_teste # Comando executado
begin; lock table teste1 in exclusive mode
terminal 2 # Quando citar Terminal estou me referindo a um Shell no prompt do usuario INFOMIX
$ onstat -k # Comando executado
retorno do comando onstat -k
IBM Informix Dynamic Server Version 10.00.UC4 -- On-Line -- Up 14 days 04:03:23 -- 3378356 Kbytes
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
101209bc 0 9ffed0ec 0 HDR+S 100002 208 0
105dad84 0 9ffed0ec 101209bc HDR+X 700264 0 0
2 active, 600000 total, 32768 hash buckets, 5 lock table overflows
Bem analisando o retorno acima podemos verificar que temos um Share Lock na database motivado pela conexão que estabelecemos quado abrimos a conexão para colocar a tabela em LOCK, temos também um EXCLUSIVE LOCK na tabela hex 0x00700264 que identificamos pelo rowid = 0 esse exclusive Lock nos forçamos acontecer..
* Granularidade de Locks
existe uma granularidade do exclusivo Lock por tabela podendo ser colocado lock de linha (row) ou em pagina (page)
Comentario: não citei essa granularidade quando falei do Share Lock table pois achei que estari complicando muito, mas tudo que veremos se aplica ao SHARE também.
Vamos então ver na pratica
