How to solve the locking problem?

I've got a strange problem with table locking in Oracle 7.3.4 running on AIX
4.

We've got a large(ish) batch job which runs doing a number of transactions
on a number of tables which locks the affected rows in the tables.
The problem is that it often seems to completely lock other users out of the
tables so that they can't do any work on other customers/accounts which are
nothing to do with the batch job being executed.

Can anyone suggest what might be causing this problem, what might be done
about it, or even which views/tables might give more information about where
the problem lock is.

Ans1:
may be, if you have foreing keys in the table, oracle recomends index to put
the lock in index rather than row.
Extrange thing.... the explanation is in oracle server application
developer,s guide chapter 6
Reply ---------
Thanks for this suggestion - I checked it out but it seems to be OK.
Foreign keys in the relevant tables appear to be covered by indexes.
In one case where the FK was a component of the PK of the child, I tested
the effect of inserting to the child (without commit), then updating the
parent table - the update worked as long as the row in the parent table
corresponding to the inserted child row was not updated

Ans2:
You don't say whether you use a cursor to do the transactions.

If you are not using cursor, then your update statement probably looks like
this:

update table1 set x = ....
where .........

then the entire table is locked.

You should use the following lock statement before the above update
statement.

lock table table1
in share update mode

in which case, I think, the table and the row being updated are locked but
others can query the table. It has been a while that I had this problem,
and this is how I resolved it.
--------------------------------------------------------------------
If you are using cursors then, use

update table1 set x = ...., ......
where ........
current of

Oracleguru

Hosted by www.Geocities.ws