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
