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?
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;
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:
- launch sqlplus and connect to the readdb
- run the read query, get status 'ACTIVE'
- run the update query against the writedb in a different sqlplus
- go back to the sqlplus from step (1), launch the read query from step (2) and get 'ACTIVE' again
- repeat read query same as in step 4 -- get the correct 'DONE' response.