Quero no artigo de hoje comentar algo sobre um tipo de lock que ocorre no banco de dados Oracle, descriminado no evento: enq: TX - row lock contention do Oracle Database. Quero falar quando que ele ocorre e trazer um exemplo de como ele acontece.
Antes de tudo precisamos entender que o tipo de lock TX está geralmente relacionado a transação. Um lock TX é adquirido sempre que uma transação se inicia, ou seja, sempre que modificamos um dado (INSERT, UPDATE, DELETE) ou declaramos a intenção de alterá-lo (SELECT FOR UPDATE).
A transação é finalizada com um COMMIT ou um ROLLBACK independente de alterar uma ou várias linhas e o lock da transação é liberado também neste momento.
Agora quando vemos evento: enq: TX - row lock contention, podemos resumir dizendo que este evento ocorre quando há uma contenção na linha por mais de uma sessão, ou quando uma transação está atualizando uma linha e outra transação tenta atualizar a MESMA linha.
Vamos ver um exemplo:
Primeiro criamos uma tabela de saldo e inserimos três registros:
Connected to Oracle Database 12c Standard Edition Release 12.1.0.2.0 SQL> create table tb_saldo ( id_func number (3) primary key, saldo number(10,2)); Table created SQL> SQL> insert into tb_saldo values (1, 133.20); 1 row inserted SQL> insert into tb_saldo values (2, 200); 1 row inserted SQL> insert into tb_saldo values (3, 45.70); 1 row inserted SQL> commit; Commit complete SQL> select * from tb_saldo; ID_FUNC SALDO ------- ------------ 1 133,20 2 200,00 3 45,70
Agora nesta mesma sessão atualizamos o saldo para o id_func = 2:
SQL> update tb_saldo set saldo = 500 where id_func = 2; 1 row updated
Após o update acima, foi iniciada uma transação e também a obtenção de um lock transacional normal, garantindo assim que nada possa ocorrer na tabela ou na linha enquanto não for terminada a transação com um commit ou rollback, liberando assim o lock para outra sessão. Veja abaixo que a sessão (update) com SID 13 iniciou um lock transacional (TX):
SQL> SELECT s.EVENT, 2 L.SID, 3 L.BLOCK 4 FROM V$LOCK L 5 JOIN V$SESSION S 6 ON L.SID = S.SID 7 WHERE L.TYPE = 'TX'; EVENT SID BLOCK ---------------------------------------------------------------- ---------- ---------- SQL*Net message from client 13 0
Agora simulando com uma outra sessão de usuário, abra outro terminal por exemplo e execute o update abaixo que tenta atualizar a mesma linha que está sendo atualizada na sessão anterior e ainda não foi terminada:
Connected to Oracle Database 12c Standard Edition Release 12.1.0.2.0 SQL> update tb_saldo set saldo = 437 where id_func = 2;
Bom, como já era esperado, a sessão fica aguardando. A coluna Block = 1 que vemos repetindo a consulta que fizemos anteriormente indica que o SID=13 está bloqueando alguém, ou esta sessão está fazendo alguém esperar. A nova sessão (SID=318) fez um request de lock, mas sem sucesso. Perceba que o evento desta sessão 318 é: enq: TX - row lock contention, ou seja, ela está fazendo contenção (“brigando, lutando”) pelo lock, aguardando a outra sessão liberar ou terminar.
SQL> / EVENT SID BLOCK ---------------------------------------------------------------- ---------- ---------- enq: TX - row lock contention 318 0 SQL*Net message from client 13 1 SQL> SQL> SELECT A.SID, ' is blocking ', B.SID 2 FROM V$LOCK A 3 JOIN V$LOCK B 4 ON A.ID1 = B.ID1 5 AND A.ID2 = B.ID2 6 WHERE A.BLOCK = 1 7 AND B.REQUEST > 0; SID 'ISBLOCKING' SID ---------- ------------- ---------- 13 is blocking 318 SQL>
Bom, agora vamos terminar a primeira transação com rollback e vemos que agora a sessão SID=318, obteve o lock transacional(TX).
SQL> rollback; Rollback complete SQL> SELECT s.EVENT, 2 L.SID, 3 L.BLOCK 4 FROM V$LOCK L 5 JOIN V$SESSION S 6 ON L.SID = S.SID 7 WHERE L.TYPE = 'TX'; EVENT SID BLOCK ---------------------------------------------------------------- ---------- ---------- SQL*Net message from client 318 0 SQL>
Então o fluxo segue normalmente, aguardando o commit ou rollback para terminar a transação deste último SID, 318.
Veja abaixo outro exemplo, se você utilizar um update em todas as linhas ou um simples: SELECT * FROM... FOR UPDATE, irá gerar o mesmo lock(enq: TX - row lock contention) caso outra sessão tentar alterar alguma linha:
SQL> select * from tb_saldo for update; ID_FUNC SALDO ------- ------------ 1 133,20 2 200,00 3 45,70 SQL> SELECT s.EVENT, 2 L.SID, 3 L.BLOCK 4 FROM V$LOCK L 5 JOIN V$SESSION S 6 ON L.SID = S.SID 7 WHERE L.TYPE = 'TX'; EVENT SID BLOCK ---------------------------------------------------------------- ---------- ---------- SQL*Net message from client 13 0
Em outra sessão:
SQL> update tb_saldo set saldo = 437 where id_func = 2;
Consultamos novamente para ver o lock enq: TX - row lock contention gerado:
SQL> SELECT s.EVENT, 2 L.SID, 3 L.BLOCK 4 FROM V$LOCK L 5 JOIN V$SESSION S 6 ON L.SID = S.SID 7 WHERE L.TYPE = 'TX'; EVENT SID BLOCK ---------------------------------------------------------------- ---------- ---------- enq: TX - row lock contention 318 0 SQL*Net message from client 13 1 SQL> SELECT A.SID, ' is blocking ', B.SID 2 FROM V$LOCK A 3 JOIN V$LOCK B 4 ON A.ID1 = B.ID1 5 AND A.ID2 = B.ID2 6 WHERE A.BLOCK = 1 7 AND B.REQUEST > 0; SID 'ISBLOCKING' SID ---------- ------------- ---------- 13 is blocking 318 SQL>
É importante frisar que sessões que estão em eventos: enq: TX - row lock contention estão consumindo recursos da máquina como outras sessões, como utilização de CPU por exemplo, elas ficam aguardando a liberação o lock para continuar sua operação. Precisamos estar atendo a este tipo de lock pois, dependendo da situação podem ocorrer um enfileiramento de várias sessões neste tipo de evento ocasionando um alto consumo de recursos da máquina.
Podemos visualizar nas principais atividades do banco de dados no Enterprise Manager em uma SQL_ID específica uma imagem como abaixo:
Por hoje é só pessoal.
Abraço,
Ronaldo
Nenhum comentário:
Postar um comentário