o look for unindexed foreign keys
o unique/primary key constraints (both inserting same value)



eg:

ops$tkyte@ORA817DEV> create table p ( x int primary key );
Table created.

ops$tkyte@ORA817DEV> create table c ( x references p );
Table created.

ops$tkyte@ORA817DEV> insert into p values ( 1 );
1 row created.

ops$tkyte@ORA817DEV> insert into p values ( 2 );
1 row created.

ops$tkyte@ORA817DEV> insert into c values ( 2 );
1 row created.

ops$tkyte@ORA817DEV> commit;
Commit complete.

ops$tkyte@ORA817DEV> update p set x = 1 where x = 1;
1 row updated.

<b>that'll lock the child table in 8i and below...</b>


ops$tkyte@ORA817DEV> insert into p values ( 3 );
1 row created.

<b>that'll block anyone from inserting into p the value 3</b>


ops$tkyte@ORA817DEV> select SID, TYPE, ID1, ID2, LMODE,
  2        (select object_name from user_objects where object_id=id1) oname,
  3             block
  4    from v$lock
  5   where sid = (select sid from v$mystat where rownum=1 )
  6   or block = 1;

       SID TY        ID1        ID2      LMODE ONAME           BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        10 TM      51210          0          3 P                   0
        10 TM      51212          0          4 C                   0
        10 TX     262198       5165          6                     0

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set echo off
either run:
insert into c values(2)
insert into p values(3)
in another session and then hit / again here...

<b>this is after trying to insert into C:</b>

ops$tkyte@ORA817DEV> /

       SID TY        ID1        ID2      LMODE ONAME           BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        10 TM      51210          0          3 P                   0
        10 TM      51212          0          4 C                   1
        10 TX     262198       5165          6                     0

<b>and then I ctl-c'ed and tried to insert into P</b>


ops$tkyte@ORA817DEV> /

       SID TY        ID1        ID2      LMODE ONAME           BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        10 TM      51210          0          3 P                   0
        10 TM      51212          0          4 C                   0
        10 TX     262198       5165          6                     1

 

thats just a surrogate key.  look at the DATA_OBJECT_ID, that is what I said is really important here -- the object (the data object) no longer exists...

ops$tkyte@ORA920> select object_id, data_object_id from user_objects where object_name= 'T';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     34408          34408

ops$tkyte@ORA920> truncate table t;

Table truncated.

ops$tkyte@ORA920> select object_id, data_object_id from user_objects where object_name= 'T';


 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     34408          34418

 

the number id1 is a 4 byte integer.

there are really two 2 byte numbers hiding in that single field.  So, if the two numbers were 2 and 3, then id1 would be 0x00020003 which is decimal

ops$tkyte@ORA920LAP> select to_number( '20003', 'xxxxx' ) from dual;

TO_NUMBER('20003','XXXXX')
--------------------------
                    131075

We needed the low 2 bytes only, we want '3' as the result.  So, we need to "mask off" the high 2 bytes and just keep the low two bytes.  In order to do that, we need the number such that all of the bits are "on" in the low 2 bytes and off in the high ones.  In binary, that would be:

00000000000000001111111111111111

and in hex

0x0000FFFF

Now, bitand is a function that takes two numbers and performs a classic "bit anding" on them.  that is, given two binary numbers:

01010101010101
01000100011100
---------------
01000100010100  <<<=== the bit and'ed result, only when BOTH bits are on...

so, if we use that number with all 1's on the end, we would only get the bits of the first number from the low two bytes.  so:


ops$tkyte@ORA920LAP> select bitand( 131075, to_number( 'ffff','xxxx') ) from dual;

BITAND(131075,TO_NUMBER('FFFF','XXXX'))
---------------------------------------
                                      3


Now, some releases of sqlplus would not recognize the output of bitand correctly, so I just added 0 to it, that made it a number always and made sqlplus happy.

 

if you have this sequence of events:

a) call stored procedure
b) insert a row in T1
c) trigger on T1 inserts it into T2
d) insert a row in T1 again
e) trigger tries to read any of the rows from (b), (c), (d) using an autonomous
   transaction

