Enqueue ها کنترل دسترسی همزمان به یک شی واحد توسط چند نفر را بر عهده دارند تا به شی مورد نظر آسیبی وارد نشود و یکپارچگی آن حفظ شود پس از این نظر یک قابلیت مفید برای پیشبرد کار ما هستند ولی باید ساختار بانک اطلاعاتی طوری باشد که رقابت دو فرد برای دسترسی به یک شی به حداقل ممکن برسد تا wait هم کمتر رخ دهد. نمونه ای از waitای که بواسطه مدیریت Enqueue ایجاد می شود، enq: TM – contention می باشد که در اینجا سعی داریم تا علل رخ دادن آن و نحوه جلوگیری از آنرا به طور مختصر بیان کنیم.

enq: TM – contention معمولا به دو دلیل اصلی زیر رخ می دهد(البته دلایل دگر هم دارد):

1. عدم ایندکس گذاری بر روی کلید خارجی در جدول child
2. direct load insert

همچنین این نوع از wait سبب می شود تا lock در سطح جدول ایجاد شود(table level lock(TM) که منظور از TM همان table modification می باشد.

 حال دو علت اصلی این wait را با مثال تبین خواهیم کرد.

unindexed foreign keys

drop table usef_child;

drop table usef_parent;

create table usef_parent(a number primary key,b number unique);

create table usef_child(c number,b_fk ,CONSTRAINT b FOREIGN KEY (b_fk) REFERENCES usef_parent(b) ON DELETE CASCADE);

begin

for i in 1..10 loop

insert into usef_parent values(i+1,i+1);

commit;

end loop;

end;

سناریوی اول:

زمانی که چند کاربر قصد دارند رکوردهای جدول parent را حذف کنند و کاربر اول commit یا rollback نکرده که جدول child اجازه همزمانی این کار را نمی دهد:

Session 1:	Session 2:
delete usef_parent where a=10 1 rows deleted(without commit);
	delete usef_parent where a=6 waite….

select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in  (‘WAITING’) and wait_class != ‘Idle’ and event=’enq: TM – contention’ and (q.sql_id = s.sql_id  or q.sql_id = s.prev_sql_id));

SID	SQL_TEXT
129	begin :id := sys.dbms_transaction.local_transaction_id; end;
129	delete usef_parent where a=6

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where  blocking_session is  not NULL order by blocking_session;

BLOCKING_SESSION	SID	SERIAL#	WAIT_CLASS	SECONDS_IN_WAIT
99	129	14	Application	814

سناریوی دوم:

نفر اول رکوردی را از جدول child حذف کرده ولی commit یا rollback نکرده و در همین حال فردی می خواهد از جدول parent رکوردی را حذف کند که در حالت انتظار قرار می گیرد.

Session 1:	Session 2:
delete usef_child where b_fk=2 1 rows deleted(without commit);
	delete usef_parent where a=10 waite….

راه حل:

معمولا در سیستم ها دلیل اصلی رخ دادن این wait در mode 3، به عدم ایندکس گذاری بر روی کلید خارجی بر می گردد و به همین دلیل توصیه می شود که برای همه کلیدهای خارجی، ایندکس گذاری صورت پذیرد.

create index usef_fk1 on SYS.USEF_CHILD(B_FK);

Session 1:	Session 2:
delete usef_child where b_fk=2 1 rows deleted(without commit);
	delete usef_parent where a=10 1 rows deleted;

با استفاده از اسکریپت زیر، می توانیم تمامی کلیدهای خارجی ایندکس گذاری نشده را شناسایی کنیم:

SELECT * FROM (SELECT c.table_name, cc.column_name, cc.position column_position FROM   user_constraints c, user_cons_columns cc WHERE  c.constraint_name = cc.constraint_name AND    c.constraint_type = ‘R’ MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM   user_indexes i, user_ind_columns ic WHERE  i.index_name = ic.index_name ) ORDER BY table_name, column_position;

همچنین پارامتر dml_locks سبب می شود که این  هر کسی بخواهد در این wait قرار بگیرد، کارش با خطا متوقف شود:

alter system set dml_locks=0 scope=spfile;

Session 1:	Session 2:
delete usef_child where b_fk=2; (without commit)
	delete usef_parent where a=10; ORA-00062: DML full table lock cannot be acquired; DML_LOCKS is 0

روش دیگر غیرفعال کردن TM lock در سطح جدول قابل انجام است:

ALTER TABLE usef_child DISABLE TABLE LOCK;

Session 1:	Session 2:
delete usef_child where b_fk=2 1 rows deleted(without commit);
	delete usef_parent where a=10 1 rows deleted;

مزیت دیگر ایندکس گذاری کلید خارجی:

زمانی که از ویژگی on delete cascade در هنگام ساخت کلید خارجی استفاده کردیم و در عین حال بر روی کلید خارجی ایندکسی نساخته باشیم، باید یک عملیات full-table scan بر روی جدول child صورت بگیرد تا رکورد در حال حذف شناسایی شود همچنین زمانی که از عبارت on delete restrict استفاده شود، بازهم در صورت نبود ایندکس، باید یک full table scan بر روی جدول child انجام بپذیرد.

همچنین ساخت ایندکس بر روی foreign key سبب بهینه تر شدن عملیات join بین جدول parent و child هم می شود.

در صورتی که سه شرط زیر برقرار باشد، نیازی به ساخت ایندکس بر روی کلید خارجی نخواهیم داشت:1. مقادیر unique/primary key بروزرسانی نمی شوند 2. از جدول parent رکوردی حذف نمی شود3. بین دو جدول parent و child، هیچگونه joinای رخ نمی دهد.

معمولا اگر دو شرط زیر برقرار باشد، سبب می شود تا جدول child در سطح جدول قفل شود:

1.کلید خارجی ایندکس نداشته باشد.

2. فردی بر روی کلید اصلی در جدول parent عملیات delete، update را انجام داده باشد.

direct load insert

create table usef(a number,b number );

begin

for i in 1..10 loop

insert into usef values(i+1,i+1);

commit;

end loop;

end;

Session 1:	Session 2:
insert /*+ APPEND */ into usef select * from usef; 10 rows inserted(without commit);
	insert /*+ APPEND */ into usef select * from usef; wait….