Oracle Database Discussions

Hi folks,

Dev ppl is runing a process that inserts into and updates a given same table. This table has no index, and it is a temp table created into a permanent tablespace. This could be messy, but there are reasons for this to be like that so for now please take it as it is.

What I observed is that there is an insert-select with parallel that locks in exclusive mode an entire table while an update tries to access to a row. The result is that the update must wait for the insert to finish, without the apparently need.

Firstly, I tried creating an index on the mentioned table so the update didn't have to make a full scan. It didn't work, same behaviour.

Secondly, I asked the dev guy to run the process without the parallelization, and the process ended up without locking. The insert lock mode wasn't exclusive.

So, I would say either we don't use the parallel feature correctly, or this is a bug.

Any ideas?

Details:

- Oracle 11.1.0.7 RAC (4 nodes)
- Suse 10
- Dev guys activate parallel option with an ALTER SESSION.

Thanks in advance.