Oracle 11g: New Locking behavior (modes) … an unexpected behavior. Or?

I just noticed some strange locking behavior in one of our production databases.

It took me a while to reproduce this in test. I’ve based my example on Richard Foots blog “Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)”.

My test case:

CREATE TABLE l_countries (id NUMBER PRIMARY KEY, country_name varchar2(30), note varchar2(30));
CREATE TABLE l_artists (id NUMBER PRIMARY KEY, artist_name VARCHAR2(30), country_id NUMBER CONSTRAINT artist_country_fk REFERENCES l_countries(id));
CREATE TABLE l_companies (id NUMBER PRIMARY KEY, company_name VARCHAR2(30), country_id NUMBER CONSTRAINT company_country_fk REFERENCES l_countries(id));
CREATE TABLE l_albums (id NUMBER, album_name VARCHAR2(30), 
                       artist_id NUMBER CONSTRAINT album_artist_fk REFERENCES l_artists(id), 
                       company_id number CONSTRAINT albums_company_fk REFERENCES l_companies(id));

CREATE SEQUENCE l_countries_seq START WITH 3 INCREMENT BY 1;

INSERT INTO l_countries VALUES (1,'NORWAY','Dummy');
INSERT INTO l_countries VALUES (2,'ENGLAND','Dummy');

INSERT INTO l_artists VALUES (1,'Aha',1);
INSERT INTO l_artists VALUES (2,'The Beatles',2);

INSERT INTO l_companies VALUES (1,'No Sound',1);
INSERT INTO l_companies VALUES (2,'Music Nonstop',2);

INSERT INTO l_albums VALUES (1,'Take on me',1,1);
INSERT INTO l_albums VALUES (2,'Let it be',2,2);
commit;

We then do some testing with two different sessions.

Session 1:

SQL> delete from l_albums where id=10;

0 rows deleted

-- not commited yet

Session 2:

update l_countries c set c.note='TST' where id=1;

1 rows updated

Everything looks just fine. But then we create a trigger on the L_COUNTRIES table:

CREATE OR REPLACE TRIGGER L_COUNTRIES_BIU
   BEFORE INSERT OR UPDATE ON L_COUNTRIES FOR EACH ROW ENABLE
DECLARE
BEGIN
   IF INSERTING THEN
      IF (:NEW.ID IS NULL) OR (:NEW.ID = 0) THEN
         SELECT L_COUNTRIES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
      END IF;
   END IF;
END;
/

And do the test again.

Session 1:

SQL> delete from l_albums where id=10;

0 rows deleted

-- not commited yet

Session 2:

update l_countries c set c.note='TST' where id=1;

-- HANGS (blocked)!!!

Creating indexes on the foreign keys in  l_companies and l_artists tables will remove the blocking.

create index l_company_country_fk on l_companies(country_id);
create index l_artist_country_fk on l_artists(country_id);

But still … I was a bit surprised by the previous result. The code in the trigger is enclosed in a “IF INSERTING …” block. I’m doing a UPDATE, not an INSERT. I’m not actually changing any primary key values, or deleting any primary key values in the parent table here. Is this expected behavior? Feel free to comment on this.

Post a Comment

Your email is never published nor shared. Required fields are marked *