Home » SQL & PL/SQL » SQL & PL/SQL » How oracle locks child table with foreign keys? (oracle 91)
How oracle locks child table with foreign keys? [message #377057] Fri, 19 December 2008 12:01 Go to next message
mallikchandrima
Messages: 2
Registered: December 2008
Junior Member
TAB1
=====

a1 b1 c1
a2 b2 c2
a3 b3 c3


TAB2
=====
x1 y1 z1 a1
x2 y2 z2 a2
x3 y3 z3 a3
(FK indexed)

TAB3
=====
p1 q1 r1 y1
p2 q2 r2 y2
p3 q3 r3 y3
(FK indexed)



Transaction 1 for session1
select * from TAB1 where col1 = a1 for update nowait;

doing activities on tab2 and tab3 depending on the foreign key joins in loop.


Transaction 2 for session 2
select * from TAB1 where col1 = a2 for update nowait;

doing activities on tab2 and tab3 depending on the foreign key joins in loop.



1) Please let me know whether these sessions can head towards deadlock.



2) If some session try to update TAB3 row of y1 when session 1 is still working , how will system behave?
will it go for blocked session or go for dead lock or get an exception message?

Re: How oracle locks child table with foreign keys? [message #377058 is a reply to message #377057] Fri, 19 December 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do YOU think what will happen? Why?
We don't do homework for you but we can help you.

By the way, you didn't tell us what the PK and FK. Hey, you didn't tell us what are the columns!

Regards
Michel

Re: How oracle locks child table with foreign keys? [message #377078 is a reply to message #377058] Fri, 19 December 2008 23:04 Go to previous messageGo to next message
mallikchandrima
Messages: 2
Registered: December 2008
Junior Member
TAB1 
===== 
col1   col2   col3
a1     b1      c1 
a2     b2      c2 
a3     b3      c3 
(pk)

TAB2 
===== 
col1  col2  col3  col4
x1     y1    z1    a1 
x2      y2    z2   a2 
x3      y3     z3  a3 
(PK)   (Uk)       (FK to TAB1 col1)(Indexed)

TAB3
=====
col1  col2  col3  col4
p1     q1    r1    y1
p2     q2    r2    y2
p3     q3    r3    y3
(PK)              (FK to TAB2 col2)(Indexed)

Transaction 1 for session1
select * from TAB1 where col1 = a1 for update nowait;

doing activities on tab2 and tab3 depending on the foreign key joins in loop.


Transaction 2 for session 2
select * from TAB1 where col1 = a2 for update nowait;

doing activities on tab2 and tab3 depending on the foreign key joins in loop.

1) Please let me know whether these sessions can head towards deadlock.

2) If some session try to update TAB3 row of y1 when session 1 is still working , how will system behave?
will it go for blocked session or go for dead lock or get an exception message?

I am posting this message because I am facing blocked session and dead lock issues.I am trying to find out rootcause.This may be the reason...but I cud not find any thing regarding this...so,I need help from experts.


[Edit MC: add code tags to align table columns, do it yourself next time]

[Updated on: Sat, 20 December 2008 00:57] by Moderator

Report message to a moderator

Re: How oracle locks child table with foreign keys? [message #377084 is a reply to message #377078] Sat, 20 December 2008 01:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Have you triggers? Deadlock traces show you the reason of deadlock.

2) It depends on the overall locks, it blocks maybe with or maybe without deadlock

Regards
Michel


Previous Topic: Table and Procedure scritps from SQL (merged)
Next Topic: PRAGMA
Goto Forum:
  


Current Time: Sat Dec 03 20:07:58 CST 2016

Total time taken to generate the page: 0.08010 seconds