....
Oracle relational database. ACID compliant.
Transaction either occurs or brings it back to the state before the transaction occured. 100% of the time.
You dont get inconsistent answers from Oracle
....
completely agreed, but here is an exception to that.
Session#1 - did this.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
demo@ORA12C> set feedback off
demo@ORA12C> drop table t purge;
demo@ORA12C> create table t as select 1 x, 0 y from dual;
demo@ORA12C> insert into t(x,y) values(2,0);
demo@ORA12C> set feedback on
demo@ORA12C> select * from t;
X Y
---------- ----------
1 0
2 0
2 rows selected.
demo@ORA12C> update t set y = 1;
2 rows updated.
demo@ORA12C> select * from t;
X Y
---------- ----------
1 1
2 1
2 rows selected.
demo@ORA12C>
Session#2 - did this.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
demo@ORA12C> select * from t;
X Y
---------- ----------
1 0
demo@ORA12C> update t set y = y+1;
Update is blocked, since the session#1 holds the lock.
Now back into Session#1 and did this.
demo@ORA12C>
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> select * from t;
X Y
---------- ----------
1 1
2 1
2 rows selected.
demo@ORA12C>
Now go back to session#2 and did this.
demo@ORA12C>
demo@ORA12C> update t set y = y+1;
2 rows updated.
demo@ORA12C> select * from t;
X Y
---------- ----------
1 3 <<<===== Incorrect results.
2 2
demo@ORA12C>
not sure if this is a Bug (to me it sounds like a bug!), let me know if this is still reproducible in 12.2 (sorry don't have access to that database) and any patch available for 12.1.0.2 database.