Bloqueios |
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. |
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; |
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. |
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. |
Realizado por Turtle Learning ®. Última alteração em 2011-02-26