Re: Oracle Isolation Level and ADO

kenshiro2000@xxxxxxxxx wrote: 
I have some COMs developed in Visual Basic. These COMs access to an
Oracle DB with ADO. it isn't set the Isolation level of the transaction
on the ADO Connection and i think that ADO use the default Level "Read
Committed".


Yes, this is correct.

"If you do not use the IsolationLevel property of the ADO Connection object to specify an isolation level, the resource manager specifies the default isolation level, which is Read Committed for both SQL Server and Oracle."

http://support.microsoft.com/default.aspx?scid=kb;en-us;295570

Is the explicit LOCK useless in this code?

No, the lock is not useless -- in theory. READ COMMITTED allows the data to change during your transaction, and therefore if you execute your query twice, you might get different results. You want a more strict isolation level, but I would suggest using "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" instead of issuing explicit locking commands.

The REPEATABLE READ isolation level should work too, but Oracle does not support it, and automatically upgrades such transactions to SERIALIZABLE.

Read the following excellent article, it explains locks and isolation levels, with attention to differences between Oracle and other RDBMS implementations:

http://www.dbazine.com/db2/db2-disarticles/gulutzan6

What is the right Isolation Level?

I think that "Serializable" should be equvalent to the explicit LOCK on
the tables.

SERIALIZABLE denies all operations on the data (read as well as write), so it is similar to EXCLUSIVE locking. But this is not exactly supported by Oracle, because Oracle implements only UPDATE locking, according to Gulutzan's article above.

Regards,
Bill K.
.

Relevant Pages

  • Re: ADO.NET 2.0 - Question for Microsoft Employees
    ... snapshot isolation. ... That exists at System.Data - which leads me to believe that nothing really stops me from setting that isolation level on Oracle. ... They all work more or less the same: depending on the transaction isolation level, other transactions can read the data changed or have to wait till the exclusive lock is lifted from the row or table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.NET 2.0 - Question for Microsoft Employees
    ... but snapshot is more for reads .. ... Writes in oracle are either Readcommitted or Serializable. ... They all work more or less the same: depending on the transaction isolation level, other transactions can read the data changed or have to wait till the exclusive lock is lifted from the row or table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Strange problem with JDBC and Oracle 10g
    ... Somehow delete statements inside a transaction do not seem to have any ... effect when I set the isolation level to *serializable* in Oracle 10g. ... With the default isolation level everything works fine. ... > END OF STMT ...
    (comp.lang.java.databases)
  • Getting an OLEDB Transaction for Oracle 7.2
    ... establish a transaction in an Oracle 7.2 database. ... isolation level 'Unspecified' gives me a different error message. ...
    (microsoft.public.dotnet.general)
  • Re: ISOLATION levels and when to use SERIALIZEABLE
    ... Setting an isolation level tells SQL Server ... satisfy the where clause of a query in the transaction. ... acquiring KEY RANGE locks to protect a range of data in an index. ... select * into orders2 from orders ...
    (microsoft.public.sqlserver.programming)