LOCKS – Oracle

Quem trabalha com banco de dados relacionais já deve ter tido problema com Locks diversas vezes. Podem deixar apps lentas, deixá-las completamente paradas, e naturalmente vem a dúvida: por que precisamos deles?

Eles não são tão ruins assim e ao final espero que entenda que eles são, na verdade, indispensáveis. São quem permitem que sistemas multi-users possam ser escaláveis de forma consistente. Atuam para que apenas uma transação de cada vez possa alterar um determinado dado, formando uma fila com todos que tem esta intenção.

Imagine você alterando uma linha da tabela de empregados. Enquanto pretendia alterar o “salário”, outra pessoa foi e alterou o “nome” do mesmo registro. Embora não pareça algo muito preocupante, você consegue imaginar a “bagunça” quando várias sessões tentam alterar o dado ao mesmo tempo? E no caso de alguém que queira aumentar 1.000 no salário e outra aumentar 10%: a ordem de execução vai afetar o resultado final.

Enfim, para começar é importante entender que Lock é a forma que o banco de dados tem de organizar as transações e se manter consistente. Alterações nas tabelas respeitam a ordem de chegada. Chegou primeiro, atualiza primeiro. Chegou depois e tem alguém atualizando? Espere até a conclusão. E aqui nascem os problemas.

Neste artigo tentaremos mostrar um pouco de como os Locks funcionam. Não dá para se falar de maneira global porque cada banco de dados acaba implementando de forma diferente, então focaremos na implementação do Oracle. Há, obviamente, vários conceitos relacionados e vamos tratando deles no decorrer do artigo. Como tem assunto para um livro, vamos dividir em uma pequena série de artigos.

Aqui falaremos de conceitos como transações e seu papel no contexto da aplicação. Também abordaremos um problema comum de apps multi-users, os chamados lost updates. Em seguida vamos para os problemas relacionados ao banco de dados como bloqueios, uma estrutura do cabeçalho do bloco de dados chamado ITL e deadlock.

Nos próximos artigos desta série, vamos mergulhar um pouco mais para descobrir como o Oracle trabalha internamente para gestão de Locks e transações e também como ele trata as multi versões de uma linha.

 

TRANSAÇÃO

 

Primeiramente, vamos entender como um banco de dados é alterado e o que há de especial nesta mudança. Parece simples, não?

SQL> update hr.employees set FIRST_NAME = ‘Adriano’, LAST_NAME = ‘Bonacin’ where EMPLOYEE_ID = 206;

1 row updated.

Neste momento (até o commit ou rollback) o banco de dados, de maneira simplificada:

– Está protegendo esta linha para que ninguém a altere;

– Protegendo a tabela para que ninguém a drope;

– Criou uma forma de desfazer a alteração;

– Criou uma forma para refazer.

E tudo isto depois de verificar se havia espaço livre na tabela, se eu tinha permissão para fazer a alteração, entre outras coisas.

Por default, o Oracle não confirma a alteração, embora seja otimizado para isso (e não para rollbacks). Assim, é importante garantir que o commit seja efetuado para não deixar o registro bloqueado. Que fique claro, porém, que as apps também podem ter uma arquitetura que já faça a alteração e o commit automaticamente.

Quando efetuado o commit e recebido o OK do banco de dados, um DB (A C I D) garante que a partir daquele momento seu dado não será mais perdido (Durabilidade). Você ainda pode perder o banco de dados inteiro, mas aí é outra conversa.

Resumindo, chamamos de transação esta operação de iniciada com um update (ou insert, delete, …) e finalizada com commit (ou rollback) que leva o DB de um estado consistente a outro.

 

LOST UPDATES

 

Tratando de transações e Locks do ponto de vista da aplicação, um problema comum ocorre quando vários usuários podem alterar o mesmo dado, o chamado de lost update (alteração perdida). Considere o cenário abaixo como exemplo:

1 – O usuário A consulta os dados do cliente 1 para ajustar seu endereço.

2 – O usuário B consulta os dados do mesmo cliente 1 para também ajustar o endereço.

3 – O usuário A altera o endereço para “Av Brigadeiro Faria Lima”.

4 – O usuário B altera o endereço para “Av Brigadeiro F Lima”.

