help understand my lock

in an earlier post ( transaction lock (TX) on table block with PK), I showed a 'blocking' Transaction lock on a table.. I was able to get more info (tfslock script), and have compressed the output as much as I could to fit in 80 char line:

User SID Table Name COMMA Lock Held Lock Req'd ID1 - ID2 Lck ---- ---- --------- ------ ------------ ----------- ------------ ---
@lcl 52 None BACKGR Exclusive NONE 131075-6533 TX

          None       BACKGR Row Exclusive NONE       6147-0       TM 


@smaug 23 None       UPDATE NONE          Exclusive  131075-6533  TX 

          TEAM_GROUP UPDATE Row Exclusive NONE       6105-0       TM 
          DIV        UPDATE Row Exclusive NONE       6147-0       TM 
          TEAMS      UPDATE Row Share     NONE       6080-0       TM 
          GROUPS     UPDATE Row Share     NONE       6086-0       TM 


@smaug 58 None       UPDATE NONE          Exclusive  131075-6533  TX 

          TEAM_GROUP UPDATE Row Exclusive NONE       6105-0       TM 
          DIV        UPDATE Row Exclusive NONE       6147-0       TM 
          TEAMS      UPDATE Row Share     NONE       6080-0       TM 
          GROUPS     UPDATE Row Share     NONE       6086-0       TM 

TX - Transaction enqueue lock
TM - DML enqueue lock

6147 is object-id of DIV
The first session (@lcl) did a
select * from div where id=1 for update;

the other sessions are trying to update div.other_column (via a trigger on team_group table, among other things as visible for an extensive list of locks held).

initially i thought maybe other sessions are trying to update same rows, but there is no 'row excluvive' waits, nor was there any information in v$session_wait on waits on row (row_wait_row# = 0 in v$session), there was however row_wait_block#, which always seemed to be block 8210.

8210 seems to be the first (non-header) block of the DIV table

system_at_NCAA> select header_block from dba_segments where segment_name='DIV';

HEADER_BLOCK

        8209

There is a DIV_PK index on DIV
Index is not referenced by other objects.

Another thing I don't fully understand, is why tfslock script is not showing table_name in @lcl session.

please no comments on poor data model ;)

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Fri Mar 17 2006 - 15:13:04 CST