| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem setting SERIALIZABLE in a distributed transaction.
Here's a small demonstration of behavior I described (8.1.7, but should be
the same in 8.1.5), with some explanations following:
Session 1 log
1 row created.
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> declare
2 cnt pls_integer;
3 begin
4 dbms_output.put_line('Tx 1 started at '||to_char(sysdate,'HH24:MI:SS'));
5 set transaction read only;
6 select count(*) into cnt from t;
7 dbms_output.put_line('Rows in T: '||cnt);
8 dbms_lock.sleep(10);
9 select count(*) into cnt from t;
10 dbms_output.put_line('Rows in T 10 seconds after: '||cnt);
11 commit;
12 dbms_output.put_line('Tx 1 ended at '||to_char(sysdate,'HH24:MI:SS'));
13 end;
14 /
Tx 1 started at 13:06:05
Rows in T: 2
Rows in T 10 seconds after: 2
Tx 1 ended at 13:06:15
PL/SQL procedure successfully completed.
Session 2 log:
SQL> insert into t values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'HH24:MI:SS') "Tx 2 ended at" from dual;
Tx 2 end
SQL> select count(*) "Rows in T for Tx 2" from t;
Rows in T for Tx 2
3
---------------------
Note that Tx 2 started after Tx 1 and ended before Tx 1. It inserted one row into T, committed it and confirmed that row was inserted (3 rows in T). Tx 1 started by defining a read only transaction and selected count from T, which was 2 at that time. It then suspended for 10 seconds and re-queried T - and still saw 2 rows, while Tx 2 already inserted 3rd row and committed it! The same holds for read-write transactions, but in this case each query gets its own snapshot. If you remove SET TRANSACTION .... from the above code, you will see that changes made by Tx 2 become visible for Tx 1 - each query from T receives its own snapshot now because tx is read-write (default) and Oracle maintains statement-level read consistency for this tx. Currently you cannot freely switch between tx-level and stmt-level read consistency within one transaction, and read only transactions are what they are - read only, so you can't change anything within such tx though you are guaranteed to have consistent view of the database throughout the whole tx.
Also note that SET TRANSACTION must be the first statement in a transaction. Your problem may well be fixed by issuing COMMIT just before SET TRANSACTION to end previous tx and start new tx with new isolation level (no guarantees that it will help though ;)
By the way, I would recommend upgrading to at least 8.1.7 - 8.1.5 is long time desupported and was full of bugs.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Alasdair I MacLeod" <alasdair_i_macleod_at_talk21.com> wrote in message news:3D5BDF1B.EBAE77AA_at_talk21.com...Received on Fri Aug 16 2002 - 04:36:01 CDT
> Vladimir,
>
> thanks for your comments but I'm not seeing the transaction behaviour
> you describe.
>
> The problem I had was that I don't want to see any changes to my local
> table after the transaction begins. The default oracle tx level
> doesn't seem give me this - if I select * from table then insert and
> commit another row from a different session and select * again the new
> row will show up in my original tx.
>
> I.e.
> session 1 session 2
> select * from test
> num
> ---
> 1
> 2
> insert into test (num) values 3;
> commit;
> select * from test
> num
> ---
> 1
> 2
> 3
>
> (Running on 8.1.5 by the way.)
>
> What I was looking for was a situation where the third line would not
> show
> up in session 1 until the tx ended - I have to do some data processing
> before transferring the data and end up with two sets of data with a one
> to
> one correlation.
>
> I got round the problem by having an initial transaction that simple
> updates
> a flag column to mark the rows as 'mine'. Then, in a later transaction
> I
> select for update, do stuff and write to the remote db. This works fine
> and saves on creating a fairly long lived, serializable, tx. However,
> it
> doesn't feel the 'right thing'.
>
 |
 |