Oracle 11g R2 – Transactions and DBLink Usage

dblinkoracle-11g-r2transaction

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:

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.

Best Answer

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. "

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Postgresql – Read Committed Isolation Level

Related Question