Oracle Locks – Parte I – "Isso já eu sabia"

Desde o inicio que qualquer Oracle implementa mecanismos de lock para lidar com problemas de concorrência e de consistência na Base de Dados. Aceder a dados simultâneamente enquanto é possível dar a outras sessões dados  consistentes, permitindo coerência na leitura e escrita dos dados permanece a função mais importante de uma Base de Dados. Sem estar a querer entrar pelo mundo teórico do modelo ACID, vou apenas demonstrar alguns pontos pertinentes dos mecanismos de Lock no Oracle.

Vou usar 2 sessões e analisar os respectivos locks à medida das operações que vou fazendo. Para facilitar a compreensão vou também explicando:

SQL> create table t1_locks as select rownum as N1, dbms_random.string('A',2) S1 from dual connect by level <= 5;

Table created.

SQL> select sid from v$session where audsid=userenv('SESSIONID');

SID
----------
40

SQL> select sid from v$session where audsid=userenv('SESSIONID');

SID
----------

29

SQL> column s1 format a3;
SQL> select * from t1_locks;

N1 S1
---------- ---
1 PN
2 fP
3 Yo
4 Ur
5 KX

 

Na SID 40 será feito um insert:

SQL> insert into t1_locks (N1,S1) values(6,'SL');

1 row created.

Sem o commit executado veremos o que vê a sessão com o ID 29:

 

SQL> select * from t1_locks;

N1 S1
———- —
1 PN
2 fP
3 Yo
4 Ur
5 KX

 

Naturalmente a SID 29 não verá as alterações feitas pela SID 40, pois não existe commit. Veremos o que se passou
no Oracle ao nível do mecanismo de locking:


SQL> select sid,type,id1,lmode,request from v$lock where sid in (40,29);

SID TY ID1 LMODE REQUEST
---------- -- ---------- ---------- ----------
40 AE 100 4 0
29 AE 100 4 0
40 TM 74204 3 0
40 TX 458771 6 0

 

Os “AE” são para desprezar (significa basicamente Application Edition lock, 11g apenas), mas os outros não. “TX” representa Transaction Lock, ou Transaction Enqueue e “TM” significa DML ou Table lock (DML Enqueue), ou seja, o TX  representa a transação, é usado principalmente para prevenir que uma outra transacção modifique o mesmo registo, assim cada vez que um transacção necessita de modificar um registo adquire um TX. O “TM” é usado principalmente para gerir mecanismos de concorrência de operações DDL, como por exemplo, tentar fazer “drop” a uma tabela durante uma operação de insert (ou outro qualquer DML)
A coluna LMODE representa o modo de lock.
Na “TM” o modo de lock será row-exclusive (row-X, mode 3), ou seja, apenas os registos inseridos, enquanto do ponto de vista da “TX” o modo de lock será exclusive, já que o “TX” se refere à propria transacção.

Podemos de uma forma simples verificar se o lock “TM” está aplicado na tabela:


SQL> select name from sys.obj$ where obj#=74204;

NAME
------------------------------
T1_LOCKS

Confirma-se que existe um lock no objecto T1_LOCKS.
Já vimos que a SID 29 não consegue ver as alterações feitas pela SID 40, pois não houve lugar a commit.
O próximo passo é inserir um registo com a SID 29 e fazer update a um já existente (N1=1). Vejamos:

 


SQL> insert into t1_locks (N1,S1) values(7,'XX');

1 row created.


SQL> update t1_locks set S1='BB' where N1=1;

1 row updated.

Não será feito commit. Analisaremos os locks agora referentes às duas sessões:

 

SQL>  select sid,type,id1,lmode,request from v$lock where sid in (40,29);

SID TY ID1 LMODE REQUEST
---------- -- ---------- ---------- ----------

40 TM 74204 3 0
29 TM 74204 3 0
29 TX 393239 6 0
40 TX 458771 6 0

 

Tudo faz sentido, 1 lock de cada tipo para cada SID diferente. As SID 29 e 40 estão a fazer “Table Lock” com row-S e ambas estão a trazer “Transaction Lock” para os registos inseridos e modificados. Até agora cada sessão consegue apenas visualizar aquilo que inseriu e/ou modificou. Vejamos:

SID 40:


SQL> select * from t1_locks; [Inclui INSERT do N1=6]

N1 S1
---------- ---
1 PN
2 fP
3 Yo
4 Ur
5 KX
6 SL

 

SID 29:


SQL> select * from t1_locks; [Inclui INSERT do N1 = 7 e UPDATE do N1=1]

N1 S1
---------- ---
1 BB
2 fP
3 Yo
4 Ur
5 KX
7 XX

 

Vamos piorar as coisas. Vamos na SID 40 tentar fazer update na tabela para N1=1, tal e qual fizemos na SID 29:


SQL> update t1_locks set S1='CC' where N1=1;
 

A sessão está bloqueada, não existiu lugar a update, até que a SID 29 faça commit/rollback das alterações. Podemos ver isto em “directo”:

SQL>  select event, seconds_in_wait, sid from v$session_wait where sid in (40,29);

EVENT SECONDS_IN_WAIT SID
-------------------- --------------- ----------
SQL*Net message from 246 29
client

enq: TX - row lock c 105 40
ontention

A coluna SECONDS_IN_WAIT permite-nos saber À quanto tempo o lock espera, ou seja, o evento “row lock contention” não é mais que uma sessão que espera por um row lock feito por outra sessão. No nosso caso o modo 6 (coluna LMODE) indica mesmo que para resolver este problema deverá ser feito um rollback ou commit dos dados. Na SID 29 faremos o commit:


SQL> commit;

Commit complete.

 

Entretanto na SID 40:


SQL> update t1_locks set S1='CC' where N1=1;

1 row updated.

 

Faremos agora um select * a ambas as tabelas:

SID 40:

SQL> select * from t1_locks;

N1 S1
---------- ---
1 CC
2 fP
3 Yo
4 Ur
5 KX
7 XX
6 SL

7 rows selected.

SID 29:


SQL> select * from t1_locks;

N1 S1
---------- ---
1 BB
2 fP
3 Yo
4 Ur
5 KX
7 XX

O resultado é simples de interpretar. Enquanto que a SID 40, vê as alterações efectuadas pela SID 29, o contrário não acontece pois ainda não foi feito qualquer commit na SID 40. De notar que o valor para N1=1 é diferente em ambas as sessões já que na SID 40 foi feito o update para CC mas sem commit, sendo isto apenas visto pela SID 40 e não pela SID 29. De notar ainda que a SID 40 contêm um registo [N1=7] que foi inserido pela SID 29. Para terminar este exemplo bastante simples vamos ver de novo o estado dos locks:


SQL> select sid,type,id1,lmode,request from v$lock where sid in (40,29);

SID TY ID1 LMODE REQUEST
---------- -- ---------- ---------- ----------
40 TM 74204 3 0
40 TX 458771 6 0

Portanto apenas a SID 40 ainda detêm locks pois não foi feito qualquer rollback ou commit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s