SQL em Oracle > DML > Bloqueios
Bloqueios tl_logo2.jpg

Numa base de dados os dados são consultados e alterados concorrentemente pelos vários utilizadores. O que acontece se dois utilizadores tentarem alterar o mesmo dado ao mesmo tempo? O primeiro a chegar activa um bloqueio (LOCK) que só é retirado quando a sua transacção terminar. A segunda transacção terá que esperar que o bloqueio seja retirado, o que só acontece quando a primeira transacção termina. Neste módulo vamos ver:


Bloqueio em actualização de dados

O exemplo abaixo mostra como uma actualização de dados entre duas sessões diferentes activa um bloqueio:

Tempo Sessão 1 Sessão 2 Comentário
1
drop table teste;
create table teste (
   id number(10,0),
   nome varchar2(10),
   constraint pk_teste primary key (id)
);
Na sessão 1 removemos a tabela TESTE (caso exista) e recriamo-la. São dois comandos DDL, sendo cada um deles uma transacção com COMMIT implícito. Depois do COMMIT implícito do segundo comando inicia-se uma nova transacção na sessão 1.
2
insert into teste (id,nome) 
    values (1,'aaaaaa');
insert into teste (id,nome) 
    values (2,'bbbbbb');
insert into teste (id,nome) 
    values (3,'cccccc');
commit;
A sessão 1 insere várias linhas na tabela TESTE e confirma as alterações.
3
select * from teste;
update teste 
   set nome='AAAAAA' 
   where id=1;
select * from teste;
A sessão 1 inicia uma transacção que muda o nome da linha 1. Esta alteração não é confirmada e portanto a transacção não termina. A linha recebe um bloqueio (LOCK) que se manterá activo enquanto a transacção não terminar.
4
select * from teste;
update teste 
   set nome='BBBBBB' 
   where id=2;
select * from teste;
A sessão 2 inicia uma transacção que muda o nome da linha 2. Esta alteração não é confirmada e portanto a transacção não termina.
5
update teste
   set nome='aaaAAA'
   where id=1;
Dentro da mesma transacção iniciada no passo anterior a sessão 2 tenta mudar o nome da linha 1. No passo 3 a sessão 1 bloqueou o acesso a esta linha, pelo que a sessão 2 fica à espera que a sessão 1 levante o bloqueio.
6
commit;
A sessão 1 termina a transacção e retira o bloqueio.
7
select * from teste;
A sessão 2 fica desbloqueada e continua a transacção, fazendo a alteração.
8
select * from teste;
A sessão 1 ainda não vê as alterações feitas pela sessão 2 porque estas não foram confirmadas.
9
commit;
A sessão 2 termina a transacção confirmando as alterações.

bthome.gifTopo


Bloqueio infinito (DEAD LOCK)

O ponto anterior mostra que uma transacção que pretenda alterar um dado que está bloqueado fica à espera que o LOCK seja libertado. Este facto potencia situações em que duas transacções concorrentes ficam à espera uma da outra indefinidamente, o que é conhecido por "DEAD LOCK" e está ilustrado no exemplo abaixo:

Tempo Sessão 1 Sessão 2 Comentário
1
drop table teste;
create table teste (
   id number(10,0),
   nome varchar2(10),
   constraint pk_teste primary key (id)
);
Na sessão 1 removemos a tabela TESTE (caso exista) e recriamo-la. São dois comandos DDL, sendo cada um deles uma transacção com COMMIT implícito. Depois do COMMIT implícito do segundo comando inicia-se uma nova transacção na sessão 1.
2
insert into teste (id,nome) 
    values (1,'aaaaaa');
insert into teste (id,nome) 
    values (2,'bbbbbb');
insert into teste (id,nome) 
    values (3,'cccccc');
commit;
A sessão 1 insere várias linhas na tabela TESTE e confirma as alterações.
3
update teste 
   set nome='AAAAAA' 
   where id=1;
A sessão 1 inicia uma transacção que muda o nome da linha 1. Esta alteração não é confirmada e portanto a transacção não termina. A linha recebe um bloqueio (LOCK) que se manterá activo enquanto a transacção não terminar.
4
update teste 
   set nome='BBBBBB' 
   where id=2;
A sessão 2 inicia uma transacção que muda o nome da linha 2. Esta alteração não é confirmada e portanto a transacção não termina. A linha recebe um bloqueio (LOCK) que se manterá activo enquanto a transacção não terminar.
5
update teste 
   set nome='bbbBBB' 
   where id=2;
A sessão 1 tenta alterar a linha 2 que está bloqueada pela sessão 2 e por isso fica à espera.
6
update teste 
   set nome='aaaAAA' 
   where id=1;
A sessão 2 tenta alterar a linha 1 que está bloqueada pela sessão 1 e por isso fica à espera.
7
update teste
       *
ERROR at line 1:
ORA-00060: deadlock detected while 
           waiting for resource
