Update rolled back after commit

From: Rich J <rich242j_at_gmail.com>
Date: Mon, 7 Jun 2021 10:52:05 -0500
Message-ID: <CAANsBX3Q9CysQNpxuzMNqRuTJueJ062hW9+BJ=9QqFSYEM4sKA_at_mail.gmail.com>

Hey all,

In a 19.6 development DB, a developer updates a single row in a table and commits, but the change is rolled back after the commit (commands pasted into SQLPlus, thus the weird formatting):

SQL> select * from testsch.mytab08 where pk_col01='BH';

update testsch.mytab08 set data_col='ORIGVAL' where pk_col01='BH' and pk_col02 = ' AND';

select * from testsch.mytab08 where pk_col01='BH';

commit;

select * from testsch.mytab08 where pk_col01='BH';

• Output from first select pk_col01 pk_col02 data_col
  • ---------------------------------------------------- ------------- BH AND *NEWVAL * BH TL1 ORIGVAL BH TL2 ORIGVAL BH TL3 ORIGVAL BH STL3 ORIGVAL BH STL1 ORIGVAL BH REJ ORIGVAL BH RXS ORIGVAL BH SRXS ORIGVAL BH STL2 ORIGVAL

10 rows selected.

• UPDATE run SQL> 1 row updated.
• In-transaction SELECT to verify the single row updated SQL> pk_col01 pk_col02 data_col
  • ---------------------------------------------------- ------------- BH AND *ORIGVAL * BH TL1 ORIGVAL BH TL2 ORIGVAL BH TL3 ORIGVAL BH STL3 ORIGVAL BH STL1 ORIGVAL BH REJ ORIGVAL BH RXS ORIGVAL BH SRXS ORIGVAL BH STL2 ORIGVAL

10 rows selected.

• COMMIT SQL> Commit complete.
• Post-transaction SELECT showing committed data. SQL> pk_col01 pk_col02 data_col
  • ---------------------------------------------------- ------------- BH AND *ORIGVAL * BH TL1 ORIGVAL BH TL2 ORIGVAL BH TL3 ORIGVAL BH STL3 ORIGVAL BH STL1 ORIGVAL BH REJ ORIGVAL BH RXS ORIGVAL BH SRXS ORIGVAL BH STL2 ORIGVAL

10 rows selected.

• Manual SELECT pasted into SQL*Plus immediately after the initial statements showing the "rollback" value. SQL> select * from testsch.mytab08 where pk_col01='BH';

pk_col01       pk_col02                                            data_col



-------- ---------------------------------------------------- -------------


BH                    AND                                     *NEWVAL
 *
BH                    TL1                                     ORIGVAL
BH                    TL2                                     ORIGVAL
BH                    TL3                                     ORIGVAL
BH                   STL3                                     ORIGVAL
BH                   STL1                                     ORIGVAL
BH                    REJ                                     ORIGVAL
BH                    RXS                                     ORIGVAL
BH                   SRXS                                     ORIGVAL
BH                   STL2                                     ORIGVAL

10 rows selected.

SQL> exit

This was originally done in Toad, where I suspected a threading/transactional issue, but as you can see above, was fully replicated in SQL*Plus. I needed to pause troubleshooting for a meeting. When I started again, I ran the above in a trace, which of course worked as expected without the phantom rollback. There are no triggers on the table. This is a standalone instance with no replication. There is a "AFTER ALTER OR TRUNCATE ON DATABASE" trigger in this DB, but I don't see how that could have affected this. No errors are evident in the SQL*Plus session, nor the alert log.

I'm struggling to come up with a transactional scenario where this could happen. Thoughts?

Thanks,
Rich

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 07 2021 - 17:52:05 CEST