1 2 Previous Next 20 Replies Latest reply: Sep 22, 2010 4:41 AM by Franck Pachot RSS

    Data reverts back on commit

    799084
      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
          Salman Qureshi
          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
            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
              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
                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....
                  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
                    Salman Qureshi
                    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
                      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
                        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
                          Salman Qureshi
                          Apology for mistake. I took it wrong
                          • 10. Re: Data reverts back on commit
                            799084
                            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....
                              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
                                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
                                  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
                                    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