(e) is fatally flawed and never worked that way in ANY release.

an autonomous transaction, by its very definition -- by its VERY definition -- is like a separate transaction.  Therefore, it can see

a) data that was committed when it begin
b) data it modifies

since the data in (b), (c), (d) is NOT YET committed, an atran in (e) will not see it.  It is easy to see this behaviour:

ops$tkyte@ORA920> create table t1 ( x int );

Table created.

ops$tkyte@ORA920> create table t2 ( x int );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace procedure autonomous_proc
  2  as
  3          pragma autonomous_transaction;
  4  begin
  5          dbms_output.put_line( 'Enter.....' );
  6
  7          for x in ( select * from t2 )
  8          loop
  9                  dbms_output.put_line( 'data = ' || x.x );
 10          end loop;
 11          dbms_output.put_line( 'Exit.....' );
 12  end;
 13  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace trigger t1_trigger
  2  after insert on t1 for each row
  3  begin
  4          insert into t2 values ( :new.x );
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA920> create or replace trigger t2_trigger
  2  after insert on t2 for each row
  3  begin
  4          autonomous_proc;
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t1 values ( 1 );
Enter.....
Exit.....

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> insert into t1 values ( 2 );
Enter.....
data = 1
Exit.....

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.


<b>and you will observe exactly the same behaviour in 8i</b>

Now, if you believe they are behaving differently -- supply us a full test case as I have -- in <b>as few lines of code as humanly possible -- that is really important -- as small as you can</b>


odds are, you find your mistake in the test case generation -- i usually find mine that way.


 

<quote>
The request column of the waiter will have a value 4 and type 'TX' with id1 and 
id2 pointing to anyone of the blockers (probably the first one). It won't have 
its own TX entry since the transaction did not began at that point of time.
</quote>

well, how could it have a TX record if it doesn't have a TX record? :)  you sort of contradict yourself.

A small test was easy to set up.

In one session:


drop table t;
                                                                                                                    create table t ( x int ) initrans 2 maxtrans 2;
                                                                                                                    insert into t select rownum from all_users;
commit;
select distinct dbms_rowid.rowid_block_number(rowid) from t;
                                                                                                                    update t set x = x where x = 1;


Now, open two more windows and in the second:
update t set x = x where x=2;

and in the third
update t set x=x where x=3;

What I observed was:

ops$tkyte@ORA920> select sid, type, id1, id2, lmode, block, request from v$lock where sid in ( 8, 9, 11 )
  2  /
 
       SID TY        ID1        ID2      LMODE      BLOCK    REQUEST
---------- -- ---------- ---------- ---------- ---------- ----------
         8 TX     131106      68619          6          0          0
         8 TM      59314          0          3          0          0
         9 TX     458777      67872          6          1          0
         9 TM      59314          0          3          0          0
        11 TM      59314          0          3          0          0
        11 TX     458777      67872          0          0          4
 
6 rows selected.


