Oracle – O Lock TX

Falamos superficialmente sobre como os locks funcionam no Oracle, também de problemas frequentes com eles relacionados, como o lost update, block e o deadlock.

Agora é hora de olhar com um pouco mais de detalhe como as coisas funcionam internamente. Neste artigo vamos focar no lock TX e sua relação com as ITLs.

Mostraremos também uma nova feature do Oracle 12c com relação a permissão de leitura x lock.

DML Lock

Basicamente temos 3 classes de lock no Oracle que se subdividem:

– DML Lock

– DDL Lock

– Internal Lock e Latch.

DML (Data Manipulation Language) são instruções SQLs que se propõem a alterar determinado dado. Os locks deste tipo visam manter estas alterações consistentes mesmo que ocorram de forma “concorrente”. Mas note que não há alteração concorrente, então o melhor a se dizer é que os locks apenas gerenciam, de certa forma, a alteração por ordem de chegada.

Outro ponto importante é que, durante o tempo em que estamos alterando os dados, não seja permitido alterações estruturais ou em mesmo drop da tabela. O Oracle irá cuidar de tudo isso sem que você perceba ou, pelo menos, que você quase não perceba.

Lock TX

O termo TX está geralmente ligado a expressão “transaction” e é com isto que este tipo de lock está relacionado. Um lock TX é adquirido sempre que uma transação inicia e ela se inicia sempre que modificamos um dado (INSERT, UPDATE, DELETE, …) ou declaramos a intenção de alterá-lo (SELECT FOR UPDATE). Ela finalizará com um COMMIT ou um ROLLBACK.

Haverá apenas um lock TX independentemente do número de linhas, blocos ou tabelas que sua transação envolva. É importante notar que, mesmo assim, esta não é uma operação custosa. Diferente de outras implementações, o Oracle não centraliza a gestão de locks em estruturas como “lock manager”. Como dito no artigo anterior, o Oracle se baseia nas ITL (Interested Transaction List) de cada bloco para fazer esta gestão.

Desta forma, o lock estará sempre junto com o dado e ao consultá-lo (ou tentar alterá-lo) será possível descobrir se está livre. O melhor disto é que o caminho para ler ou modificar é o mesmo e direto, sem precisar passar por algum lock manager, diminuindo assim o overhead em momentos em que há muita alteração e muito locks.

Usaremos o famoso SCOTT para demonstração dos casos que veremos aqui. Então, se quiser reproduzir nosso teste, basta criá-lo em seu DB. Lembrando que estamos usando o 12c, que traz uma importante mudança com relação a permissão de leitura de dados e que citaremos ainda neste artigo.

Primeiro teste que faremos será verificar o número de linhas na v$lock versus o número de linhas alteradas. Vamos lockar uma linha da dept e em seguida todas as linhas.

SCOTT@orcl > select * from dept;

DEPTNO DNAME          LOC

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

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

Ao realizar uma alteração (sem commit), podemos ver o lock.

SCOTT@orcl > update dept set loc = ‘SAO PAULO’ where DEPTNO = 40;

1 row updated.

SYS@orcl >

SELECT s.username,

l.sid,

trunc(l.id1 / power(2, 16)) undoseg,

bitand(l.id1, to_number(‘ffff’, ‘xxxx’)) + 0 slot,

l.id2 seq,

l.lmode,

l.request,

l.type,

l.block

FROM   v$lock     l

JOIN   v$session  s

ON     l.sid      = s.sid

WHERE  l.type     = ‘TX’

AND    s.username = ‘SCOTT’;

 

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         245          5         13       2729     6       0 TX        0

 

E ao alterar todas as linhas com a mesma sessão:

SCOTT@orcl > update dept set DNAME = lower(DNAME);

4 rows updated.

 

Consultando a fila de locks:

SYS@orcl >/

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         245          5         13       2729     6       0 TX        0

 

