Re: IsolationLevel workaround?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Bob,

I couldn't find the BOL section you mentioned (I'm using ADO, not ADO.NET.)

In the end I opted not to use the ADO transaction methods (i.e.
Connection.BeginTran, etc.) and instead just issue Command.Execute('BEGIN
TRAN'), etc.

This allows me to use the SNAPSHOT isolation level.

Thanks for your reply and willingness to help.

Sincerely,

David Rueter
drueter@xxxxxxxxxx

"Bob Barrows" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23j0XDr8sJHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
David Rueter wrote:
I would like to use the SQL 2005 SNAPSHOT isolation level. I
understand that the ADO IsolationLevelEnum has not been updated for
SQL 2005.

I thought it would be OK if I left the connection set to
cn.IsolationLevel=adXactReadCommitted, and then I issued a statement
on the connection like:

cn.Execute('SET TRANSACTION ISOLATION LEVEL SNAPSHOT');

However, ADO is kind enough to set the isolation method back to
ReadCommitted whenever I begin a transaction. And, I am not allowed
to change the isolation level to SNAPSHOT inside a transaction if the
isolation level was not SNAPSHOT at when the transaction was started.
Argh...

So I thought if I set cn.IsolationLevel=adXactUnspecified, then
issued the Execute statement as above to set the isolation level for
the connection that I would be OK. However, then I get errors like:

"Neither the isolation level nor a strengthening of it is supported."

Is there any way to get ADO to use the SNAPSHOT isolation level? Or,
is there at least any way to get ADO to NOT set the isolation level
at the start of a transaction so that I can set the isolation level
myself?

According to BOL, the SQL Native Client provider should provide this
support. I haven't tried it myself, but are you using SQLODEDB or
SQLNACLI for your provider?
See the topic "Working with Snapshot Isolation"
--
HTH,
Bob Barrows




.

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)
  • Oracle Isolation Level and ADO
    ... Oracle DB with ADO. ... BEGIN TRANSACTION ... Is the explicit LOCK useless in this code? ... isolation level impact only on read locks. ...
    (comp.databases)
  • IsolationLevel workaround?
    ... I would like to use the SQL 2005 SNAPSHOT isolation level. ... that the ADO IsolationLevelEnum has not been updated for SQL 2005. ... ReadCommitted whenever I begin a transaction. ... Is there any way to get ADO to use the SNAPSHOT isolation level? ...
    (microsoft.public.data.ado)
  • Re: IsolationLevel workaround?
    ... cn.Execute('SET TRANSACTION ISOLATION LEVEL SNAPSHOT'); ... ADO is kind enough to set the isolation method back to ... ReadCommitted whenever I begin a transaction. ...
    (microsoft.public.data.ado)
  • 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)