Locks são mecanismos necessários para a proteção de dados, pois com início dos sistemas computacionais multi-usuários, torna-se necessário a criação de formas que impeçam um usuário de atualizar ou apagar um determinado registro ao mesmo tempo.
Nesse post vamos tratar especificamente sobre Locks de TM: Locks
São usados principalmente para fazer concorrência com operação simultâneas DDL, como a prevenção de uma tabela que não pode ser apagada no meio de uma operação DML (Insert, Delete, Update, … ) e Querys. Quando há uma instrução DDL incidindo sobre uma tabela, um bloqueio é adquirido.
Uma transação adquire bloqueio de tabela quando a tabela é modificada nas seguintes declarações DML (Insert, Delete, Update e Select for Update), essas operações necessitam de bloqueios para 2 propósitos:
a) Reservar o acesso DML para a tabela em nome de uma transação
b) Evitar operações DDL que entrariam em conflito com a transação
Segundo o Tuning Guide: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm
A causa mais comum para TM Locks é a falta de índices em colunas que são chaves estrangeiras (Foreign Keys Constraints).
Nesse ponto, temos que pensar em como descobrir as chaves estrangeiras (não indexadas), mais informações sobre esse tipo de constraint pode ser encontrado no post Modelagem, Integridade e Normalização.
A note: ID 16428.1 do metalink nos mostra como:
a) Criar a tabela:
CREATE TABLE foreign_key_exceptions
(owner VARCHAR2(30),
constraint_name VARCHAR2(30),
status VARCHAR2(8),
table_name VARCHAR2(30),
foreign_key VARCHAR2(2000));
b) Esse script popula a tabela criada no passo (a):
Declare
pl_cons_column VARCHAR2(30);
pl_foreign_key VARCHAR2(2000);
pl_ind_column VARCHAR2(30);
pl_ind_name VARCHAR2(30);
pl_ind_owner VARCHAR2(30);
pl_index VARCHAR2(2000);
CURSOR c1 IS SELECT constraint_name,owner,table_name,status
FROM dba_constraints
WHERE constraint_type=’R’;
CURSOR c2(cons_name VARCHAR2,cons_owner VARCHAR2) IS SELECT column_name
FROM dba_cons_columns
WHERE constraint_name=cons_name
AND owner=cons_owner
ORDER BY dba_cons_columns.position;
CURSOR c3(ind_table varchar2,tab_owner varchar2) IS
SELECT index_name, owner
FROM dba_indexes
WHERE table_name=ind_table
AND table_owner=tab_owner;
CURSOR c4(ind_name varchar2,ind_owner varchar2) IS
SELECT column_name
FROM dba_ind_columns
WHERE INDEX_NAME=ind_name
AND INDEX_OWNER=ind_owner
ORDER BY dba_ind_columns.column_position;
BEGIN
FOR c1_rec in c1 LOOP
pl_cons_column := NULL;
pl_foreign_key := NULL;
pl_ind_column := NULL;
pl_ind_name := NULL;
pl_ind_owner := NULL;
pl_index := NULL;
OPEN c2(c1_rec.constraint_name,c1_rec.owner);
FETCH c2 INTO pl_cons_column;
pl_foreign_key := pl_cons_column;
LOOP
FETCH c2 into pl_cons_column;
EXIT WHEN c2%NOTFOUND;
pl_foreign_key := pl_foreign_key||’,’||pl_cons_column;
END LOOP constraint_names;
CLOSE c2;
OPEN c3(c1_rec.table_name,c1_rec.owner);
LOOP
FETCH c3 INTO pl_ind_name,pl_ind_owner;
EXIT WHEN c3%NOTFOUND;
OPEN c4(pl_ind_name,pl_ind_owner);
FETCH c4 INTO pl_ind_column;
pl_index := pl_ind_column;
IF pl_index=pl_foreign_key THEN
CLOSE c4;
EXIT index_name;
END IF;
IF pl_index = SUBSTR(pl_foreign_key,1,LENGTH(pl_index)) THEN
LOOP
FETCH c4 INTO pl_ind_column;
EXIT WHEN c4%NOTFOUND;
pl_index:= pl_index||’,’||pl_ind_column;
IF pl_index=pl_foreign_key
THEN
CLOSE c4;
EXIT index_name;
END IF;
IF pl_index != SUBSTR(pl_foreign_key,1,LENGTH(pl_index))
THEN
EXIT index_columns;
END IF;
END LOOP index_columns;
END IF;
CLOSE c4;
END LOOP index_name;
CLOSE c3;
IF pl_index != pl_foreign_key OR pl_index IS NULL THEN
INSERT INTO foreign_key_exceptions VALUES
(c1_rec.owner,c1_rec.constraint_name,c1_rec.status,
c1_rec.table_name,pl_foreign_key);
COMMIT;
END IF;
END LOOP;
END;
/
c) Executar o SQL abaixo para verificar as FKs não-indexadas:
A partir daí é só indexá-las e terminamos com mais esse pequeno problema que pode afetar muitas aplicações, causando famosos problemas de desempenho.
Mais sobre os tipos de locks odem ser encontrados em: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2066
Good Bye.
José Eduardo Fiamengui Júnior
oradeep 