Re: Oracle Isolation Level and ADO


"Bill Karwin" <bill@xxxxxxxxxx> wrote in message
news:dc1gl801rue@xxxxxxxxxxxxxxxxxxxxx
> 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.
Readers don't block writers in Oracle. (regardless of the locking method or
isolation level).
Jim


.

Relevant Pages

  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... Oracle recommends not using bitmap indexes in OLTP ... long due to performance problems) readers will lock and cause deadlocks. ... shouldn't enter a non-default isolation level for long - behave, ... Even in your database code you are extremely cognizant of the ...
    (comp.databases.oracle.server)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... Does Oracle support query sampling? ... Isolation is semantics, locking is implementation. ... readers block writers, while Oracle has never had this problem. ... SQL Server 2005 introduces a new "snapshot" isolation level that is ...
    (comp.databases.oracle.server)
  • Re: Database or store to handle 30 Mb/sec and 40,000 inserts/sec
    ... Does Oracle support query sampling? ... Isolation is semantics, locking is implementation. ... readers block writers, while Oracle has never had this problem. ... SQL Server 2005 introduces a new "snapshot" isolation level that is ...
    (comp.databases.oracle.server)
  • 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 and ODBC { CALL ..... } Syntax
    ... > Coonecting to Oracle 8i DB running on Unix ... > OleDB connection with provider as MSDAORA. ... > the same way as it works in ADO arena. ...
    (microsoft.public.dotnet.framework.adonet)