Re: Locking Problem

From: Oracleguru, Suresh Bhat <oracleguru_at_mailcity.com>
Date: Tue, 02 Mar 1999 18:01:12 GMT
Message-ID: <01be64e6$fc38f740$a504fa80@mndnet>

Hi

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
www.oracleguru.net
oracleguru_at_mailcity.com

Shad <aaa_at_bbb.com> wrote in article <7bgceh$aef_at_romeo.logica.co.uk>...
> Hi,
> 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.
>
> Thanks for your help.
>
>
>
>
>
Received on Tue Mar 02 1999 - 12:01:12 CST