Ou seja, independente de alterar uma linha ou várias, o lock é feito por transação. Além disso, você deve ter notado que a query traz alguns campos “estranhos”. Eles servem exatamente para identificar a transação[1]. Veja só:

SYS@orcl >select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

XIDUSN    XIDSLOT     XIDSQN

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

5         13       2729

 

Não vamos entrar em muitos detalhes, mas para não deixar tão inexplicável, o Oracle possui duas colunas para registrar 3 informações: Undo segment number, slot e sequence, por isso precisamos desta manipulação.

O importante aqui é notar que o lock aponta para os mesmos valores de USN, SLOT e SQN da transação, então uma transação, um lock.

 

BLOCKs

 

Agora vamos conferir como os blocks funcionam. Alteramos (sem COMMIT) todas as linhas da tabela DEPT.

SCOTT@orcl > update dept set loc = ‘SAO PAULO’;

4 rows updated.

 

Temos todas as linhas lockadas, o que ocorre se outra transação tentar alterar alguma linha da DEPT?

Bom, como já era esperado, a sessão fica aguardando:

SCOTT@orcl > update dept set loc = ‘SAO PAULO’ where DEPTNO = 10;

Como fica a fila de locks na v$lock?

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         421          4         24       2469     0       6 TX        0

SCOTT         244          4         24       2469     6       0 TX        1

 

Vemos uma informação interessante: A sessão com SID 244 que já possuía o lock, agora consta com BLOCK = 1 identificando que ela está fazendo alguém esperar.

A nova sessão (SID=421) fez um request de lock, mas sem sucesso. Veja que as informações a respeito da transação apontam para aquela que possui o lock.

É desta forma conseguimos identificar quem está locando quem.

SQL>

SELECT a.sid, ‘ is blocking ‘, b.sid

FROM   v$lock a

JOIN   v$lock b

ON     a.id1 = b.id1

AND    a.id2 = b.id2

WHERE  a.block = 1

AND    b.request > 0;

SID ‘ISBLOCKING’         SID

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

244  is blocking         421

 

 

ITLs

 

E as ITLs, o que tem a ver com isso? Bom, agora o assunto fica um pouco mais interessante. Vamos precisar de um dump do bloco onde estão os dados da DEPT para entender. Além disso, para ilustrar o valor default do INITRANS, vamos ver como está na tabela:

SYS@orcl >

SELECT owner,

table_name,

ini_trans

FROM   dba_tables d

WHERE  d.table_name = ‘DEPT’

AND    d.owner      = ‘SCOTT’;

OWNER        TABLE_NAME       INI_TRANS

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

SCOTT        DEPT                     1

 

Vamos identificar o block/datafile:

SYS@orcl >

SELECT DISTINCT dbms_rowid.rowid_block_number(ROWID) blk,

dbms_rowid.rowid_relative_fno(ROWID) file_no

FROM   scott.dept;

BLK    FILE_NO

———- ———-

181          6

 

Agora o dump do block:

SYS@orcl >alter session set tracefile_identifier=’ABONACIN’;

Session altered.

SYS@orcl >alter system dump datafile 6 block 181;

System altered.

 

O arquivo de trace será gerado no mesmo diretório do seu alert.

-rw-r—–. 1 oracle oinstall  197 Feb 26 13:30 orcl_ora_9742_ABONACIN.trm

-rw-r—–. 1 oracle oinstall 5.0K Feb 26 13:30 orcl_ora_9742_ABONACIN.trc

 

[oracle@hostlab trace]$ cat orcl_ora_9742_ABONACIN.trc

 

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0005.00d.00000aa9  0x0100073b.0254.34  —-    4  fsc 0x0000.00000000

 

Pode ser utilizado uma calculadora comum do Windows para constatar que AA9 hexa corresponde a 2729 decimal, 00d -> 13, 0005 -> 5, valores associados ao undo segment number, slot e sequence.

 

 

E o número 4 na coluna Lck, o que significa? Vamos seguir com os testes. Feito ROLLBACK, vamos alterar uma linha e fazer um dump.