Qual o resultado final? O último? O primeiro? Um mix?

Para aumentar a possibilidade trabalhar em concorrência (escalabilidade) sem estes conflitos (de perder alterações), geralmente apps usam diferentes metodologias: “Lock Pessimista” e “Lock Otimista”.

Basicamente:

– Pessimista: declaramos (Lock) que vamos alterar o dado (select for update) quando o consultamos. O próximo que tiver intenção de alterar, fica aguardando.

– Otimista: lemos o dado sem o Lock e garantimos na hora da alteração que ele estava exatamente como lemos (outra sessão pode ter alterado entre ler e alterar).

 

BLOCKs

 

Este é o principal efeito negativo, quando uma sessão possui um Lock de determinado dado e outra faz o request do mesmo. A sessão que chegou depois ficará aguardando (completamente congelada) até que o dado solicitado seja liberado.

Os bloqueios podem ocorrer com SELECT FOR UPDATE, quando duas sessões consultam alguma linha em comum. Neste caso, há uma cláusula NOWAIT que pode ser adicionada e a segunda sessão tomará um erro ao invés do block.

No caso dos INSERTS, há dois cenários em que ocorrem comumente. No primeiro, a tabela deve ter PK ou algum outro índice único e ambas as sessões estão tentando inserir a mesma chave. No segundo, duas tabelas devem ter FK e o insert na filha ocorre enquanto outra sessão inseriu na tabela pai (mas ainda não comitou).

Com UPDATEs, DELETEs e MERGEs, a prevenção seria a melhor forma estar livre. Tentar lockar a linha antes da alteração com SELECT FOR UPDATE NOWAIT e só então concluir a manipulação do dado.

 

LOCK ESCALATION

 

Um outro termo que talvez você veja por aí, mas que não ocorre com o Oracle, é o Lock Escalation. Em alguns DBs a forma de gestão de lock depende de um Lock Manager. Uma estrutura de tabela em memória que registra as alinhas que estão lockadas.

Porém, quando o número de linhas lockadas de uma mesma tabela é consideravelmente grande, o DB prefere escalar o lock para o bloco/tabela inteira. Fica mais fácil para gestão, mas provavelmente teremos linhas lockadas sem a real necessidade.

O Oracle não usa este tipo de gerenciamento de lock. Para lockar uma linha, o Oracle vai até o DB Block e armazena ali a transação que alterou (ou tem a intenção de) a linha, independentemente do número de linhas. Será sempre assim, nunca haverá o Lock Escalation.

 

ITL

 

Interested Transaction List (ITL) são estruturas do cabeçalho do bloco (DB block header) onde são armazenadas as transações que desejam alterar linhas daquele bloco. É ocupada uma entrada por transação e não por linha lockada. Assim, uma transação pode lockar 300 linhas do bloco que apenas uma entrada das ITLs será utilizada.

Associado a isso, estão os eventos de espera como o “enq: TX – allocate ITL entry” que ocorre quando a requisição de alterações no mesmo bloco por transações (sessões) diferentes é alta.

As ITLs estão diretamente relacionadas com os parâmetros INITRANS e MAXTRANS dos segmentos, que representam o número mínimo e máximo de transações ocorrendo no mesmo bloco. Após escolher o número mínimo, este valor pode crescer se houver espaço livro no bloco.

INITRANS: Número de entradas iniciais do bloco, cujo default é 2 (mesmo que você solicitar 1).

MAXTRANS: Número máximo de transações, que desde o Oracle 10g é 256.

Uma conclusão imediata disto é que não será possível mais que 256 transações por bloco, embora este seja um número mais do que adequado na grande maioria das vezes.

 

 

DEADLOCKs

 

Os deadlocks são no mínimo assustadores, mas é a forma que o DB tem de evitar locks cíclicos. Veja o cenário abaixo:

– Sessão A possui lock no empregado 200

SQL> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

– Sessão B possui lock no departamento 200

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update;

DEPARTMENT_ID DEPARTMENT_NAME

————- ——————————

200 Operations

– Sessão A tenta lockar o departamento 200 (lockado por B)

SQL> select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update;

… fica congelada

– Sessão B tenta lockar o empregado 200 (lockado por A)

SQL> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

