Master Yavor from Tryavna

Презентацията ми „The adventure of upgrading to 11.2“ от есенния семинар на БГПО в Хисар – тук

Отново изразявам моите огромни благодарности и възхищение на Милена, СПИ и другите организатори, които инвестират много от личното си време и енергия за да направят тези семинари. Страхотна работа, отново!

Това не го очаквах. Обикновено с новите версии на Oracle нещата скалират по-добре; има по-малко заключваници от предишните версии. Това, което ще опиша, е ново за 11g

Някой би казал, че това е некадърен дизайн на приложението. Всеки знае, че foreign key колоните трябва да се индексират. Обаче в 10g това водеше до по-бавно изтриване от parent таблицата, или по-бавен update на primary key в нея (докато се сканира child таблицата за съответни записи). А сега имаме изцяло ново поведение – заключване, което може да продължи произволно дълго време. Един прост INSERT в parent таблицата може да блокира всички други сесии, които се опитват да направят UPDATE или DELETE (пак на parent таблицата). Тук изобщо не говорим за промяна в child таблицата – достатъчно е да я има, ако ще и празна. И за да ни е по-весело, ние настъпахме тая мотика с child таблица от 724 реда, на която full scan-а в 10g е минавал светкавично и не сме усетили никакъв проблем.

* * *

Ето и подробностите. Note 1343365.1 казва следното

There is change in lock modes behavior introduced intentionally by fix 5909305 in 11.1 onward.

Change has been made to DML (TM) lock modes for foreign key constraints (Doc ID 5909305.8).

The change in behavior mentioned in note 5909305.8 , introduces higher level of lock on parent table while running DML against Child table. ( SX vs SS )

There are cases unrelated to this bug which lack of indexes on foreign key columns increase the time locks take place. This reflect as wait on TM-enq.

Звучи безобидно. Поведението на SS и SX е почти еднакво. Има 2 разлики – SX не е съвместим с други S и SSX (Share и Share Sub-eXclusive) заключвания. Но тия двете не се срещат в ежедневната работа с DML заявки. Почти.

Тук се включва неиндексирания foreign key. Това е единствения DML за който знам, който прави S заключване на цялата child таблица. И така, със следните прости стъпки аз успявам да се заключа всеки път на 11.2.0.2:

1. Подготвям си таблици и данни

drop table t2 purge;
drop table t1 purge;

create table t1 (pk number primary key);

create table t2 (pk number primary key, fk number references t1(pk));

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);

insert into t2 values (11, 1);
insert into t2 values (21, 2);
insert into t2 values (22, 2);
insert into t2 values (31, 3);
insert into t2 values (32, 3);
insert into t2 values (33, 3);

commit;

2. Залагам капана от сесия 1

insert into t1 values (5);

Кратък поглед във v$lock показва, че нашата сесия 1 държи 3 заключваници (всъщност малко повече в 11g, но трите са най-интересни)
– mode 6 (eXclusive) TX lock върху новия ред
– mode 3 (Sub eXclusive) TM lock върху таблицата t1 (демек parent таблицата). обърнете внимание, че това е заключване на таблицата, а не на някой ред в нея
– mode 2 (Sub Share) при 10g или Mode 3 (Sub eXclusive) при 11g, TM lock на таблица t2 (това е child таблицата). Отново това не е свързано с никакви редове в нея – може да има, може и да няма засегнати редове. Такива заключвания има за всички таблици, които имат foreign key constraint, сочещ към нашия parent.

3. Опитваме да променим или изтрием произволен (друг) ред от parent таблицата от сесия 2:

delete from t1 where pk=4;

или

 update t1 set pk=2 where pk=2;

В 10g това минава безгрижно. Обаче в 11g и двете зависват, опитвайки се да получат Mode 4 (Share) lock на child таблицата – защото foreign key колоната не е индексирана.

Как да си намерим неиндексираната таблица:
FВсъщност аз забелязвам сесиите, чакащи за TM locks в Grid Control. Там излизат едни червени неща, лесно се виждат. Може да ги видим и във V$SESSION:

 SQL> select sid, final_blocking_session
  2    from v$session
  3   where event = 'enq: TM - contention';
 
       SID FINAL_BLOCKING_SESSION
---------- ----------------------
       132                    332

За да намерим проблемния ресурс (с други думи – таблицата без индекс), пускаме това:

 SQL> select SID, type, id1, lmode, request, block
  2    from v$lock
  3   where sid in (132, 332)
  4     and type = 'TM';
 
       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       132 TM       950744          3          0          0
       132 TM       950746          0          4          0
       332 TM       950744          3          0          0
       332 TM       950746          3          0          1

На ред номер 4 пише BLOCK=1, т.е. това заключване *блокира* някой друг. На ред номер 2 виждаме REQUEST=4 и LMODE=0 – това показва, че сесията има нужда от mode 4 lock, обаче не може да се уреди. Колоната ID1 в случая е object ID (за TM locks):

 SQL> select owner, object_name, object_type
  2    from dba_objects
  3   where object_id = 950746;
 
OWNER      OBJECT_NAM OBJECT_TYPE
---------- ---------- -------------------
YAVOR      T2         TABLE

Решението е лесно, след като сме открили проблема. Просто създаваме индекс на foreign key колоната от child таблицата

 create index t2_fk on t2(fk);

След създаването на индекс, промяната върху parent таблицата вече не прави S заключване на child. Вместо това се прави SX заключване, което е съвсем съвместимо с другия SX.

Това поведение е трудно за обяснение на develpers. Честно казано, дори и аз не мога да си обясня как индекса премахва нуждата от S lock и го намаля на SX. Ние не засягаме никакви редове в child таблицата. Ама… така са го направили.