How to set transaction isolation level using classic ASP?

1k views Asked by At

I have a classic ASP page and want to set the transaction isolation level to READ UNCOMMITTED. Using this documentation I have came up with the following:

Set conn = GetConnection
conn.IsolationLevel = adXactReadUncommitted

'conn.BeginTrans

'conn.CommitTrans

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO [dbo].[A] ([IsolationLevel]) SELECT CASE transaction_isolation_level  WHEN 0 THEN 'Unspecified'  WHEN 1 THEN 'ReadUncommitted'  WHEN 2 THEN 'ReadCommitted'  WHEN 3 THEN 'Repeatable'  WHEN 4 THEN 'Serializable'  WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL  FROM sys.dm_exec_sessions  where session_id = @@SPID"
Set rs = cmd.Execute()

response.write(conn.IsolationLevel)

The last response.write gives me correctly 256 (READ UNCOMMITTED) but when I query the table I got only ReadCommitted records.

Could anyone tell what I am doing wrong?


And here is the body of the GetConnection function:

FUNCTION GetConnection()
    DIM strConnectionDotNet : strConnectionDotNet = "Data Source=..."
    SET GetConnection = Server.CreateObject("adodb.connection")
    GetConnection.connectionstring="Provider=sqloledb;" & strConnectionDotNet
    GetConnection.open
END FUNCTION
1

There are 1 answers

0
gotqn On BEST ANSWER

As it is said in the documentation:

Note: The IsolationLevel settings will not work until next time BeginTrans is called.

It is a kind of strange, but I need to the following:

conn.BeginTrans
... sql statement is executed here
conn.CommitTrans

Even thought the T-SQL statement is a SELECT. Also, after the conn.CommitTrans it is still using the default isolation level (the one specified in the context of the database).