Minimizing System Contention ( 12- Blocking Lock Olayını Tesbit Etme )

12- Blocking Lock Olayını Tesbit Etme

  - Kullanıcılarınız oturumlarının bazılarının çok yavaş olduğundan şikayet ediyor.
  Bu oturumların Oracle tarafından locklanmış olabileceğinden şüpheleniyorsunuz ve bu oturumları kimin elinde tuttuğunu bulmak istiyoruz.
  
  - Oracle, veritabanında yıkıcı davranışları önlemek için, birden çok oturum tarafından yürütülen işlemleri kontrol etmek için çeşitli lock türleri kullanmaktadır.
  Bir blocking lock, bir oturumu "yavaşlatabilir" ,aslında oturum yalnızca bir nesne üzerinde lock (tek satır veya bir dizi satır veya bir tablonun tamamında) tutan başka bir oturumu bekliyor demektir.
  Veya bir development senaryosunda, bir developer birden çok oturum başlatmış olabilir ve bazıları birbirini engelliyor.

  - Oracle locklarını analiz ederken incelemeniz gereken lock veritabanı viewlarının bazıları V$LOCK ve V$SESSION dir.
  Locklı objeleri tanımlamak için V$LOCKED_OBJECT ve DBA_OBJECTS viewleri de çok yararlıdır. Bir oturumun başka bir oturuma uyguladığı tler tarafından engellenip engellenmediğini öğrenmek için aşağıdaki sorguyu uygulayabilirsiniz:

  - Bir oturumun başka bir oturum ile "lock" ile engellenip engellenmediğini bulmak için aşağıdaki sorguyu çalıştırın :

  SQL> select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' ) is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;

  BLOCKING_STATUS
  --------------------------------------------------------------------
  HR@MIRO\MIROPC61 ( SID=68 ) is blocking SH@MIRO\MIROPC61 ( SID=81 )

  - Sorgunun çıktısı blocking session yanı sıra tüm blocked sessions larıda gösterir.

  - Herhangi bir user için instance da herhangi bir  "blocking locks" olup olmadığını öğrenmenin hızlı bir yolu :
 
   SQL> select * from V$lock where block > 0;
 
  - Bu sorguda herhangi bir satır almazsanız, şu anda instance da herhangi bir "blocking locks" yok!

  Nasıl Çalışır:

  - Oracle yıkıcı davranışı(destructive behavior) önlemek için iki tür kilit kullanır: exclusive ve shared locklar.
  Aynı obje üzerinde birden çok shared lock elde edilebilinirken yalnızca bir transaction bir satır veya tablo üzerinde exclusive lock elde edilebilir.
  Oracle kilitleri, row ve tablo düzeyleri olmak üzere iki düzeyde kullanır. TX simgesi ile gösterilen row kilitleri, yalnızca INSERT, UPDATE ve DELETE gibi bir DML ifadesi tarafından değiştirilecek her satıra ait bir tablo satırı kilitler.
  Bu, aynı zamanda bir merge veya bir SELECT ... FOR UPDATE deyimi için de geçerlidir. Bu ifadelerden birini içeren işlem, exclusive lock kilidinin yanı sıra row share table kilidini de kapar.
  Transaction (ve oturum), bu kilitleri commit edilene veya roll back yapana kadar tutar.
  Bu iki şeyden birini yapana kadar, belirli bir satırı değiştirmeyi düşünen diğer tüm oturumlar blocklanır.
  Bir transaction un tablonun satırlarını veya satırlarını değiştirmeyi planladığı seferde,
  o tablonun üzerinde tablo lock (TM) tutar ve bu tablonun herhangi bir DDL işlemine (örneğin DROP TABLE) izin vermez.
 
  Oracle database de, "locking" şu şekilde çalışır:
  • Bir okuyucu, başka bir okuyucuyu engellemeyecektir.
  • Bir okuyucu bir yazıcıyı engellemeyecektir.
  • Bir yazıcı, aynı veriler üzerindeki okuyucuyu engellemeyecektir.
  • Bir yazıcı, aynı veriyi değiştirmek isteyen başka bir yazıcıyı engelleyecektir.

- Yukarıdaki listede son durum, iki session bir tabloda aynı verileri değiştirmeyi planladığında
Oracle'ın otomatik kilitlemesini yıkıcı davranışı(destructive behavior) önlemek için başlatır.
Varolan bir satırı update edecek ilk transaction, o satırda exclusive lock alır.
Bir satırı kilitleyen ilk oturum, (COMMIT veya ROLLBACK işlemi yapana kadar) bu kilidi tutmaya devam ederken, diğer oturumlar bu tablodaki kilitlenmiş satır dışındaki diğer satırları değiştirebilir.
Birinci oturumda tutulan table lock, yalnızca diğer oturumların tablo yapısını değiştirmek için bir DDL ifadesi yayınlamasını önlemeyi amaçlamaktadır.
Oracle, karmaşık bir kilitleme mekanizması kullanır; bu sayede, row-level lock otomatik olarak tabloya veya blok seviyesine yükseltilmez,row bazında kalır.