terça-feira, 29 de agosto de 2017

Row Locks (TX) - Como identificar a linha bloqueada

Em um banco de dados multiusuários como o Oracle database, é necessário que exista algum tipo de bloqueio de dados para que possam ser resolvidos os problemas associados à simultaneidade, consistência e integridade de dados. Estes bloqueios são mecanismos que impedem a interação destrutiva entre as transações que acessam o mesmo recurso.

Um dos tipos de bloqueios existentes é o bloqueio (lock) de linha identificado pelo evento de espera (wait event) "enq: TX - row lock contention".

Um lock do tipo linha ocorre naturalmente sobre a linha de uma tabela, ou seja, enquanto uma linha (registro) está "alocado", nenhuma outra sessão conseguirá alterá-lo até que a transação que originou o lock chegue ao seu fim, seja por commit ou rollback.


A identificação de um lock transacional (TX) pode ser realizada de diferentes formas, aqui utilizaremos o script utllockt.sql distribuído pela própria Oracle e encontrado abaixo do $ORACLE_HOME/rdbms/admin

Montando o cenário:

SESSÃO 1:


SQL> select distinct sid from v$mystat;

       SID
----------
        58

SQL> create table teste (id number);

Table created.

SQL> insert into teste values (1);

1 row created.

SQL> insert into teste values (2);

1 row created.

SQL> insert into teste values (3);

1 row created.

SQL> insert into teste values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> update teste set id=5 where id=1;

1 row updated.

Observe que ao final do UPDATE não foi executado commit ou rollback;

SESSÃO 2:


SQL> select distinct sid from v$mystat;

       SID
----------
        51

SQL> update teste set id=6 where id=2;

1 row updated.

SQL> update teste set id=5 where id=1;


A sessão 2 (sid=51) ao tentar realizar a alteração da mesma linha da sessão 1 (sid=58) ficou "travada" pois a linha id=1 está bloqueada até que a sessão 1 realize um commit ou rollback do registro.


SESSÃO 3:


SQL> select distinct sid from v$mystat;

       SID
----------
        56

SQL> update teste set id=6 where id=2;

A sessão 3 (sid=56) ao tentar alterar a mesma linha que a sessão 2 (sid=51) também ficou presa, pois a sessão 2 ainda não concluiu sua transação, desta forma existe a sessão 2 aguardando a sessão 1 e a sessão 3 aguardando a sessão 2 conforme árvore de bloqueio abaixo:

SESSÃO 4:



SQL> set lines 200
SQL> @?/rdbms/admin/utllockt.sql
drop table lock_holders
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

drop   table dba_locks_temp
             *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


2 rows created.


Commit complete.


Table dropped.


1 row created.


Commit complete.


WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
58                None
   51             Transaction       Exclusive      Exclusive      196619            958
      56          Transaction       Exclusive      Exclusive      327712            947


Table dropped.

Cenário de bloqueio:



Identificando as linhas bloqueadas:

Sempre que uma sessão fica aguardando um lock transacional (row lock – TX), algumas informações adicionais são pobuladas na v$session:




Utilizando-se destes dados é possível identificar o ROWID da linha bloqueada.

O ROWID é uma pseudocolumn que representa o endereço de cada linha de uma tabela. Os valores desta pseudocoluna são strings que podem conter os caracteres A-Z, a-z, 0-9 e o sinal de mais (+) e a barra (/).

Os rowids são formados pelas seguintes informações:

  •          Data block: Bloco de dados que contém a linha;
  •          Row: Linha no bloco de dados;
  •          Database file: Arquivo de dados que a linha;
  •         Data object number: Número de identificação do objeto.


Agora basta utilizar o pacote DBMS_ROWID para interpretar os conteúdos de rowid. As funções do pacote extraem e fornecem informações sobre os quatro elementos rowid listados acima.

Como as colunas ROW_WAIT_* da v$session são populadas apenas quando uma sessão está aguardando alguma transação, podemos identificar a rowid (endereço da linha) que as sessões 2 (sid=51) e 3 (sid=56) estão aguardando.


Linha bloqueada da sessão 2:


SQL> set lines 200
SQL> col object_name for a30
SQL> select o.object_name,
  2  s.row_wait_obj#,
  3  s.row_wait_file#,
  4  s.row_wait_block#,
  5  s.row_wait_row#,
  6  dbms_rowid.rowid_create ( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# ) as "ROWID"
  7  from v$session s, dba_objects o
  8  where s.sid=51
  9  and s.ROW_WAIT_OBJ# = o.OBJECT_ID;


OBJECT_NAME                    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ROWID
------------------------------ ------------- -------------- --------------- ------------- ------------------
TESTE                                  57927              1           83065             0 AAAOJHAABAAAUR5AAA


SQL> select * from teste where rowid='AAAOJHAABAAAUR5AAA';

        ID
----------
         1

 Linha bloqueada da sessão 3:



SQL> select o.object_name,
  2  s.row_wait_obj#,
  3  s.row_wait_file#,
  4  s.row_wait_block#,
  5  s.row_wait_row#,
  6  dbms_rowid.rowid_create ( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# ) as "ROWID"
  7  from v$session s, dba_objects o
  8  where s.sid=56
  9  and s.ROW_WAIT_OBJ# = o.OBJECT_ID;


OBJECT_NAME                    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ROWID
------------------------------ ------------- -------------- --------------- ------------- ------------------
TESTE                                  57927              1           83065             1 AAAOJHAABAAAUR5AAB


SQL> select * from teste where rowid='AAAOJHAABAAAUR5AAB';

        ID
----------
         2

Neste exemplo (cenário), como não foi utilizado bind variables nas operações DML é possível identificar também o valor bloqueado pelo próprio texto sql da instrução:



SQL> select sql_id from v$session where sid=51;

SQL_ID
-------------
g7tsgdb2thq5t

SQL> select sql_fulltext from v$sql where sql_id='g7tsgdb2thq5t';

SQL_FULLTEXT
--------------------------------------------------------------------------------
update teste set id=5 where id=1

SQL> select sql_id from v$session where sid=56;

SQL_ID
-------------
7r7ugkhm1bnaz

SQL> select sql_fulltext from v$sql where sql_id='7r7ugkhm1bnaz';

SQL_FULLTEXT
--------------------------------------------------------------------------------
update teste set id=6 where id=2


Com a utilização de bind variables a instrução retornada seria similar a:



update teste set id=:b2 where id=:b1


A identificação da instrução também é importante para saber quais colunas estão sofrendo alterações, pois a linha retornada pelo rowid pode conter várias colunas diferentemente do exemplo onde a tabela possuía apenas uma coluna.


Referências:

https://docs.oracle.com/database/122/CNCPT/data-concurrency-and-consistency.htm#CNCPT1313
https://docs.oracle.com/database/122/REFRN/V-SESSION.htm#REFRN30223

https://docs.oracle.com/database/122/SQLRF/Data-Types.htm#SQLRF50998
Postagem mais recente Postagem mais antiga Página inicial

0 comentários:

Postar um comentário

Translate

# ACE Program

#Oracle

#Oracle
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

#Blog reconhecido

#ARTICULISTA

Marcadores

Postagens populares