SCOTT@orcl > update dept set loc = ‘Sao Paulo’ where deptno = 10;

1 row updated.

 

Locks:

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         244          4         24       2469     6       0 TX        0

 

ITL:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.25  —-    1  fsc 0x0000.00000000

 

Vamos fazer o update na mesma transação, em outra linha.

SCOTT@orcl > update dept set loc = ‘Maringá’ where deptno = 20;

1 row updated.

 

Locks (continuamos com apenas um):

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         244          4         24       2469     6       0 TX        0

 

ITL:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.26  —-    2  fsc 0x0000.00000000

 

E assim por diante. Veremos que esta coluna representa a quantidade de linhas deste bloco que estão lockadas por esta transação. Ao fazer um update nas 4 linhas:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0009.003.000005db  0x01000a41.00c1.21  C—    0  scn 0x0000.0018b4fb

0x02   0x0004.018.000009a5  0x010007de.02a7.27  —-    4  fsc 0x0000.00000000

 

 

Como vimos, há duas ITLs neste bloco. O que significa que apenas duas transações podem alterá-lo simultaneamente. O que acontece se uma terceira transação tentar alterar uma linha da DEPT?

SCOTT@orcl > update dept set loc = ‘São Paulo’ where DEPTNO = 10;

1 row updated.

SCOTT@orcl > update dept set loc = ‘Maringá’ where deptno = 20;

1 row updated.

SCOTT@orcl > update dept set loc = ‘Salvador’ where deptno = 30;

1 row updated.

 

Os locks foram obtidos, como é possível?

USERNAME      SID    UNDOSEG       SLOT        SEQ LMODE REQUEST TYPE  BLOCK

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

SCOTT         421          7         23       2490     6       0 TX        0

SCOTT         244         10         19       3574     6       0 TX        0

SCOTT         127          8         17       2844     6       0 TX        0

 

Vamos ver novamente o dump do bloco. Com isso, vemos que agora temos três ITLs.

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0007.017.000009ba  0x01000504.02bb.12  —-    1  fsc 0x0000.00000000

0x02   0x000a.013.00000df6  0x010006fc.035e.2a  —-    1  fsc 0x0000.00000000

0x03   0x0008.011.00000b1c  0x01000c33.02a1.05  —-    1  fsc 0x0000.00000000

 

Pontos importantes sobre isso são:

– As ITLs não sofrem shrink. Uma vez criadas, permanecerão no bloco.

– O bloco é criado com duas (ou o valor definido pela INITRANS) e utilizam o espaço livre do bloco caso mais transações concorram no mesmo bloco.

– O valor máximo é de 255, independentemente de haver espaço livre ou não.

Vamos ilustrar também este último ponto. Criamos uma tabela com linhas curtas para que ocupem apenas um bloco e uma tablespace com bloco de 32k.

SQL> alter system set db_32k_cache_size=100M;

System altered.

 

SQL> create tablespace TESTEITL datafile ‘/u01/app/oracle/oradata/orcl/testeitl01.dbf’ size 100M BLOCKSIZE 32K;

Tablespace created.

 

SCOTT@orcl >

CREATE TABLE tb_itl

(col_id  INT,

col_txt VARCHAR2(10)) TABLESPACE TESTEITL;

Table created.

 

SCOTT@orcl >

INSERT INTO tb_itl

SELECT rownum, ‘A’

FROM   dual

CONNECT BY LEVEL <= 300;

300 rows created.

 

SCOTT@orcl > COMMIT;

Commit complete.

 

SCOTT@orcl >

SELECT dbms_rowid.rowid_block_number(ROWID) blk,

dbms_rowid.rowid_relative_fno(ROWID) file_no,

count(*)

FROM   scott.tb_itl

GROUP  BY dbms_rowid.rowid_block_number(ROWID) ,

dbms_rowid.rowid_relative_fno(ROWID);

BLK    FILE_NO   COUNT(*)

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

35          7        300