As duas sessões estão à espera uma da outra, situação que se manteria eternamente, não fosse a intervenção da base de dados que detecta e termina o comando UPDATE da sessão 1.
8
select * from teste;
A transacção da sessão 1 ainda não terminou. O primeiro UPDATE mantém-se mas o segundo foi abortado. O LOCK sobre a linha 1 continua a bloquear a sessão 2. A sessão 1 pode decidir abortar o trabalho ou tentar de novo.
9
rollback;
Na transacção da sessão 1 o primeiro comando correu bem, mas o segundo correu mal, pelo que foi decidido cancelar a transacção e portanto os dados voltam à versão inicial.
10
select * from teste;
Logo que a sessão 1 liberta a linha 1 a sessão 2 prossegue a transacção em curso, alterando a linha com ID 1;
11
select * from teste;
A sessão 1 ainda não vê as alterações feitas pela sessão 2 porque estas não foram confirmadas;
12
commit;
A transacção da sessão 2 termina e os respectivos LOCKS são libertados. A sessão 1 já pode ver as alterações;

note04.gif

bthome.gifTopo


SELECT FOR UPDATE

O comando SELECT possui a cláusula FOR UPDATE que permite activar um LOCK sobre todas as linhas seleccionadas pelo comando. Este LOCK impede que outras sessões da base de dados alterem estas linhas enquanto a transacção actual não terminar. Este comando permite bloquear linhas antes de as alterar, mas deve ser usado com muita cautela, pois enquanto o LOCK está activo os outros utilizadores não podem fazer alterações nessas linhas, o que pode gerar tempos de espera (contenção).

O exemplo abaixo mostra como usar a cláusula FOR UPDATE:

Tempo Sessão 1 Sessão 2 Comentário
1
drop table teste;
create table teste (
   id number(10,0),
   nome varchar2(10),
   constraint pk_teste primary key (id)
);
Na sessão 1 removemos a tabela TESTE (caso exista) e recriamo-la. São dois comandos DDL, sendo cada um deles uma transacção com COMMIT implícito. Depois do COMMIT implícito do segundo comando inicia-se uma nova transacção na sessão 1.
2
insert into teste (id,nome) 
    values (1,'aaaaaa');
insert into teste (id,nome) 
    values (2,'bbbbbb');
insert into teste (id,nome) 
    values (3,'cccccc');
commit;
A sessão 1 insere várias linhas na tabela TESTE e confirma as alterações.
3
select id, nome 
  from teste 
  where id=1 
  for update;
A sessão 1 inicia uma transacção executando o comando SELECT com a cláusula FOR UPDATE. A base de dados coloca um LOCK sobre a linha com ID 1. Note que no resultado do comando SELECT não há nenhuma indicação de que foi usada a cláusula FOR UPDATE;
4
select id, nome 
  from teste 
  where id=1 
  for update;
A sessão 2 inicia uma transacção executando o mesmo comando SELECT com a cláusula FOR UPDATE. A sessão 2 vai ficar bloqueada esperando que a sessão 1 liberte o LOCK;
5
update teste
  set nome='AAAAAA'
  where id=1;
A sessão 1 altera os dados da linha 1, enquanto a sessão 2 continua à espera;
6
commit;
A sessão 1 termina a transacção e liberta o LOCK. A sessão 2 retoma o trabalho bloqueando a linha 1;
7
commit;
A sessão 2 termina a transacção e liberta o LOCK;

No exemplo anterior a sessão 2 ficou bloqueada quando tentou aceder a um recurso que tinha um LOCK. A sessão 2 não recebe uma estimativa do tempo de espera, pois este depende apenas da sessão 1. A opção NO WAIT pode ser adicionada à cláusula FOR UPDATE, permitindo a interrupção imediata do comando SELECT FOR UPDATE quando o recurso acedido se encontra bloqueado. O exemplo abaixo mostra a sua utilização:

Tempo Sessão 1 Sessão 2 Comentário
1
drop table teste;
create table teste (
   id number(10,0),
   nome varchar2(10),
   constraint pk_teste primary key (id)
);
Na sessão 1 removemos a tabela TESTE (caso exista) e recriamo-la. São dois comandos DDL, sendo cada um deles uma transacção com COMMIT implícito. Depois do COMMIT implícito do segundo comando inicia-se uma nova transacção na sessão 1.
2
insert into teste (id,nome) 
    values (1,'aaaaaa');
insert into teste (id,nome) 
    values (2,'bbbbbb');
insert into teste (id,nome) 
    values (3,'cccccc');
commit;
A sessão 1 insere várias linhas na tabela TESTE e confirma as alterações.
3
select id, nome 
   from teste 
   where id=1 
   for update;
A sessão 1 inicia uma transacção executando o comando SELECT com a cláusula FOR UPDATE. A base de dados coloca um LOCK sobre a linha com ID 1.
4
select id,nome
  from teste
  where id in (1,2)
  for update nowait;
from teste
     *
ERROR at line 2:
ORA-00054: resource busy and acquire 
           with NOWAIT specified
A sessão 2 inicia uma transacção executando um comando SELECT com a cláusula FOR UPDATE. Este comando selecciona duas linhas, tendo uma delas um LOCK. Como foi usada a opção NOWAIT a execução do comando é abortada de imediato.
5
select id,nome
  from teste
  where id in (1,2)
  for update of teste.nome wait 10;
