indexing FK to avoid locks

Anthony.P · Sep 14, 2012 10:26AM

Hi,

I've seen many many posts here where guys have troubles with locks created on parent table when editing a child table.
As I can see here: http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref3103 and on this forum, creating index on FK columns will resolve this issue.

I wanted to test this behaviour, but I can't reproduce it. Is there someone who could tell me what I'm doing wrong in this test?

create table t1 (id NUMBER primary key, txt VARCHAR2(10));
create table t2 (id NUMBER primary key, idt references t1(id) );

insert into t1 values (1,'one');
insert into t1 values (2,'two');
insert into t1 values (3,'three');
insert into t2 values (1,1);
insert into t2 values (2,2);
commit;

update t2 set idt=1 where id=2;

As you can see, I have no index. So when I check locks, I can see both table are locked with SX:

Sid/Serial Name             lkType lkMode lkReq  Seconds Blocking?       ID1       ID2 Object
---------- ---------------- ------ ------ ------ -------- --------- --------- --------- ----------------
29,110     PAUL             TX     X                    3               65580       680 T1
29,110     PAUL             TM     SX                   3               16306         0 T1
29,110     PAUL             TM     SS                   3               16304         0 T1
29,110     PAUL             TX     X                    3               65580       680 T2
29,110     PAUL             TM     SX                   3               16306         0 T2
29,110     PAUL             TM     SS                   3               16304         0 T2

Now, I perform the following:

rollback;
create index ii on T2(idt);
commit;
update t2 set idt=1 where id=2;

But then I still have the same locks:

Sid/Serial Name             lkType lkMode lkReq  Seconds  Blocking?       ID1       ID2 Object
---------- ---------------- ------ ------ ------ -------- --------- --------- --------- ----------------
29,110     PAUL             TX     X                    0              524305       889 T1
29,110     PAUL             TM     SX                   0               16306         0 T1
29,110     PAUL             TM     SS                   0               16304         0 T1
29,110     PAUL             TX     X                    0              524305       889 T2
29,110     PAUL             TM     SX                   0               16306         0 T2
29,110     PAUL             TM     SS                   0               16304         0 T2

If I update "id" rather than "idt", then I only see locks on T2 with or without index.

