Re: Serializable transactions

I have the answer to my own question. I think it some might be interessed by the explanation.
When a transaction is set to serializable, Oracle ensure data constistency. That is to say, query are repeatable, there is no phantom read. When you do a select in those transactions, even if the data update are not concerned by the update, if the selected data are modified by an other committed transaction, serializability is broken and the transaction aborts.
These kind of mechanism are very usefull, specially for web enabled applications. In those application you might have to ensure data consistency. But if you use a lock, the lock remains active untill the session (cookie based or whatever) is deleted or ends because of timeout. Using locks you can easly lock your whole database if the user does not close its session carefully. But using serializable transation, you get the same security level, but you never lock the database. The only constrain is to handle the transaction error. Moreover, you can make a transaction which end relies on set of row which has no connexion whith the updated data. For that, you just have to select the data you don't want to be changed during the transaction. Here an exemple which make understand the whole thing about the serializable transactions :
Exemple 1
t1 :
SQL> select * from ref_table_1;

        ID TOTO TITI PARAM_CREA PARAM_STAR PARAM_ENDA
---------- ----- ---------- ---------- ---------- ----------

         1 eheh          15 05/03/2000 01/01/2000 04/30/2000
         3 eheh          15 05/03/2000 10/01/2000
         2 eheh          15 05/03/2000 05/01/2000 09/30/2000

t2 :
SQL> select * from ref_table_1;

        ID TOTO TITI PARAM_CREA PARAM_STAR PARAM_ENDA
---------- ----- ---------- ---------- ---------- ----------

         1 eheh          15 05/03/2000 01/01/2000 04/30/2000
         3 eheh          15 05/03/2000 10/01/2000
         2 eheh          15 05/03/2000 05/01/2000 09/30/2000

SQL> set transaction isolation level serializable;

Transaction set.

t1 :
SQL> delete from ref_table_1 where ID=1;

1 row deleted.

SQL> commit;

Commit complete.

t2 :
SQL> delete from ref_table_1 where ID=2;

1 row deleted.

SQL> commit;

Commit complete.

In this first exemple, everything just works fine. But now, look at the second one. Notice where the select * ... statement is located in the transactions.
example 2 :
t1 :
SQL> select * from ref_table_1;

        ID TOTO TITI PARAM_CREA PARAM_STAR PARAM_ENDA
---------- ----- ---------- ---------- ---------- ----------

         1 eheh          15 05/03/2000 01/01/2000 04/30/2000
         3 eheh          15 05/03/2000 10/01/2000
         2 eheh          15 05/03/2000 05/01/2000 09/30/2000

t2 :
SQL> set transaction isolation level serializable;

Transaction set.

t1 :
SQL> delete from ref_table_1 where ID=1;

1 row deleted.

SQL> commit;

Commit complete.

t2 :
SQL> select * from ref_table_1;

        ID TOTO TITI PARAM_CREA PARAM_STAR PARAM_ENDA
---------- ----- ---------- ---------- ---------- ----------

         2 eheh          15 05/03/2000 05/01/2000 09/30/2000
         3 eheh          15 05/03/2000 10/01/2000
         1 eheh          15 05/03/2000 01/01/2000 04/30/2000

SQL> delete from ref_table_1 where ID=2; delete from ref_table_1 where ID=2
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction Received on Tue Jun 06 2000 - 00:00:00 CEST