something very strange

Hi all

can anyone tell me how something like this can happen. i have a package that was created under user a, and executed under user b. the package writes to table c.
i have tow guys looking at table c after the process finished, one of them excuted the process and the other one not. they bouth enter the databes as user b. when they look at table c, the one that ren the process can see the new row, the other one can not. only if he reconnects to the database he can see the new row!

can anyone tell me why?

thanks

I guess you haven't commited ...

It could be a reason when the first b had connect to the process the commit has not propagated to the database level, and when the second user connects it would have got. Some times if it was user b1 is doing the insert, then he would be able to see the inserted new data, b'cos the session hasn't performed the commit on his session. On other words the data is still sitting on the user's RBS, which is why the user who inserted the data was able to see. Until he does and explicit commit, or he exits the session when the commit is performed when there is no explicit commit, the other user b wouldn't be able to see the new change. Hope this would fit your problem.

Sam

sorry, he commit is in the package itself, no wayno commit was done!!!

On such cases, there seems to be some kind of lock being held on the table that prevents the user from looking into it. When he reconnects, it sees to get resetted. This would be my assumption. Other thing would be that you might not have the parallel querry option on this table.

Hope this would shed some light.
Sam

Hi sambavan

first of all, thanks.
second, can you tell me where i can look for the parallel querry option on this table?

First of all could you, tell me what is your Oracle version. and platform. Secondly, go ahead and check the DBA_LOCKS, V$LOCKS, V$LOCK_ACTIVITY , V$LOCKED_OBJECT and V$LOCKS_WITH_COLLISIONS to see what is exactly happening.

On 8I, parallel query will be enabled by default. In the mean time post, on what you see.

Sam

Hi sambavan

i work on v7.3. and i could not find V$LOCKS_WITH_COLLISIONS ? is it possible?

Quote:

---

Originally posted by sambavan
On such cases, there seems to be some kind of lock being held on the table that prevents the user from looking into it. When he reconnects, it sees to get resetted. This would be my assumption. Other thing would be that you might not have the parallel querry option on this table.

Hope this would shed some light.
Sam

---

AFAIK in Oracle there is no way any kind of lock could prevent users to read the locked table. "Readers newer block writers, writers never block readers..." is Oracle's well known sintagma.

So you'd better not look in that dirrection, the problem must be somewhere else.

it could well be if transaction mode is serializable. Then even commted rows are not seen by the user. serializable mode is quite usefull (for example, to make aconsistent export, etc)

Hi LND



exept for looking at the code itself (wich is warped) is there a way to find out that i have a row inserted in a serializeble mode?

looking at the documentation i see no way of that.

Wrong Advise : Removed by Editor

[Edited by uday on 03-09-2001 at 05:48 PM]

Sam,

Again I think that async operations have nothing to do with that. Once the transaction is commited both users have to read that row from the same buffer from cache, no matter how data is written to disks.

I think LND has pointed to more likely couse of this mistery.

serializable mode is not for inserts, it is for reading:

a session in serializable mode does not see commits(i.e. commited data) done by other sessions.

The commands are
SET TRANSACTION ISOLATION_LEVEL SERIALIZABLE and
SET TRANSACTION ISOLATION_LEVEL READ_COMMITTED

The mode is valid until transaction ends(commit, rollback, ddl). The SET TRANSACTION must be first statement of transaction (after lastcommit, rollback, ddl)

Exception is if all instance is running in serializable mode (quite hard to believe). Check init.ora parameters (isolation_level I believe)

Jurij, LND, thanx for fixing my missconceptions. Guss what? I'm still learning.

That was one good piece of information. :D

Sam

You can set the isolation level for a transaction (with SET TRANSACTION) and for a session (with ALTER SESSION).
If INIT.ORA parameter SERIALIZABLE is set to TRUE the isolation level is set to SERIALIZABLE for the instance. Oracle recommends not to change the default value (FALSE) for the SERIALIZABLE parameter. It is provided for sites that must run in ANSI/ISO compatible mode, or that want to use applications written to run with earlier versions of Oracle.

Hi guys


first of all, i think i'm crazy becous this is me weekend and i'm reading your answers at home... thank you all and i have to say that not only sambavana is the one stuying hear.

any way, i have to get back to the office to be able to get to check on all of your advices. just whanted to let you know that i'm not answering you all becouse thwe db is far away.

Hi guy's

dont know why, but everything is o.k. now!

thank you all again