Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

indexing FK to avoid locks

Anthony.PSep 14 2012 — edited Sep 17 2012
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!

Comments

unknown-7404
You should not do this.

Environment variables such as PATH can only give preference to one item of any given name. With DLLs for example the first DLL of any given name that is found will be the one loaded by Windows. It would be up to the software doing the loading to determine if that is the correct version and most software just looks by name.

That is what is known as 'dll hell'; the wrong version of a dll is being used.

You don't need both clients installed so why do you want to do that?
Pl identify which version of Win 7 - you will need Professional or higher. Pl also identify which exact version of Oracle client.

I have no personal experience with this myself, but the 11gR2 docs say you can install both the 32-bit and 64-bit clients as long as you use different ORACLE_BASE directories.

http://docs.oracle.com/cd/E11882_01/relnotes.112/e23557/toc.htm#CJADGJDB

HTH
Srini
Helios-GunesEROL
Hi;

By using different path yes you can install.

Please review:
Master Note For Oracle Database Client Installation [ID 1157463.1]
Client / Server / Interoperability Support Between Different Oracle Versions [ID 207303.1]


Regard
Helios
941843
Windows 7 Enterprise

Oracle Client 11g (11.2.0.1.0)
941843
Doesn't this write both ORACLE_HOME paths to the PATH environment variable, and then cause, as mentioned above, DLL hell? With WOW64, it is unclear to me how Microsoft separates 32-bit paths from 64-bit paths.

Unfortunately, my company is running 32-bit Microsoft Office 2007 and other 32-bit programs that use the Oracle thick client install, and my company also uses 64-bit programs that require the Oracle thick client install too.

The solution thus far has been to uninstall one of the programs, and thus clean-up the %PATH% environment variable; or rearrange the order of the Oracle client homes in %PATH% to make the broken one work.
941843
After researching the links, this was still unclear if the 2 clients would conflict with each other.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 15 2012
Added on Sep 14 2012
6 comments
460 views