I obviously forgot something or doing something wrong... Could someone help? (I'm using 10.2.0.5)

Thanks!

Charles Hooper · Sep 14, 2012 11:03AM

Anthony,

If you are trying to see the consequences of missing indexes on the child table's declared foreign key columns, you should be looking for blocking TM type locks. For example:

SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.SQL_ID,
  S.SQL_ADDRESS,
  S.SQL_HASH_VALUE,
  S.SQL_CHILD_NUMBER CN,
  S.ROW_WAIT_OBJ#,
  S.ROW_WAIT_FILE#,
  S.ROW_WAIT_BLOCK#,
  S.ROW_WAIT_ROW#,
  L.LMODE,
  L.REQUEST,
  L.ID1,
  L.ID2,
  L.TYPE,
  L.BLOCK
FROM
  V$LOCK L,
  V$SESSION S
WHERE
  (L.ID1, L.ID2, L.TYPE) IN
    (SELECT
      ID1,
      ID2,
      TYPE
    FROM
      V$LOCK
    WHERE
      REQUEST > 0)
  AND L.SID=S.SID;

An example output with 3 sessions involved might look like this:

SID USERNAME PROGRAM     SQL_ID        SQL_ADDR SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST ID1   ID2 TY BLOCK
--- -------- ----------- ------------- -------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ----- --- -- -----
307 USER2    sqlplus.exe 4rtg0hv0atfkx 224E2B7C     3232545373  0            -1              0               0             0     3       5 82913   0 TM     1
314 USER3    sqlplus.exe cv338j6z2530g 224DAE38     3189935119  0            -1              0               0             0     0       3 82913   0 TM     0
320 TESTUSER sqlplus.exe 0vbusv12hnbk6 22480E10     1158295110  0         12517              1           29656             0     3       0 82913   0 TM     1

If you search on the Internet or read various Oracle performance tuning books, you are bound to find several variants of the above SQL statement - some of those variant SQL statement versions simply do not work. Investigating why one of these variants does not work can be helpful to understanding the information exposed by some of the Oracle performance views. Below is a link to one of my articles were various people discussed why a variant of the SQL statement might produce misleading information:
http://hoopercharles.wordpress.com/2010/06/03/lock-watching-what-is-wrong-with-this-sql-statement/

If you want to work through an example of what happens when indexes are missing on the foreign key columns in the child table, take a look at the following article. On Oracle Database 10.2.0.5 and below the example will trigger a TX enqueue once the TM lock is released, while on 11.1.0.6 and later the example will trigger a deadlock when the TM lock is released:
http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-but-not-on-10g/

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Anthony.P · Sep 14, 2012 3:59PM

Thank you very much Charles, but I'm still doing something wrong since your request display no rows...
By the way, the one I've written is this one:

SELECT
  s.sid ||','||s.serial# AS "Sid/Serial",
  s.username AS "Name",
  l.type AS "lkType",
  decode(l.lmode,1,'none',2,'SS',3,'SX',4,'S',5,'SSX',6,'X',null)  "lkMode",
  decode(l.request,1,'none',2,'SS',3,'SX',4,'S',5,'SSX',6,'X',null)  "lkReq",
  l.ctime "Seconds",
  decode(l.block,1,'Yes',NULL) AS "Blocking?",
  l.id1, l.id2,
  o.object_name "Object"
FROM
  v$lock l,
  v$session s,
  dba_objects o,
  v$locked_object lko
WHERE
  s.sid=l.sid AND
  lko.session_id=s.sid AND
--  (l.request != 0 OR l.block=1) AND
  lko.object_id=o.object_id
ORDER BY l.ctime DESC, o.object_name;

I have to read more carefully your blog...

jgarry · Sep 14, 2012 4:17PM

Maybe you need two sessions to see one get blocked? See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754#3986404442549

Jonathan Lewis · Sep 15, 2012 1:43AM

Anthony wrote:

I've seen many many posts here where guys have troubles with locks created on parent table when editing a child table.

Can you supply links to a couple of articles.
The "foreign key locking" problem is about locks on the CHILD table when you edit (specifically, modify or delete the key) on the PARENT.

You may, as a consequence, see people saying that they can't update the parent when someone else is editing a child, but that's because they want to lock the child (typically in mode 4 (S), possibly mode 5 (SSX)) when the other person is holding an incompatible lock (typically mode 3) on the child.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: <b><em>Oracle Core</em></b>

Charles Hooper · Sep 15, 2012 7:48AM

Anthony,

Thank you for providing the SQL statement that you are using.

Jonathan and Joel have provided very good information to help you. The SQL statement that you posted has some of the same issues as the SQL statement I found in a book (referenced in the first of the blog articles I previously provided). I believe that it is a key point that an entry in the V$LOCK view is insufficient to state that the session will block another session. As mentioned by Jonathan, there are different lock modes (NULL (1), SS (2), SX (3), S (4), SSX (5), and X (6)) for the various lock types (TX, TM, etc.) - some of those lock modes (and type combinations) will potentially block other sessions from accomplishing a specific task. For example, if one session modifies a row in a table, the session will take a TM lock on the table, which will prevent another session from dropping that table but not necessarily block another session from inserting, updating, or deleting a row in the table. The lock mode compatibility (whether one session will block another) is found in the documentation here:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm#sthref3243

You will need at least 2 sessions for your experiments to see the potential issues of missing foreign key indexes on the child tables. As Jonathan clearly stated, it is the child table that will be the determining factor in the blocking table lock if a suitable index is not present on that table when the parent table is modified (specifically when the primary and/or unique key columns in the parent table are modified or possibly when a row is deleted from the parent table).

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Anthony.P · Sep 17, 2012 2:55AM

Thank you all,

Thanks to your help, I could reproduce this issue. I do need 2 sessions (what I did before but didn't mentioned here) but I was doing a wrong update to watch the issue.

Session #2:
insert into t2 values(3,1);

Session #1:
update t1 set id=4 where id=3;
-- (hanging)

Where t1(id) is PK. Before, I was performing updates like "update t1 set idt='Three' where id=3;" which can run without problem.

Thanks again, and I'm going to write a more accurate script to display locks.

Forum Stats

Quick Links

Categories

indexing FK to avoid locks

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

indexing FK to avoid locks

Answers