something very strange
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: something very strange

  1. #1
    Join Date
    Jul 2000
    Posts
    243
    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

    shawish_sababa

    shawish_sababa@hotmail.com
    Share on Google+

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    I guess you haven't commited ...
    Share on Google+

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  4. #4
    Join Date
    Jul 2000
    Posts
    243
    sorry, he commit is in the package itself, no wayno commit was done!!!
    shawish_sababa

    shawish_sababa@hotmail.com
    Share on Google+

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi sambavan

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

    Share on Google+

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  8. #8
    Join Date
    Jul 2000
    Posts
    243
    Hi sambavan

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

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by sambavan [/i]
    [B]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 [/B][/QUOTE]

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?
    Share on Google+

  10. #10
    Join Date
    Nov 2000
    Posts
    212
    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)
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width