from teste
     *
ERROR at line 2:
ORA-30006: resource busy; acquire 
           with WAIT timeout expired
A sessão 2 executa o mesmo comando que no exemplo anterior, mas desta vez usa as opções OF e WAIT. A primeira opção é muito útil quando o query envolve várias tabelas (JOIN), pois evita o bloqueamento de todas as linhas de todas as tabelas envolvidas. A segunda opção permite definir um tempo de espera, em segundos, pela libertação do LOCK.
6
commit;
A sessão 1 termina a transacção e liberta o LOCK.

bthome.gifTopo


Bloqueio e integridade referencial

Se existir um relacionamento via Foreign Key entre uma tabela Main e uma tabela Child, a base de dados vai gerar bloqueios especiais em Child sempre que Main sofre INSERT, UPDATE ou DELETE sobre a PK, que serão descritos no exemplo abaixo:

Tempo Sessão 1 Sessão 2 Comentário
1
drop table tbChild cascade constraints;
drop table tbMain cascade constraints;

create table tbMain (
  id_tbMain number constraint 
           pk_tbMain primary key,
  nome varchar2(100) not null
);

create table tbChild (
  id_tbChild number constraint 
            pk_tbChild primary key,
  nome varchar2(100) not null,
  id_tbMain number constraint 
            fk_tbChild references 
            tbMain(id_tbMain)
);
Na sessão 1 removemos as duas tabelas (caso existam) e recriamo-las. A tabela tbChild tem uma restrição FK que "aponta" para a primeira tabela.
2
insert into tbMain (id_tbMain,  nome) 
  values (1,'aaaaaa');
insert into tbMain (id_tbMain,  nome) 
  values (2,'bbbbbb');
insert into tbMain (id_tbMain,  nome) 
  values (3,'cccccc');
insert into tbMain (id_tbMain,  nome)
  values (4,'dddddd');

insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (1,'AAAAAA', 1);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (2,'AAABBB', 1);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (3,'AAACCC', 1);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (4,'BBBAAA', 2);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (5,'BBBBBB', 2);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (6,'BBBCCC', 2);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (7,'CCCAAA', 3);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (8,'CCCBBB', 3);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (9,'CCCCCC', 3);
commit;
A sessão 1 insere várias linhas nas duas tabelas e termina a transacção.
3
update tbMain 
  set id_tbMain=5
  where id_tbMain=1;
Error report:
SQL Error: ORA-02292: 
    integrity constraint 
   (HR.FK_TBCHILD) violated 
   - child record found
A sessão 1 tenta alterar a PK de um registo na tabela tbMain. A BD vai procurar na tabela tbChild registos que referenciem esse valor da PK, tendo encontrado pelo menos um, o que impede a alteração. Como a coluna FK não tem um indice, a procura é feita por "full table scan". Enquanto o "full table scan" estiver a decorrer a base de dados coloca um bloqueio na tabela tbChild que impede UPDATE e DELETE sobre esta tabela. Para acelerar a pesquisa podemos criar um índice sobre a coluna FK.
4
update tbMain 
  set id_tbMain=6 
  where id_tbMain=4;
A sessão 1 tenta alterar a PK de um registo na tabela tbMain. Na tabela tbChild não existem registos com valor 4 na coluna id_tbMain, pelo que a alteração pode ser feita, embora não seja confirmada.
5
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (12,'AAAEEE', 1);
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (13,'DDDAAA', 4);
A sessão 2 insere com sucesso um registo em tbChild porque o registo relacionado em tbMain tem o id 1. O segundo insert fica bloqueado porque o registo relacionado tem o id 4, cujo valor está "mutante", ou seja, depende do resultado da transacção da sessão 1 (instante 4). O bloqueio da sessão foi provocado por um LOCK criado pelo INSERT.
6
commit;
A sessão 1 confirma a alteração do passo 4.
7
Error starting at line 1 in command:
insert into tbChild 
   (id_tbChild,  nome, id_tbMain) 
   values (13,'DDDAAA', 4)
Error report:
SQL Error: ORA-02291: 
   integrity constraint 
   (HR.FK_TBCHILD) violated 
   - parent key not found
O segundo insert dá erro, pois a sessão 1 mudou a PK.
8
commit;
A sessão 2 termina a sua trasacção.
9
insert into tbMain 
  (id_tbMain,  nome) 
  values (5,'eeeeee');
A sessão 1 insere um novo registo na tabela tbMain.
10
insert into tbChild 
  (id_tbChild,  nome, id_tbMain) 
  values (11,'EEEAAA', 5);
A sessão 2 tenta inserir um novo registo em tbChild que se relaciona com o registo "mutante" inserido no passo anterior. A sessão fica bloqueada à espera da confirmação (ou não) do INSERT da sessão 1.
11
commit;
A confirmação da sessão 1 desbloqueia a sessão 2.
12
commit;
Termina a sessão 2.

bthome.gifTopo


Realizado por Turtle Learning ®. Última alteração em 2011-02-26