the blocked sid (11) is actually blocked waiting on the SECOND (not the first here -- but that doesn't "prove" it couldn't be the first, just proves that it doesn't have to be the first).

It does have a TX record. 



The blocked fkey is also easy to setup (you all can try these things yourselves!!! you too have the power.  simulations are the way to learn -- it is how i figure out most of the answers here!)


Run this in session 1:

create table p ( x int primary key );
create table c ( x references p );
                                                                                
insert into p values ( 1 );
insert into c values ( 1 );
insert into p values ( 2 );
commit;


In another window, do this:

insert into c values ( 2 );


and then in the first window do this:

update p set x=x where x=2;
 

Now, in a free session, look at v$lock (remember, change the sid's to be the sid's on your system!)
ops$tkyte@ORA920> select sid, type, id1, id2, lmode, block, request from v$lock where sid in ( 8, 9, 11 );
 
       SID TY        ID1        ID2      LMODE      BLOCK    REQUEST
---------- -- ---------- ---------- ---------- ---------- ----------
         8 TM      59330          0          0          0          4
         8 TM      59328          0          3          0          0
        11 TX     262179      69553          6          0          0
        11 TM      59330          0          3          1          0
        11 TM      59328          0          2          0          0

See -- now you can see for yourself exactly what will happen and when.



As for q2 -- data block waits are caused by many many things.  You could be waiting for some other session to read the block into the buffer cache, you could have itl issues, you could be contending for the data block that is the head of the freelist (as was the case in my example in the book).

I removed the contention by having sufficient freelists.  In 9i, you might look at ASSM (automatic segment space management) in order to not have to think about freelists as well.

 

the TM lock is not a "table lock" like "lock table in X mode", it is a lock registered against that segment saying "hey, I've got some locking going on this table".

It would prevent someone from dropping a column you are updating for example.  It is lightweight.

the TX lock isn't on the "table", you get one -- not one per table:

ops$tkyte@ORA9IR2> update emp set ename=ename;
 
14 rows updated.
 
ops$tkyte@ORA9IR2>  select * from v$lock where sid = (select sid from v$mystat where rownum=1);
  
 
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      
-------- -------- ---------- -- ---------- ---------- ---------- ---------- 
56B41D8C 56B41E98         10 TX     393258       9816          6          0          
56AE9D48 56AE9D5C         10 TM      35514          0          3          0          
 
ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> update dept set dname=dname;
 
4 rows updated.
 
ops$tkyte@ORA9IR2>  select * from v$lock where sid = (select sid from v$mystat where rownum=1);
  
 
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      
-------- -------- ---------- -- ---------- ---------- ---------- ---------- 
56B41D8C 56B41E98         10 TX     393258       9816          6          0          
56AE9DCC 56AE9DE0         10 TM      35515          0          3          0          
56AE9D48 56AE9D5C         10 TM      35514          0          3          0          
 


A TM per segment, a TX per transaction.   

Basically the alter table disable table lock is preventing the "high mode" locks -- the ones that prevent other concurrent activity.

An update for example takes a "row exclusive" (mode=3) 'table lock'.  It means "hey, I've got some rows locked in exclusive mode in this table (not really a table lock truly).  as long as you don't try to lock the same rows -- you can have at this table"


A locak table in exclusive mode takes an "exclusive" lock -- it gets them all -- it does this at the table level.  It is a true "table lock"


Consider:

ops$tkyte@ORA9IR2> alter table emp disable table lock;
Table altered.
 

 
ops$tkyte@ORA9IR2> lock table emp in row share mode;
Table(s) Locked.

<b>that is like SELECT FOR UPDATE does.</b>
 
ops$tkyte@ORA9IR2> lock table emp in row exclusive mode;
Table(s) Locked.

<b>That is like UPDATE does</b>
 
ops$tkyte@ORA9IR2> lock table emp in share mode;
lock table emp in share mode
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP
 
 
ops$tkyte@ORA9IR2> lock table emp in share row exclusive mode;
lock table emp in share row exclusive mode
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP
 
 
ops$tkyte@ORA9IR2> lock table emp in exclusive mode;
lock table emp in exclusive mode
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP

<b>those last three are true TABLE LEVEL locks -- locked the entire table.  They are what are prohibited</b>

guess the command should be

alter table t disable "most" table lock; 

update is a little "stronger", consider:


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> lock table emp in share mode;
 
Table(s) Locked.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          for x in ( select ename from emp where rownum = 1 for update )
  5          loop
  6                  dbms_output.put_line( 'locked ' || x.ename );
  7          end loop;
  8          commit;
  9  end;
 10  /
locked SMITH
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          for x in ( select ename from emp where rownum = 1 for update )
  5          loop
  6                  dbms_output.put_line( 'locked ' || x.ename );
  7                  update emp set ename = ename where ename = x.ename;
  8                  dbms_output.put_line( 'updated ' || x.ename );
  9          end loop;
 10          commit;
 11  end;
 12  /
locked SMITH
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 7
 


<b>we are able to "select for update" SMITH, but we are not able to actually update SMITH because of the outstanding table level share lock

(again -- concepts guide, lists all locks, describes all locks, tells us what locks coexist with other locks.  check it out!)</b>
 

a) if you fire this up:

