This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Sep 22, 2010 2:41 AM by Franck Pachot RSS

Data reverts back on commit

799084 Newbie
Currently Being Moderated
I'm trying to update the data in one of my tables. I launch SQLPlus, do a SELECT to see the old data, run an UPDATE statement and then do a SELECT again to verify that the change is done. However, once I run COMMIT and then run my SELECT again, I don't see my change. It seems to be the case that there's some other connection that's holding a lock on that table or something which isn't allowing me to save on commit. There's nothing in the database logs specifically for this table.

I tried running some queries I found to identify which process may have a lock on this table or something, but couldn't find anything. The column that I'm updating in this table does not have any referential constraints whatsoever.

Any ideas how I can identify what the problem is and what might be the culprit here ?

This is a 11g database if that helps

Edited by: user11960166 on Sep 20, 2010 5:24 PM
  • 1. Re: Data reverts back on commit
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    Quite interesting and weird problem. Can you please do following and paste the output here?
    create table test (id number);
    
    insert into test values(1);
    
    insert into test values(2);
    
    insert into test values(3);
    
    select * from test;
    
    commit;
    
    select * from test;
    
    update test set id=10 where id=1;
    
    select * from test;
    
    commit;
    
    select * from test;
    With this, please also do same sequence of operation on your own table on which you are having problem and paste here.

    Salman

    Edited by: Salman Qureshi on Sep 21, 2010 8:41 AM
  • 2. Re: Data reverts back on commit
    sb92075 Guru
    Currently Being Moderated
    Any ideas how I can identify what the problem is and what might be the culprit here ?
    I suspect Problem Exists Between Keyboard And Chair
  • 3. Re: Data reverts back on commit
    Vladimir Zakharychev Journeyer
    Currently Being Moderated
    What you describe is quite impossible. I mean, if you update a row, see it updated, then commit, receive no errors and the update is rolled back instead of being committed, this means that very basic and essential functionality of the database is broken. Since it is impossible, please make sure your test case is correct. Or post it here (real output, not some fiction.)
  • 4. Re: Data reverts back on commit
    799084 Newbie
    Currently Being Moderated
    Here's the SQLPlus output from my terminal. If I run "show errors" after the commit, I don't see any errors.

    SQL> UPDATE job_requests set released_date = SYSDATE where id in (161, 195);

    2 rows updated.

    SQL> select released_date from job_requests where id in (161, 195);

    RELEASED_
    ---------
    20-SEP-10
    20-SEP-10

    SQL> commit;

    Commit complete.

    SQL> select released_date from job_requests where id in (161, 195);

    RELEASED_
    ---------



    SQL>
  • 5. Re: Data reverts back on commit
    Aman.... Oracle ACE
    Currently Being Moderated
    Its simply not possible except that you have hit some bug. Can you create a new user and do some operations within it and show us?

    As Sys user, do this,
    SQL>grant dba to newuser identified by newuser;
    SQL>conn newuser/newuser
    SQL>create table test(a number);
    SQL>insert into test values(1);
    SQL>commit;
    SQL>select * from test;
    Show us the output of above commands in the same order.

    HTH
    Aman....
  • 6. Re: Data reverts back on commit
    SalmanQureshi Expert
    Currently Being Moderated
    Hello,
    SQL> select released_date from job_requests where id in (161, 195);
    
    RELEASED_
    
    
    SQL>
    This is something weird. Here your statement should show "now rows selected" but i think it is still selecting two rows and showing you NULL in result set. Please perform that tasks in the sequences i suggested in my previous post.

    If you have some other tool like sql developer, can you perform update and select there on the same table and check?

    Salman
  • 7. Re: Data reverts back on commit
    Vladimir Zakharychev Journeyer
    Currently Being Moderated
    SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, STATUS
      FROM ALL_TRIGGERS
     WHERE TABLE_NAME='JOB_REQUESTS';
  • 8. Re: Data reverts back on commit
    Vladimir Zakharychev Journeyer
    Currently Being Moderated
    Here your statement should show "now rows selected" but i think it is still selecting two rows and showing you NULL in result set.
    What made you think "No rows selected" should've been returned? Did you see the update statement altering values of any columns referenced in WHERE clause?
  • 9. Re: Data reverts back on commit
    SalmanQureshi Expert
    Currently Being Moderated
    Apology for mistake. I took it wrong
  • 10. Re: Data reverts back on commit
    799084 Newbie
    Currently Being Moderated
    Let me specify a few more things that I should have mentioned earlier. There's no triggers in the table. I did verify that from user_triggers and all_triggers. Also this issue happens only on a few rows in this table. Other rows as well as other tables work just fine.
  • 11. Re: Data reverts back on commit
    Aman.... Oracle ACE
    Currently Being Moderated
    It doesn't matter that how many rows are there or a part of the transaction. A commit is going to work for a transaction and all the rows effected by that transaction. As asked before, please show us a cut/paste of the commands that were given few replies above.

    Aman....
  • 12. Re: Data reverts back on commit
    Carlovski Pro
    Currently Being Moderated
    Weird. My first thought was, is this a global temporary table (with on commit delete) , but I'd expect to see no rows returned, but it looks like it is returning 2 null records.
  • 13. Re: Data reverts back on commit
    Vladimir Zakharychev Journeyer
    Currently Being Moderated
    Hmm... So you say there are no triggers (usual suspects when things go weird with DML.) No other processes running in background that could alter these rows, like jobs or external programs processing data in this table continuously? What's the table DDL? Can you post the output of this:
    SET LONG 100000
    SELECT SYS.DBMS_METADATA.GET_DDL('TABLE','JOB_REQUESTS') FROM SYS.DUAL;
    SELECT SYS.DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','JOB_REQUESTS') FROM SYS.DUAL;
    SELECT SYS.DBMS_METADATA.GET_DEPENDENT_DDL('RLS_POLICY','JOB_REQUESTS') FROM SYS.DUAL;
    SELECT SYS.DBMS_METADATA.GET_DEPENDENT_DDL('RLS_CONTEXT','JOB_REQUESTS') FROM SYS.DUAL;
    and this:
    SET TIMING ON
    SELECT NVL(TO_CHAR(RELEASED_DATE),'NULL') FROM JOB_REQUESTS WHERE ID IN (161, 195);
    UPDATE JOB_REQUESTS SET RELEASED_DATE = SYSDATE WHERE ID IN (161, 195);
    SELECT NVL(TO_CHAR(RELEASED_DATE),'NULL') FROM JOB_REQUESTS WHERE ID IN (161, 195);
    COMMIT;
    SELECT NVL(TO_CHAR(RELEASED_DATE),'NULL') FROM JOB_REQUESTS WHERE ID IN (161, 195);
  • 14. Re: Data reverts back on commit
    799288 Newbie
    Currently Being Moderated
    Have you checked the basics, are you updating the table or a synonym? is anyone else accessing the table at the same time (the phantom update)? Have you put a trace on the session to see exactly what happens?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points