… fica congelada

 

Veja o que ocorreu com a sessão A:

select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

No alert do DB é registrado este evento:
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32577.trc.

E a informação no trace (apenas algumas partes):

*** 2017-02-12 10:21:29.714

*** SESSION ID:(241.57407) 2017-02-12 10:21:29.714

*** MODULE NAME:(SQL*Plus) 2017-02-12 10:21:29.714

*** CLIENT DRIVER:(SQL*PLUS) 2017-02-12 10:21:29.714

DEADLOCK DETECTED ( ORA-00060 )

See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

—– Information for the OTHER waiting sessions —–

current SQL:

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update

—– End of information for the OTHER waiting sessions —–

 

Information for THIS session:

—– Current SQL Statement for this session (sql_id=1fu1jghcq3ydc) —–

select DEPARTMENT_ID, DEPARTMENT_NAME from departments where DEPARTMENT_ID = 200 for update

Como se pode ver, o trace traz algumas informações importantes na investigação do que pode ter ocorrido. A principal é o SQL que a minha sessão tentou executar e não conseguiu.

Em apps de grande porte, casos comuns de deadlock são com FK não indexadas. Quando se altera/exclui dados da chave na tabela pai, o Oracle coloca lock full nas tabelas filhas que não possuem índice nos mesmo campos. Com a tabela inteira lockada, a chance de alguém precisar destes dados é maior.

O fato que talvez cause uma certa estranheza em quem nunca se aprofundou no assunto é que este lock full ocorre apenas durante a execução do DML na tabela pai e não durante toda a transação.

Para ilustrar, vamos usar o seguinte cenário:

– Table pai: hr.departments (PK: department_id)

– Table filha: hr.employees (FK: department_id)

– por default, há um índice na hr.employees.department_id. Foi dropado: DROP INDEX EMP_DEPARTMENT_IX;

Se o lock durar apenas durante o DML na pai, poderemos lockar uma linha na filha após a conclusão.

SESSAO 1> DELETE departments d WHERE  d.department_id = 170;

1 row deleted.

Elapsed: 00:00:00.00

SESSAO 2> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

Elapsed: 00:00:00.01

Ok, conforme esperado. E como podemos então perceber este lock que dura 00:00:00.00 s? Bom, neste caso, se já houver algum registro lockado na filha, não será possível concluir o DML na pai. Faremos rollback nas duas e começaremos na ordem inversa.

SESSAO 2> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from employees where EMPLOYEE_ID=200 for update;

EMPLOYEE_ID FIRST_NAME           LAST_NAME

———– ——————– ————————-

200 Jennifer             Whalen

Elapsed: 00:00:00.00

SESSAO 1> DELETE departments d WHERE  d.department_id = 170;

… aguardando

Sempre devemos indexar as colunas das FKs? Não necessariamente. De forma simplificada, se você faz join com pai e filha e/ou pensa em alterar a chave (ou excluir) de uma tabela pai, é importante que na filha exista um índice.

 

CONCLUSÃO

 

Falamos um pouco de pontos positivos e negativos do lock. Negativos quando nos geram problemas de performance, positivos quando nos garantem a consistência dos dados. Falamos também de alguns fenômenos relacionados como o Lost Update, Blocks e Deadlocks.

Após esta breve introdução sobre o conceito, nos artigos seguintes aprofundaremos um pouco. Falaremos do tipo de lock, modo de lock tanto para DML como DDL. Revisitaremos o assunto das FKs, com uma riqueza maior de detalhes.

Até lá!

Adriano Bonacin

Todas as marcas eventualmente citadas neste Blog são de propriedade de suas respectivas empresas e sua menção tem apenas caráter informativo.

Mantenha-se informado

Informe seu e-mail abaixo e receba um resumo semanal de conteúdos exclusivos sobre estratégia, tecnologia e novidades do mercado de TI.

Fique tranquilo: Nós também não gostamos de SPAM. Enviaremos somente conteúdo de seu interesse e você poderá descadastrar-se a qualquer momento.

Adriano Bonacin

Especialista em Oracle há mais de 10 anos, Adriano é um profissional certificado Oracle. Já atuou em projetos dos mais variados portes, atualmente é DBA Oracle na empresa PagSeguro,