Ou seja, as 300 linhas estão no mesmo bloco. Um dump inicial irá mostrar apenas 2 ITLs e após nosso teste, veremos 255.

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0020.002.00000002  0x01000772.0000.04  –U-  300  fsc 0x0000.0032f0aa

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

 

Agora vamos fazer uma “manobra” para que sejam alteradas 300 linhas por transações diferentes. O teste consiste em abrir recursivamente uma AUTONOMOUS TRANSACTION. Esperamos que após 255 transações não seja mais possível que isto ocorra. Usaremos a procedure abaixo para isso.

CREATE OR REPLACE PROCEDURE do_update(pnLinha IN NUMBER) AS

PRAGMA        AUTONOMOUS_TRANSACTION;

vrItl         tb_itl%ROWTYPE;

veResBusy     EXCEPTION;

PRAGMA        EXCEPTION_INIT(veResBusy, -54);

BEGIN

–fazemos select for update para lockar a linha com col_id = pnLinha

SELECT *

INTO   vrItl

FROM   tb_itl

WHERE  col_id = pnLinha

FOR    UPDATE NOWAIT;

 

–chamamos recursivamente para update da linha com col_id = pnLinha + 1

do_update(pnLinha + 1);

 

COMMIT;

EXCEPTION

WHEN veResBusy THEN

dbms_output.put_line(‘Nao conseguimos lockar a linha de col_id: ‘ || pnLinha);

COMMIT;

WHEN no_data_found THEN

dbms_output.put_line(‘Todos os registros foram lockados.’);

COMMIT;

END;

/

 

Resultado:

SCOTT@orcl > exec do_update(1);

Não conseguimos lockar a linha de col_id: 256

PL/SQL procedure successfully completed.

 

E por fim, conferindo as ITLs:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0033.002.00000002  0x010008a2.0000.03  –U-    1  fsc 0x0000.0032f7a1

0x02   0x0032.002.00000002  0x01000892.0000.03  –U-    1  fsc 0x0000.0032f7a3

0xfe   0x00fe.000.00000002  0x010017d2.0000.01  –U-    1  fsc 0x0000.0032f645

0xff   0x00ff.000.00000002  0x010017e2.0000.01  –U-    1  fsc 0x0000.0032f643

 

GRANT READ

 

Por fim, vamos falar de um privilégio novo do Oracle 12c relacionado com lock, o GRANT READ. Em versões passadas, tínhamos apenas o grant de SELECT. O problema é que o privilégio de SELECT permite o SELECT FOR UPDATE e embora alguém pudesse apenas “ler” o registro, podia declarar o interesse de alterá-lo, causando o lock.

O Oracle 12c traz esta new feature. Vejamos como funciona, de forma simples. Concedemos privilégio de SELECT para um user e de READ para outro e comparamos o resultado.

SCOTT@orcl > grant select on dept to hr;

Grant succeeded.

 

SCOTT@orcl > grant read on dept to oe;

Grant succeeded.

 

SCOTT@orcl > conn hr/hr

Connected.

 

HR@orcl > select * from scott.dept for update;

 

DEPTNO DNAME          LOC

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

10 ACCOUNTING     SAO PAULO

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

 

HR@orcl > conn oe/oe

Connected.

 

OE@orcl > select * from scott.dept for update;

select * from scott.dept for update

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

CONCLUSÃO

 

Finalizamos mais uma parte da nossa análise e ainda temos bastante assunto pela frente. Falamos de propriedade de ITL e qual a relação entre os locks TX e ITL.

 

Ficarão para os próximos artigos o lock TM e os de DDL, além de outras coisas como leitura consistente, multi versões que uma linha pode ter.

 

 

[1] https://docs.oracle.com/database/121/REFRN/GUID-FAE908F8-24B1-4B90-8FC5-86FCB532431C.htm#REFRN30291

[2] Oracle Database Transactions and Locking Revealed, Thomas Kyte, Darl Kuhn, 2014

 

 

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,