ops$tkyte@ORA9IR2> create materialized view mv
  2  build deferred
  3  refresh on demand
  4  as
  5  select a.object_id id1, b.object_id id2
  6    from all_objects a, all_objects b;
 
Materialized view created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('MV');
 


and in another window do this:

ops$tkyte@ORA9IR2> alter materialized view mv compile;


it'll block (the compile) indefinitely.  the refresh is a "compile" already.

2) and while that is hanging out:

ops$tkyte@ORA9IR2> select
  2        (select username from v$session where sid=a.sid) blocker,
  3         a.sid,
  4        ' is blocking ',
  5         (select username from v$session where sid=b.sid) blockee,
  6             b.sid
  7    from v$lock a, v$lock b
  8   where a.block = 1
  9     and b.request > 0
 10     and a.id1 = b.id1
 11     and a.id2 = b.id2
 12  /
 
BLOCKER                               SID 'ISBLOCKING'
------------------------------ ---------- -------------
BLOCKEE                               SID
------------------------------ ----------
OPS$TKYTE                              12  is blocking
OPS$TKYTE                              11


shows that and we can see:


OPS$TKYTE(11,274) ospid = 4794 command = 0 program =
sqlplus@localhost.localdomain (TNS V1-V3) dedicated server=4795
Wednesday 19:31  Wednesday 19:34 last et = 20227
alter materialized view mv compile
--------------------
OPS$TKYTE(12,99) ospid = 4768 command = 2 program =
sqlplus@localhost.localdomain (TNS V1-V3) dedicated server=4769
Wednesday 19:30  Wednesday 19:34 last et = 110873
INSERT /*+ APPEND */ INTO "OPS$TKYTE"."MV" select a.object_id id
1, b.object_id id2
  from all_objects a, all_objects b

11 is the alter compile, 12 is the refresh...
 

the integrity constraint...

it gets a TM lock with lmode=2, row-S... pretty much the lowest level of locking, but a lock none the less.

these TM locks are done for two reasons:

a) to reserve DML access to the table
b) to prevent DDL on the table.

In this case, b) is relevant to you.


So, you cannot lock it in exclusive mode, but you can in "share" mode:

ops$tkyte@ORA9IR2> create table temp(t integer primary key);
 
Table created.
 
ops$tkyte@ORA9IR2> create table temp2(t references temp(t));
 
Table created.
 
ops$tkyte@ORA9IR2> create index t2_idx on temp2(t);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into temp values(1);
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select type, lmode, object_name
  2    from v$lock, user_objects
  3   where sid = (select sid from v$mystat where rownum=1)
  4     and type = 'TM'
  5     and id1 = object_id
  6  /
 
TY      LMODE OBJECT_NAME
-- ---------- ------------------------------
TM          3 TEMP
TM          2 TEMP2
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          lock table temp2 in exclusive mode;
  5          commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 
 
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          lock table temp2 in SHARE mode;
  5          commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 

it is an SGA thing.

ops$tkyte@ORA9IR2> show parameter enqueue
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enqueue_resources                    integer     968

ops$tkyte@ORA9IR2> show sga
 
Total System Global Area  305730740 bytes
Fixed Size                   451764 bytes
Variable Size             201326592 bytes
Database Buffers          100663296 bytes
Redo Buffers                3289088 bytes


ops$tkyte@ORA9IR2> alter system set enqueue_resources = 100000 scope=spfile;
 
System altered.
 
ops$tkyte@ORA9IR2> connect / as sysdba
Connected.
ops$tkyte@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  322507976 bytes
Fixed Size                   451784 bytes
Variable Size             218103808 bytes
Database Buffers          100663296 bytes
Redo Buffers                3289088 bytes
Database mounted.
Database opened.


ops$tkyte@ORA9IR2> select (322507976-305730740)/1024/1024 from dual;
 
(322507976-305730740)/1024/1024
-------------------------------
                     16.0000191
 
ops$tkyte@ORA9IR2>