Enq: TM Lock Contention
- Veritabanınızdaki birkaç oturum, bazı insert ifadelerini işlemek için çok uzun zaman alıyor.
Sonuç olarak, "active" session sayısı çok yüksek ve veritabanı yeni oturum bağlantılarını kabul edemiyor. Kontrol ettiğimizde, veritabanının bir çok "enq: TM - contention wait" olayı yaşadığını fark ettiniz.
- Insert işlemlerini gerçekleştirmek için bekleyen oturumların "enq: TM – contention event" olayındaki beklemeler, genelde indexsiz foreign key kısıtlamasından kaynaklanmaktadır.
Bu, bir parent tabloya bağımlı veya child tablonun foreign key kısıtlamasında ilişkili keylerde bir index eksik olduğunda ortaya çıkar.
Oracle, child tablodaki foreign key tarafından başvurulan parent tablodaki PK sütununda değişiklikler yapıyorsa, child tablo üzerinde bir tablo kilidi alır.
Bunların tam tablo kilitleri (TM) olduğunu ve satır düzeyi kilitleri (TX) olmadığını unutmayın; bu kilitler bir satırla sınırlı değil, tablonun tamamında görülür.
Doğal olarak, bu tablo kilidi elde edildiğinde Oracle, child tablonun verilerini değiştirmeye çalışan tüm diğer oturumları engeller. Üst tablonun sütununa başvuran child tabloya bir index oluşturduktan sonra, "TM contention" nedeniyle bekleyişler kaybolacaktır.
Nasıl Çalışır:
- Tablodaki foreign key kısıtlamalarını indexlemezsiniz, child tabloda exclusive lock alır.
Indeksllenmemiş foreign key kilitleme nedeniyle contention ile sonuçlanacağını göstermek için aşağıdaki örneği kullanın. Burada gösterildiği gibi iki table (STORES ve PRODUCTS) oluşturun:
SQL> create table stores
(store_id number(10) not null,
supplier_name varchar2(40) not null,
constraint stores_pk PRIMARY KEY (store_id));
SQL>create table products
(product_id number(10) not null,
product_name varchar2(30) not null,
supplier_id number(10) not null,
store_id number(10) not null,
constraint fk_stores
foreign key (store_id)
references stores(store_id)
on delete cascade);
- STORES tablosundaki herhangi bir satırı silerseniz, kilitleme nedeniyle beklemeleri farkedeceksiniz. PRODUCTS tablosunda foreign key olarak belirttiğiniz sütunda yalnızca bir index oluşturarak bu beklemelerden kurtulabilirsiniz:
create index fk_stores on products(store_id);
- Aşağıdaki sorgu ile veritabanınızda indexli tüm foreign key kısıtlamalarını bulabilirsiniz:
SQL> select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;
- Bir foreign key sütununa index eklemezseniz, child table sıklıkla kilitlenir, dolayısıyla contention ile ilgili beklemelere neden olur. Oracle, foreign keylere daima index eklemenizi önerir.
Tip: Alt tablodaki foreign keye bağlı unique veya primary key asla güncelleştirilmez veya silinmezse , alt tablodaki foreign key sütununu indexlemek zorunda değilsiniz
- Oracle, foreign key sütununu index eklemezseniz child tabloda bir tablo kilit elde etme eğiliminde olacaktır.
Parent tabloya bir satır eklerseniz, parent tablo child tabloda bir kilit elde etmez; Ancak, parent tabloda bir satır güncelleştirir veya silerseniz, child tabloda kilit meydana gelecektir. Diğer bir deyişle, üst tablodaki primary keydeki herhangi bir değişiklik, alt tabloda full table lock (TM) ile sonuçlanır. Örneğimizde STORES tablosu, STORE_ID foreign key içeren PRODUCTS tablosunun üst öğesidir. Değişken bir tablo olan PRODUCTS tablosundaki STORE_ID sütununun değerleri,
üst tablo olan STORES tablosunun unique veya primary key değerleriyle eşleşmelidir.
Bu durumda, STORES tablosundaki STORE_ID sütunu, o tablonun primary keyidir.
- Üst tablonun (STORES) primary keyini değiştirdiğinizde, veritabanı, PRODUCTS tablosunda full table lock alır.
Diğer oturumlar, foreign key sütunundan başka sütunlar da dahil olmak üzere, PRODUCTS tablosunda herhangi bir değeri değiştiremez. Oturumlar yalnızca PRODUCTS tablosunu sorgulayabilir ancak değiştiremez.
Bu süre zarfında, PRODUCTS tablosundaki herhangi bir sütunu değiştirmeye çalışan herhangi bir oturumun beklemesi gerekecek (TM: enq contention wait). Oracle, alt tablodaki (PRODUCTS) kilidi yalnızca ana tablodaki (STORES) primary keyi değiştirmeyi tamamladıktan sonra serbest bırakacaktır.
PRODUCTS tablosundaki verileri değiştirmek için bekleyen bir sürü oturumunuz varsa, hepsi beklemek zorunda kalacaklar ve kısa DML işlemlerini gerçekleştiren birçok kullanıcısı olan bir online transaction processing türü veritabanına sahipseniz, active session sayısı doğal olarak çok hızlı yükselir.
Alt tabloda gerçekleştirdiğiniz tüm DML işlemlerinin, üst tabloda bir tablo kilidi gerektirmediğini unutmayın.