2

I have 2 dbs, let's call them writedb and readdb. Readdb has a dblink to writedb and a view to a table over that dblink.

I do an update on writedb to that table which view points to and then read from that table over the dblink. I appear to be seeing old values for seconds after the transaction commits even if I set isolation level to SERIALIZABLE.
Does this make any sense?

On writedb:

create table mk.dblink_test (
id varchar2(16),
status varchar2(16));

insert into mk.dblink_test
SELECT rownum, 'ACTIVE'
FROM   dual
CONNECT BY LEVEL <= 1000000;

On readdb:

CREATE DATABASE LINK DBL_TEST CONNECT TO mk IDENTIFIED BY password USING 'writedb:1521/qa'

The SQL that updates writedb directly:

update mk.dblink_test
set status = 'DONE'
where id = '1';
commit;

Now the situation with SQL which get dirty reads is weird. The following simpler query

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
WHERE r.id = '1';
commit;

does not appear to exhibit the problem. But a more complex version:

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
LEFT OUTER JOIN (SELECT '3' status from dual) optout ON optout.status = r.status
WHERE r.id = '1';
commit;

reproduces it 90% reliably. The steps to reproduce:

  1. launch sqlplus and connect to the readdb
  2. run the read query, get status 'ACTIVE'
  3. run the update query against the writedb in a different sqlplus
  4. go back to the sqlplus from step (1), launch the read query from step (2) and get 'ACTIVE' again
  5. repeat read query same as in step 4 -- get the correct 'DONE' response.
  • can you add an example of the two sessions? – miracle173 Dec 25 '14 at 9:35
  • You mean actual SQL? – MK01 Dec 25 '14 at 22:52
  • The best would be a simple test case that can be reproduced on any system. At least statements and output of both sessions. But no data that compromises your system – miracle173 Dec 25 '14 at 23:14
  • @miracle173 I did what I could, pretty much everything short on the table schema. I'll play with it tomorrow. – MK01 Dec 26 '14 at 6:44
  • 1
    What you described here does not conform to any transaction isolation level. The only way I could reproduce it was by enabling the Result Cache. For example with result_cache_modeset to FORCE and result_cache_remote_expiration set to 1, the database caches results related to remote objects, and these results remain valid for 1 minute (or more, depending on the value of this parameter), so you can get stale results. By default these parameters are not set (MANUAL and 0). I would check the values of these parameters and the V$RESULT_CACHE_* views for anything related. – Balazs Papp Dec 27 '14 at 14:50
1

Oracle has spoken:

http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm#i1008473

Distributed Query Returns Different Results on Consecutive Runs when no Changes Exist in the Base Tables. (Doc ID 561471.1)

...skip some more writing...

Bug.611416 SELECT AFTER UPDATE AND COMMIT DOESN'T SHOW UPDATED COLUMNS

...skip some more writing...

"You can use the following techniques to ensure that the SCNs of the two machines are synchronized just before a query: * Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example, SELECT * FROM DUAL@REMOTE. * Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before issuing the remote query. "

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.